I have an application that hasn’t been touched in two years. My former employer asked me to figure out why suddenly the data-abstract based server has started taking SQL that used to work, and not working anymore with it.
The original SQL that didn’t work contained a left join, I rewrote it without the left join, and used an inner-select instead. Now instead of an “ambiguous column name” error coming back from ADO, I am getting EDAException message “invalid column name ‘event_desc’”.
It seems to me that Data Abstract will not allow me to do things like:
select T2.Field1,
(select B from Table2 T where T.ID=TableA.ForeignKey1) AValue
from Table2 T2
WHERE {WHERE}
Note the use of a {WHERE} expression.
Even hacks like adding a SELECT T.* ( ORIGINAL_QUERY_HERE) T are not helping me.
What seems to happen is that the AValue or as AValue syntax is NOT respected or understood. I’m using an SQL Server Database in this case, and this worked fine two years ago.
This is DataAbstract 7.0.71, with a Delphi server side, and several different clients.
Here’s my whole query:
---- Delivery And Pickup Freight Codes V2015.1
declare @Del TABLE
( ID int,
freightDesc varchar(255)
)
insert @Del select ID,freightDesc from tblFreight
insert into @Del (ID,freightDesc) VALUES (-2,‘We Deliver’)
insert into @Del (ID,freightDesc) VALUES (-1,‘Customer Freight’)
---- Get BarcodeBookings V2015.1
SELECT T.* FROM (
SELECT B.ID,
B.booking_no,
B.showName,
B.dDate,
B.rDate,
B.TOutDate,
B.TInDate,
C.OrganisationV6,
A.VenueName,
B.VenueRoom,
B.status,
CASE
WHEN B.status=0 THEN 'Booked’
WHEN B.status=1 THEN 'Checked Out’
WHEN B.status=2 THEN 'Returned’
WHEN B.status=3 THEN 'Closed’
WHEN B.status=4 THEN 'Pull’
END status_str,
B.BookingProgressStatus,
B.booking_type_v32,
B.order_no,
B.delivery_address_exist,
B.event_code,
(select event_desc from dbo.tblEvent P where P.event_code = B.event_code) as event_desc,
B.delivery_time,
B.pickup_time,
B.del_time_h,
B.del_time_m,
B.ret_time_h,
B.ret_time_m,
B.From_locn,
(select locn_name from tblLocnlist where locn_number=B.from_locn) as From_locn_name,
B.return_to_locn,
(select locn_name from tblLocnlist where locn_number=B.return_to_locn) as Return_to_locn_name,
B.Trans_to_locn,
A.Address1,
A.Address2,
A.City,
A.State,
A.Country,
A.ZipCode AS PostCode,
B.ProjectManager,
B.ConDate,
B.bBookingIsComplete,
B.Division,
B.delivery_viav71,
B.pickup_viav71,
ISNULL(FD.freightDesc,'TYPE '+CAST(delivery_viav71 as char)) as Delivery_via_desc,
ISNULL(FP.freightDesc,'TYPE '+CAST(pickup_viav71 as char)) as Pickup_via_desc
FROM dbo.tblbookings AS B
LEFT JOIN dbo.tblVenues AS A ON B.VenueID = A.ID
INNER JOIN dbo.tblCust AS C ON B.CustID = C.ID
LEFT JOIN @Del FD on FD.ID=B.delivery_viav71
LEFT JOIN @Del FP on FP.ID=B.pickup_viav71
WHERE {WHERE}
) T
ORDER BY T.dDate