SQL Query converted to SQL Server

  • Thread starter Thread starter Chris Motherwell via AccessMonster.com
  • Start date Start date
C

Chris Motherwell via AccessMonster.com

The following SQL code is from an MS Access query. I need to be able to use
it on an SQL Server - how can this be done?

SELECT tblTasks.Task_ID, tblTasks.Associate, tblAssociates.Aaddress,
tblAssociates.Aaddress2, tblTasks.Task, If(tblAssociates.SE=Yes,Hours,0) AS
HoursDM, If(tblAssociates.SE=No, tblTasks.Hours,0) AS HoursOth, If
(tblAssociates.SE=Yes,HoursDM,HoursOth) AS AHours, If(tblAssociates.SE=Yes,
tblAssociates.SEPayment, tblAssociates .STPayment) AS Payment, If
(tblAssociates.SE=Yes,HoursDM* tblAssociates.SEPayment,HoursOth*
tblAssociates.STPayment) AS Amount, tblAssociates.SEPayment,
tblAssociates.STPayment, tblTasks.Postage+ tblTasks.Ferry_Fare+
tblTasks.Prints+ tblTasks.Courier+ tblTasks.Taxi_Fare+ tblTasks.Air_Fare+
tblTasks.Long_Distance_Phone+ tblTasks.Parking+ tblTasks.Vehicle_Rental+
tblTasks.Publications+ tblTasks. tblTasks.Photo_Amt AS Disbur,
tblTasks.Date, tblTasks.Mileage, tblTasks.Miles_Amt, tblTasks.Cheque_No,
tblAssociates.SE, tblAssociates.ST, tblTasks.Invoice_Paid, tblTasks.Hours,
tblTasks.Associate_Paid, tblTasks.InvoiceID, tblTasks.SE, tblTasks.ST,
tblInvoice.Inv2
FROM tblInvoice RIGHT JOIN (tblAssociates INNER JOIN tblTasks ON
tblAssociates.Associate = tblTasks.Associate) ON tblInvoice.InvoiceID =
tblTasks.InvoiceID
WHERE (((tblTasks.InvoiceID) Is Not Null))
ORDER BY tblTasks.Date;
 
Rather than If or IIf, try:
CASE WHEN tblAssociates.SE = Yes THEN Hours ELSE0 END AS
HoursDM,
 
Thanks - I tried your suggestion and received this message:

Error in SELECT clause: expression near 'CASE'.
Missing FROM clause.
Error in SELECT clause: expression near '='.
Error in SELECT clause: expression near ','.
Unable to parse query text.
And
ADO error:Incorrect syntax near the keyword 'CASE'

This is the code used:

SELECT tblTasks.Task_ID, tblTasks.Associate, tblAssociates.Aaddress,
tblAssociates.Aaddress2, tblTasks.Task, tblTasks.SE, tblTasks.ST,
tblAssociates.SE Expr1, tblAssociates.ST Expr2, tblTasks.Publications,
tblTasks.Vehicle_Rental, tblTasks.Parking, tblTasks.Long_Distance_Phone,
tblTasks.Air_Fare, tblTasks.Taxi_Fare, tblTasks.Ferry_Fare,
tblTasks.Prints, tblTasks.Courier, tblTasks.Postage, tblTasks.Photo_Amt,
tblTasks.Photocopies, tblTasks.Details, tblTasks.Miles_Amt, tblTasks.Hours,
tblTasks.Mileage, tblTasks.Date, tblInvoice.Inv2 CASE tblAssociates.SE =
Yes WHEN .Hours ELSE 0 END .HoursDM, CASE tblAssociates.SE = No WHEN
tblTasks.Hours ELSE 0 END .HoursOth,
CASE WHEN tblAssociates.SE = Yes THEN .HoursDM ELSE .HoursOth END .AHours,
CASE WHEN tblAssociates.SE = Yes THEN tblAssociates.SEPayment ELSE
tblAssociates.STPayment END .Payment, CASE WHENtblAssociates.SE = Yes THEN
..HoursDM * tblAssociates.SEPayment ELSE .HoursOth * tblAssociates.STPayment
END .Amount, tblAssociates.SEPayment,
tblAssociates.STPayment, tblTasks.Postage + tblTasks.Ferry_Fare +
tblTasks.Prints + tblTasks.Courier + tblTasks.Taxi_Fare + tblTasks.Air_Fare
+ tblTasks.Long_Distance_Phone + tblTasks.Parking + tblTasks.Vehicle_Rental
+ tblTasks.Publications + tblTaskstblTasks.Photo_Amt Disbur, tblTasks.Date,
tblTasks.Mileage, tblTasks.Miles_Amt, tblTasks.Cheque_No, tblAssociates.SE,
tblAssociates.ST, tblTasks.Invoice_Paid, tblTasks.Hours,
tblTasks.Associate_Paid, tblTasks.InvoiceID, tblTasks.SE, tblTasks.ST,
tblInvoice.Inv2
FROM tblInvoice RIGHT JOIN
(tblAssociates INNER JOIN tblTasks ON tblAssociates.Associate =
tblTasks.Associate) ON tblInvoice.InvoiceID = tblTasks.InvoiceID
WHERE ((tblTasks.InvoiceID) IS NOT NULL)
ORDER BY tblTasks.Date;

