Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Queries not Functioning in SQL but did on an Access Database. 1

Status
Not open for further replies.

rwn

Technical User
Dec 14, 2002
420
US
The query below ran just fine in Excel against an Access Database, but now trying to run on a SQL database it fails with this message. (script error near =. Upon research the line of code that has the issue is "SELECT Sum(iif(backorder_qty=0,SO_Detail.Total_Price,backorder_qty*unit_cost)) AS 'Open Sales Orders - MONTH'". For if I remove the 'Open Sales Orders - MONTH'" it runs fine.


Query:
SELECT Sum(iif(backorder_qty=0,SO_Detail.Total_Price,backorder_qty*unit_cost)) AS 'Open Sales Orders - MONTH'
FROM `\\account-win2k10\JobBOSS Server\Database\jobboss`.Customer Customer, `\\account-win2k10\JobBOSS Server\Database\jobboss`.SO_Detail SO_Detail, `\\account-win2k10\JobBOSS Server\Database\jobboss`.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='sales-molded') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='sales-molded'))


 
Hi rwn,

porting queries from Access to SQL Server is most often more than just copy/paste.
In your case, there are at least two things that do not work the same way in SQL Server than in Access.

(I assume you're using SQL Server 2012 or older)

First issue: the iif-expression is not supported in SQL Server. You need to use the case-expression to rewrite your SUM-Expression as
SQL:
sum(case when backorder_qty=0 then SO_Detail.Total_Price else backorder_qty*unit_cost end)

Second issue:
You are referencing tables by their absolute path in the Windows Domain. This is not the way SQL Server works. SQL Server uses Server names to identify the SQL Server instance where the database is located. The table Name follows the Format [database Name].[Schema Name].[table Name]. To reference tables from different Servers, you cannot just Name the Server, but you have to use the feature named "linked server" (google for sp_addlinkedserver).

yours
Guenter
 
Hi Guenter- Thank you for the kind reply. I changed the iif to Case as shown below.
The Select Query runs if I remove this (AS 'Open Sales Orders - MONTH'). If I add this back, then the error is produced.

SELECT Sum(CASE(backorder_qty=0 THEN SO_Detail.Total_Price ELSE backorder_qty*unit_cost))

FROM `\\account-win2k10\JobBOSS Server\Database\jobboss`.Customer Customer, `\\account-win2k10\JobBOSS Server\Database\jobboss`.SO_Detail SO_Detail, `\\account-win2k10\JobBOSS Server\Database\jobboss`.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='sales-molded') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='sales-molded'))
 
The AS clause should be enclosed in square brackets [] instead of single quotes.

Change
SQL:
AS 'Open Sales Orders - MONTH'

to

SQL:
AS [b][[/b]Open Sales Orders - MONTH[b]][/b]

-- Francis
Francisus ego, sed non sum papa.
 
rwn, also the code you posted back is missing the END part of the CASE statement. But I expect it would not run at all without it.
 
I truly appreciate the guidance and advise. the query below works. Wonderfull success!!
SELECT SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code
FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?))

But I add the following with your great suggestions, it still fails. I must be overlooking the obvious.

SELECT sum(case when backorder_qty=0 then SO_Detail.Total_Price else backorder_qty*unit_cost end)AS AS [Open Sales Orders - MONTH]

SELECT SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code
FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?))
 
How are you executing this query... SQL Server will not like parameters as question marks (nor Access for that matter)..

That said your original iif is suspect without a false part. Additionally you have an extra as keyword for your alias.

Code:
SELECT sum(case when backorder_qty=0 then SO_Detail.Total_Price else backorder_qty*unit_cost [red]ELSE NULL[/red] end)[red][b][i] AS[/i][/b][/red] [Open Sales Orders - MONTH] 
FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?))
 
This is a query within Excel and excuting against a SQL database. Regarding the question marks, the query runs fine with the questions marks and the only time the query fails is when add the first SELECT row statement (RED).
[highlight #A40000]SELECT sum(case when backorder_qty=0 then SO_Detail.Total_Price else backorder_qty*unit_cost end) AS [Open Sales Orders - MONTH][/highlight]
SELECT SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code
FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?))
 
SQL statments only have one Select clause, you can nest / use subqueries in a from clause but you can't add an aggregate function to a select and have other columns returned that are not aggregates nor in the group by clause. Beyond that, you are in a SQL server forum with syntax that does not work in SQL server asking for help without specics on how this ends up passed to the server telling us the statment works...
 
Thanks, so my only option is a sub queries for what i need to add in the red above? my problem, is I have no clue how to do, but thanks to all for you help!
 
I have no idea why you contue with SELECT after the red line. If you instead put a comma there, and let the field list continue with SO_Detail.SO_DetailKey etc, this query could already work.

In short you hav

SELECT expression AS [Open Sales Orders - MONTH]
SELECT fieldlist FROM tablelist
WHERE clauses

This is not correct syntax SQL. It's two queries, of which the first one is incomplete, having no FROM clause. Your expression for the [Open Sales Orders - MONTH] in red, is just one field of your field list, and you continue with further fields with a comma.

You can do
SELECT expression AS [Open Sales Orders - MONTH]
,fieldlist FROM tablelist
WHERE clauses

Bye, Olaf.
 
Olaf, I think you are missing the sum in the expression...

rwn, I think you need to identify what you wan to display in your query or what you want to sum for...

Both of the below should execute assuming the ? are still processed the same on your non-SQL end. The first adds grouping according to the first select so it shows all the columns and the sum for the other columns grouped. The second uses a subquery aliased to A. A sub query acts basically like another query and the columns returned all come from that subquery to the next query up so you have to change the table use accordingly. Hopefully that gets you going in the right direction.

Code:
SELECT SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code, sum(case when backorder_qty=0 then SO_Detail.Total_Price else backorder_qty*unit_cost end) AS [Open Sales Orders - MONTH]
FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days Between ? And ?))  
Group By SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code

Code:
Select sum(case when A.backorder_qty=0 then A.Total_Price else A.backorder_qty*A.unit_cost end) AS [Open Sales Orders - MONTH]
From 
(
     Select backorder_qty, SO_Detail.Total_Price, unit_cost
     FROM TRAINING.dbo.Customer Customer, TRAINING.dbo.SO_Detail SO_Detail, TRAINING.dbo.SO_Header SO_Header
     WHERE SO_Header.Customer = Customer.Customer AND SO_Detail.Sales_Order = SO_Header.Sales_Order AND ((SO_Detail.Status='open') AND (so_detail.promised_date-          ship_lead_days Between ? And ?) AND (SO_Detail.Sales_Code='fabr sales') OR (SO_Detail.Status='backorder') AND (so_detail.promised_date-ship_lead_days      Between ? And ?))  
     Group By SO_Detail.SO_DetailKey, SO_Detail.SO_Detail, SO_Detail.Sales_Order, SO_Detail.SO_Line, SO_Detail.PO, SO_Detail.Line, SO_Detail.Material, SO_Detail.Job, SO_Detail.Status, SO_Detail.Status, SO_Detail.Sales_Code
) A
 
lameid, I was abbreviating the syntax to make clear there is a double SELECT, where one of them simply should be a comma. I replace the whole sum with the word "expression". You might recognize it better, if I had said "term" instead.

Bye, Olaf.
 
Olaf,

I was pointing out that if you have an aggregate function such as sum, you also need a group by clause if you are including columns not derived by using aggregate functions.
 
Ah, I see. That's a fair comment. I see you have pinned it overall, anyway.

Bye, Olaf.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top