I have a pyodbc query that works fine but I am trying to apply an update to add a new calculated field to the results. The working query is:
cursor = cnxn.cursor()
cursor.execute("SELECT RestaurantLocation.ID, Restaurant.DisplayName FROM
Restaurant INNER JOIN RestaurantLocation ON Restaurant.ID =
RestaurantLocation.Restaurant_ID WHERE (RestaurantLocation.Active = 1)")
row = cursor.fetchall()
columns = [column[0] for column in cursor.description]
for ID,DisplayName in row:
DisplayName = DisplayName.replace(" ","")
cursor = cnxn.cursor()
cursor.execute("SET NOCOUNT ON; SELECT ScheduledService.ServiceDate,
Restaurant.DisplayName AS RestaurantName, InvoiceLineItem.Invoice_ID,
ScheduledRestaurantService.Location_ID, ScheduledService.Service_ID,
Service.DisplayName AS ServiceName, InvoiceLineItem.Description,
InvoiceLineItem.Amount AS 'Subtotal' INTO #Subtotal FROM
ScheduledService INNER JOIN Invoice ON ScheduledService.Invoice_ID =
Invoice.ID INNER JOIN InvoiceLineItem ON Invoice.ID =
InvoiceLineItem.Invoice_ID INNER JOIN Payment ON Invoice.ID =
Payment.RelatedInvoice_ID INNER JOIN ScheduledRestaurantService ON
ScheduledService.ID = ScheduledRestaurantService.ID INNER JOIN
RMenuAllocatedScheduleSlot ON ScheduledRestaurantService.TimeSlot_ID =
RMenuAllocatedScheduleSlot.ID INNER JOIN [User] ON
ScheduledService.RelatedUser_ID = [User].ID INNER JOIN
RestaurantLocation ON ScheduledRestaurantService.Location_ID =
RestaurantLocation.ID INNER JOIN Restaurant ON
RestaurantLocation.Restaurant_ID = Restaurant.ID INNER JOIN Service ON
ScheduledService.Service_ID = Service.ID WHERE
(ScheduledService.ServiceDate BETWEEN ? AND ?) AND (NOT
(ScheduledService.Status_ID = 6)) AND (ScheduledService.Service_ID IN
(17, 18, 23, 24, 25)) AND (InvoiceLineItem.Description IN ('Subtotal'))
AND (ScheduledRestaurantService.Location_ID = ?) SELECT
#Subtotal.Invoice_ID, InvoiceLineItem.Amount AS 'SalesTax' INTO
#SalesTax FROM #Subtotal LEFT OUTER JOIN InvoiceLineItem ON
#Subtotal.Invoice_ID = InvoiceLineItem.Invoice_ID WHERE
(InvoiceLineItem.Description IN ('Sales Tax')) SELECT
#Subtotal.Invoice_ID, CASE WHEN #Subtotal.Service_ID IN (17,23,25,26)
THEN 0.00 ELSE InvoiceLineItem.Amount END AS 'Tip' INTO #Tip FROM
#Subtotal LEFT OUTER JOIN InvoiceLineItem ON #Subtotal.Invoice_ID =
InvoiceLineItem.Invoice_ID WHERE (InvoiceLineItem.Description IN
('Tip')) Select #Subtotal.ServiceDate, #Subtotal.RestaurantName,
#Subtotal.Invoice_ID, #Subtotal.Location_ID, #Subtotal.Service_ID,
#Subtotal.Subtotal, #SalesTax.SalesTax, #Tip.Tip Into #Final From
#Subtotal LEFT OUTER JOIN #SalesTax ON #Subtotal.Invoice_ID =
#SalesTax.Invoice_ID LEFT OUTER JOIN #Tip ON #Subtotal.Invoice_ID =
#Tip.Invoice_ID SET NOCOUNT OFF; Select * From #Final Order By
#Final.Service_ID, #Final.ServiceDate Drop Table #Subtotal Drop Table
#SalesTax Drop Table #Tip Drop Table #Final", (firstDay,lastDay,ID))
rows = cursor.fetchall()
Everything there works fine. The results from the second query are based on unique IDs created in the first query.
But when I try to add a new calculated field to the second query I get an "Incorrect Syntax Error" from the SQL Db. Here is the updated query:
cursor = cnxn.cursor()
cursor.execute("SELECT RestaurantLocation.ID, Restaurant.DisplayName FROM
Restaurant INNER JOIN RestaurantLocation ON Restaurant.ID =
RestaurantLocation.Restaurant_ID WHERE (RestaurantLocation.Active = 1)")
row = cursor.fetchall()
columns = [column[0] for column in cursor.description]
for ID,DisplayName in row:
DisplayName = DisplayName.replace(" ","")
cursor = cnxn.cursor()
print(firstDay,lastDay,ID)
if ID == 13:
percentage = 0.18
else:
if ID == 25:
percentage = 0.18
else:
percentage = 0.20
cursor.execute("SET NOCOUNT ON; SELECT ScheduledService.ServiceDate,
Restaurant.DisplayName AS RestaurantName,
InvoiceLineItem.Invoice_ID, ScheduledRestaurantService.Location_ID,
ScheduledService.Service_ID, Service.DisplayName AS ServiceName,
InvoiceLineItem.Description, InvoiceLineItem.Amount AS 'Subtotal'
INTO #Subtotal FROM ScheduledService INNER JOIN Invoice ON
ScheduledService.Invoice_ID = Invoice.ID INNER JOIN InvoiceLineItem
ON Invoice.ID = InvoiceLineItem.Invoice_ID INNER JOIN Payment ON
Invoice.ID = Payment.RelatedInvoice_ID INNER JOIN
ScheduledRestaurantService ON ScheduledService.ID =
ScheduledRestaurantService.ID INNER JOIN RMenuAllocatedScheduleSlot
ON ScheduledRestaurantService.TimeSlot_ID =
RMenuAllocatedScheduleSlot.ID INNER JOIN [User] ON
ScheduledService.RelatedUser_ID = [User].ID INNER JOIN
RestaurantLocation ON ScheduledRestaurantService.Location_ID =
RestaurantLocation.ID INNER JOIN Restaurant ON
RestaurantLocation.Restaurant_ID = Restaurant.ID INNER JOIN Service
ON ScheduledService.Service_ID = Service.ID WHERE
(ScheduledService.ServiceDate BETWEEN ? AND ?) AND (NOT
(ScheduledService.Status_ID = 6)) AND (ScheduledService.Service_ID
IN (17, 18, 23, 24, 25)) AND (InvoiceLineItem.Description IN
('Subtotal')) AND (ScheduledRestaurantService.Location_ID = ?)
SELECT #Subtotal.Invoice_ID, InvoiceLineItem.Amount AS 'SalesTax'
INTO #SalesTax FROM #Subtotal LEFT OUTER JOIN InvoiceLineItem ON
#Subtotal.Invoice_ID = InvoiceLineItem.Invoice_ID WHERE
(InvoiceLineItem.Description IN ('Sales Tax')) SELECT
#Subtotal.Invoice_ID, CASE WHEN #Subtotal.Service_ID IN
(17,23,25,26) THEN 0.00 ELSE InvoiceLineItem.Amount END AS 'Tip'
INTO #Tip FROM #Subtotal LEFT OUTER JOIN InvoiceLineItem ON
#Subtotal.Invoice_ID = InvoiceLineItem.Invoice_ID WHERE
(InvoiceLineItem.Description IN ('Tip')) Select
#Subtotal.ServiceDate, #Subtotal.RestaurantName,
#Subtotal.Invoice_ID, #Subtotal.Location_ID, #Subtotal.Service_ID,
#Subtotal.Subtotal, #SalesTax.SalesTax, #Subtotal.Subtotal * .? AS
Discount, #Tip.Tip Into #Final From #Subtotal LEFT OUTER JOIN
#SalesTax ON #Subtotal.Invoice_ID = #SalesTax.Invoice_ID LEFT OUTER
JOIN #Tip ON #Subtotal.Invoice_ID = #Tip.Invoice_ID SET NOCOUNT OFF;
Select * From #Final Order By #Final.Service_ID, #Final.ServiceDate
Drop Table #Subtotal Drop Table #SalesTax Drop Table #Tip Drop Table
#Final", (firstDay,lastDay,ID,percentage))
rows = cursor.fetchall()
The error is being thrown by the value I am assigning to "percentage". I first tried to fix this by using int(), so my IF statement became:
if ID == 13:
percentage = int(0.18)
else:
if ID == 25:
percentage = int(0.18)
else:
percentage = int(0.20)
My error there was not recognizing the int() was converted using no decimal places. So I updated the IF statement to use float():
if ID == 13:
percentage = float(0.18)
else:
if ID == 25:
percentage = float(0.18)
else:
percentage = float(0.20)
After using float() to set "percentage" I get the exact same error from SQL. I have tried multiple variations of the IF statements above, but they all result in the exact same error.
What am I doing wrong?
The error is caused by the period (decimal point) immediately before the fourth parameter placeholder:
... #Subtotal.Subtotal * .? AS Discount
That is causing pyodbc to call sp_prepexec with a query like
SELECT ... #Subtotal.Subtotal * .@P1 AS Discount FROM #Subtotal
which is invalid syntax.
Parameter placeholders should always appear on their own and never rely on delimiters or other elements of the command text to modify their behaviour. In this case,
sql = "SELECT id, Subtotal, #Subtotal.Subtotal * ? AS Discount FROM #Subtotal"
params = (Decimal('0.05'),)
crsr.execute(sql, params)
which sends
exec sp_prepexec @p1 output,N'@P1 numeric(3,3)',N'SELECT id, Subtotal, #Subtotal.Subtotal * @P1 AS Discount FROM #Subtotal',50
and executes without error.