I would appreciate any suggestions.
 
Some times you have "CASE tbl..." and others you have "CASE WHEN tbl..." The
second syntax is correct.

What's up with all the ".Hours", ".HoursOth", etc? I'm not sure that I have
ever seen notation like that.

I'm not sure SQL Server converts Yes and No to the proper numeric
equivalents.
 
As you thought, SQL Server doesn't recognize Yes and No; it also doesn't
like the the names I'm using for the calculated fields, HoursDM, and
HoursOth - it returns the message Invalid column name 'HoursDM' - which is
what is also says for Yes and No. I've included the code that produced this
message:

SELECT TOP 100 PERCENT [David.Mann].tblTasks.Task_ID,
[David.Mann].tblTasks.Associate, [David.Mann].tblAssociates.Aaddress,
[David.Mann].tblAssociates.Aaddress2,
[David.Mann].tblTasks.Task, [David.Mann]
..tblTasks.SE, [David.Mann].tblTasks.ST, [David.Mann].tblAssociates.SE AS
Expr1, [David.Mann].tblAssociates.ST AS Expr2,
[David.Mann].tblTasks.Publications,
[David.Mann].tblTasks.Vehicle_Rental, [David.Mann].tblTasks.Parking,
[David.Mann].tblTasks.Long_Distance_Phone,
[David.Mann].tblTasks.Air_Fare, [David.Mann]
..tblTasks.Taxi_Fare, [David.Mann].tblTasks.Ferry_Fare, [David.Mann]
..tblTasks.Prints, [David.Mann].tblTasks.Courier,
[David.Mann].tblTasks.Postage, [David.Mann]
..tblTasks.Photo_Amt, [David.Mann].tblTasks.Photocopies, [David.Mann]
..tblTasks.Details, [David.Mann].tblTasks.Miles_Amt,
[David.Mann].tblTasks.Hours, [David.Mann]
..tblTasks.Mileage, [David.Mann].tblTasks.Date, [David.Mann]
..tblInvoice.Inv2,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN [David.Mann].tblTasks.Hours ELSE 0 END AS HoursDM,
CASE WHEN [David.Mann].tblAssociates.SE =
No THEN [David.Mann].tblTasks.Hours ELSE 0 END AS HoursOth,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN HoursDM ELSE HoursOth END AS AHours,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN [David.Mann].tblAssociates.SEPayment ELSE [David.Mann]
..tblAssociates.STPayment END AS Payment,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN HoursDM * [David.Mann].tblAssociates.SEPayment ELSE HoursOth *
[David.Mann].tblAssociates.STPayment END AS Amount,
[David.Mann].tblAssociates.SEPayment,
[David.Mann].tblAssociates.STPayment,
[David.Mann].tblTasks.Postage + [David.Mann]
..tblTasks.Ferry_Fare + [David.Mann].tblTasks.Prints + [David.Mann]
..tblTasks.Courier + [David.Mann].tblTasks.Taxi_Fare + [David.Mann]
..tblTasks.Air_Fare
+ [David.Mann]
..tblTasks.Long_Distance_Phone + [David.Mann].tblTasks.Parking + [David.Mann]
..tblTasks.Vehicle_Rental + [David.Mann].tblTasks.Publications + [David.Mann]
..tblTasks.Photo_Amt
AS Disbur, [David.Mann].tblTasks.Date AS
Expr3, [David.Mann].tblTasks.Mileage AS Expr4, [David.Mann]
..tblTasks.Miles_Amt AS Expr5, [David.Mann].tblTasks.Cheque_No,
[David.Mann].tblAssociates.SE AS Expr6,
[David.Mann].tblAssociates.ST AS Expr7, [David.Mann].tblTasks.Invoice_Paid,
[David.Mann].tblTasks.Hours AS Expr8,
[David.Mann].tblTasks.Associate_Paid,
[David.Mann].tblTasks.InvoiceID, [David.Mann].tblTasks.SE AS Expr9,
[David.Mann].tblTasks.ST AS Expr10,
[David.Mann].tblInvoice.Inv2 AS Expr11
FROM [David.Mann].tblInvoice RIGHT OUTER JOIN
[David.Mann].tblAssociates INNER JOIN
[David.Mann].tblTasks ON [David.Mann]
..tblAssociates.Associate = [David.Mann].tblTasks.Associate ON [David.Mann]
..tblInvoice.InvoiceID = [David.Mann].tblTasks.InvoiceID
WHERE ([David.Mann].tblTasks.InvoiceID IS NOT NULL)
ORDER BY [David.Mann].tblTasks.Date

Thanks for your suggestions.
 
I'm not sure now where [David.Mann] comes from. Is this the table owner? Did
you actually have a period in there?

Change the "Yes" to whatever value you are actually storing in the field. I
suspect it might be -1 or 1. No is almost always 0.

Column aliases like "Expr6" suggest you don't think it is worth your time to
give the columns descriptive names.

--
Duane Hookom
MS Access MVP


Chris Motherwell via AccessMonster.com said:
As you thought, SQL Server doesn't recognize Yes and No; it also doesn't
like the the names I'm using for the calculated fields, HoursDM, and
HoursOth - it returns the message Invalid column name 'HoursDM' - which is
what is also says for Yes and No. I've included the code that produced
this
message:

SELECT TOP 100 PERCENT [David.Mann].tblTasks.Task_ID,
[David.Mann].tblTasks.Associate, [David.Mann].tblAssociates.Aaddress,
[David.Mann].tblAssociates.Aaddress2,
[David.Mann].tblTasks.Task, [David.Mann]
.tblTasks.SE, [David.Mann].tblTasks.ST, [David.Mann].tblAssociates.SE AS
Expr1, [David.Mann].tblAssociates.ST AS Expr2,
[David.Mann].tblTasks.Publications,
[David.Mann].tblTasks.Vehicle_Rental, [David.Mann].tblTasks.Parking,
[David.Mann].tblTasks.Long_Distance_Phone,
[David.Mann].tblTasks.Air_Fare,
[David.Mann]
.tblTasks.Taxi_Fare, [David.Mann].tblTasks.Ferry_Fare, [David.Mann]
.tblTasks.Prints, [David.Mann].tblTasks.Courier,
[David.Mann].tblTasks.Postage, [David.Mann]
.tblTasks.Photo_Amt, [David.Mann].tblTasks.Photocopies, [David.Mann]
.tblTasks.Details, [David.Mann].tblTasks.Miles_Amt,
[David.Mann].tblTasks.Hours, [David.Mann]
.tblTasks.Mileage, [David.Mann].tblTasks.Date, [David.Mann]
.tblInvoice.Inv2,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN [David.Mann].tblTasks.Hours ELSE 0 END AS HoursDM,
CASE WHEN [David.Mann].tblAssociates.SE =
No THEN [David.Mann].tblTasks.Hours ELSE 0 END AS HoursOth,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN HoursDM ELSE HoursOth END AS AHours,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN [David.Mann].tblAssociates.SEPayment ELSE [David.Mann]
.tblAssociates.STPayment END AS Payment,
CASE WHEN [David.Mann].tblAssociates.SE =
Yes THEN HoursDM * [David.Mann].tblAssociates.SEPayment ELSE HoursOth *
[David.Mann].tblAssociates.STPayment END AS Amount,
[David.Mann].tblAssociates.SEPayment,
[David.Mann].tblAssociates.STPayment,
[David.Mann].tblTasks.Postage +
[David.Mann]
.tblTasks.Ferry_Fare + [David.Mann].tblTasks.Prints + [David.Mann]
.tblTasks.Courier + [David.Mann].tblTasks.Taxi_Fare + [David.Mann]
.tblTasks.Air_Fare
+ [David.Mann]
.tblTasks.Long_Distance_Phone + [David.Mann].tblTasks.Parking +
[David.Mann]
.tblTasks.Vehicle_Rental + [David.Mann].tblTasks.Publications +
[David.Mann]
.tblTasks.Photo_Amt
AS Disbur, [David.Mann].tblTasks.Date AS
Expr3, [David.Mann].tblTasks.Mileage AS Expr4, [David.Mann]
.tblTasks.Miles_Amt AS Expr5, [David.Mann].tblTasks.Cheque_No,
[David.Mann].tblAssociates.SE AS Expr6,
[David.Mann].tblAssociates.ST AS Expr7,
[David.Mann].tblTasks.Invoice_Paid,
[David.Mann].tblTasks.Hours AS Expr8,
[David.Mann].tblTasks.Associate_Paid,
[David.Mann].tblTasks.InvoiceID, [David.Mann].tblTasks.SE AS Expr9,
[David.Mann].tblTasks.ST AS Expr10,
[David.Mann].tblInvoice.Inv2 AS Expr11
FROM [David.Mann].tblInvoice RIGHT OUTER JOIN
[David.Mann].tblAssociates INNER JOIN
[David.Mann].tblTasks ON [David.Mann]
.tblAssociates.Associate = [David.Mann].tblTasks.Associate ON [David.Mann]
.tblInvoice.InvoiceID = [David.Mann].tblTasks.InvoiceID
WHERE ([David.Mann].tblTasks.InvoiceID IS NOT NULL)
ORDER BY [David.Mann].tblTasks.Date

Thanks for your suggestions.
Click to expand...
 
[David.Mann] was inserted the first time I used the code from Access. I've
removed it and changed the Yes to True and No to False - the table this
field is in upsized from a Yes/No Data Type in Access to a bit Data Type is
SQL. The following messages appear with the following code:

Error in SELECT clause: expression near '='.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Unable to parse query text.

and

ADO error: Line 9: incorrect syntax near '='.

SELECT tblTasks.Task_ID, tblTasks.Associate,
tblAssociates.Aaddress, tblAssociates.Aaddress2, tblTasks.Task,
tblTasks.SE, tblTasks.ST, tblAssociates.SE, tblAssociates.ST,
tblTasks.Publications,
tblTasks.Vehicle_Rental, tblTasks.Parking, tblTasks.Long_Distance_Phone,
tblTasks.Air_Fare, tblTasks.Taxi_Fare, tblTasks.Ferry_Fare,
tblTasks.Prints,
tblTasks.Courier, tblTasks.Postage,
tblTasks.Photo_Amt, tblTasks.Photocopies, tblTasks.Details,
tblTasks.Miles_Amt, tblTasks.Hours, tblTasks.Mileage, tblTasks.Date,
tblInvoice.Inv2,
CASE WHEN tblAssociates.SE = True THEN
tblTasks.Hours ELSE 0 END AS HoursDM, CASE WHEN tblAssociates.SE = False
THEN tblTasks.Hours ELSE 0 END AS HoursOth,
CASE WHEN tblAssociates.SE = True THEN
HoursDM ELSE HoursOth END AS AHours,
CASE WHEN tblAssociates.SE = True THEN
tblAssociates.SEPayment ELSE tblAssociates.STPayment END AS Payment,
CASE WHENtblAssociates.SE = True THEN
HoursDM * tblAssociates.SEPayment ELSE HoursOth * tblAssociates.STPayment
END AS Amount, tblAssociates.SEPayment,
tblAssociates.STPayment,
tblTasks.Postage + tblTasks.Ferry_Fare +
tblTasks.Prints + tblTasks.Courier + tblTasks.Taxi_Fare + tblTasks.Air_Fare
+ tblTasks.Long_Distance_Phone + tblTasks.Parking + tblTasks.Vehicle_Rental
+ tblTasks.Publications +
tblTasks.Photo_Amt AS Disbur, tblTasks.Date, tblTasks.Mileage,
tblTasks.Miles_Amt, tblTasks.Cheque_False, tblAssociates.SE,
tblAssociates.ST,
tblTasks.Invoice_Paid, tblTasks.Hours,
tblTasks.Associate_Paid, tblTasks.InvoiceID, tblTasks.SE, tblTasks.ST,
tblInvoice.Inv2
FROM tblInvoice RIGHT JOIN
(tblAssociates INNER JOIN
tblTasks ON tblAssociates.Associate =
tblTasks.Associate) ON tblInvoice.InvoiceID = tblTasks.InvoiceID
WHERE ((tblTasks.InvoiceID) IS FALSET NULL)
ORDER BY tblTasks.Date;

I don't see what the problem is with the syntax.
 
I don't know if this is a paste error but
WHERE ((tblTasks.InvoiceID) IS FALSET NULL)
ORDER BY tblTasks.Date;
looks wrong.
Also, I would not expect SQL Server to understand True and False. Use 1 and
0.
 
Back
Top