SQL Statement Debugging 1

[MattSTech]

I am struggling to see what is wrong with this SQL statement. By the errors it seems to have an issue with the pareths, although I am not sure what alternative I have.
the errors are:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'ladetail'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'ladetail'.

SELECT ladetail.fdate,ladetail.fshift,ladetail.fcompqty,ladetail.fscrpqty,[fcompqty]+[fscrpqty] AS Qty,ladetail.fpro_id,ladetail.fempno,inrtgs.fuprodtime,inrtgs.fsetuptime,
jomast.fquantity,[fsetuptime]/[fquantity] AS [Aloc Setup],([Aloc Setup]+[fuprodtime])*IIf(([fcompqty]+[fscrpqty])>[fquantity],[fquantity],([fcompqty]+[fscrpqty]) AS [Earned Hr]
FROM ladetail INNER JOIN (jomast INNER JOIN inrtgs ON jomast.fpartno = inrtgs.fpartno) ON (ladetail.foperno = inrtgs.foperno) AND (ladetail.fjobno = jomast.fjobno)
WHERE (((ladetail.fdate)=Date()-1) AND ((ladetail.fshift)="1" Or (ladetail.fshift)="2") AND (([fcompqty]+[fscrpqty])>0) AND ((ladetail.fpro_id) In 
("BAR") OR (((ladetail.fdate)=Date()-2) AND 
((ladetail.fshift)="3") AND (([fcompqty]+[fscrpqty])>0) AND ((ladetail.fpro_id) In ("BAR")

 

[SQLBill]

This is not SQL Server code as SQL Server doesn't use IIf. That is Microsoft Access' Jet SQL.

So, if you are using Access, you need to post this in an Access forum. If it SQL Server, then you need to use commands that are part of Transact SQL.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[MattSTech]

Thank you for the quick response.
This is a statement pulled from an access query that I am trying to massage into a usable sql statement for use in VB6. Using ado I can often use access sql statements with a little tweaking.
Is there an equivalent to iif in transact?

 

[markros]

Yes. It will be CASE function. Check BOL
CASE Transact-SQL

PluralSight Learning Library

 

[MattSTech]

Ok, I see. I am missing the next chunk it seems. I converted the IIf statement to a case statement. Now I am getting a fail on the final line because of the ) symbol. I really don't think this is the problem, but I don't really see the problem.

SELECT ladetail.fdate,ladetail.fshift,ladetail.fcompqty,ladetail.fscrpqty,[fcompqty]+[fscrpqty] 
AS Qty,ladetail.fpro_id,ladetail.fempno,inrtgs.fuprodtime,inrtgs.fsetuptime,jomast.fquantity,
[fsetuptime]/[fquantity] AS [Aloc Setup],([Aloc Setup]+[fuprodtime])*

CASE [Earned Hr]
     WHEN '[ladetail.fcompqty]+[ladetail.fscrpqty]>[fquantity]' THEN [ladetail.fquantity] 
     ELSE '[fcompqty]+[fscrpqty]' 
END 

FROM ladetail INNER JOIN (jomast INNER JOIN inrtgs ON jomast.fpartno = inrtgs.fpartno) ON 
(ladetail.foperno = inrtgs.foperno) AND (ladetail.fjobno = jomast.fjobno)WHERE(((ladetail.fdate)=date.now()-1) AND ((ladetail.fshift)="1" Or (ladetail.fshift)="2")
 AND (([fcompqty]+[fscrpqty])>0) AND ((ladetail.fpro_id) In ("BAR") OR (((ladetail.fdate)=Date.now()-2) AND ((ladetail.fshift)="3") AND 
 (([fcompqty]+[fscrpqty])>0) AND (ladetail.fpro_id) In ("BAR")

 

[markros]

Your CASE expression is strange. Are you comparing the Earned Hr column with some character string

'[ladetail.fConpqty] + ...

Why do you need single quotes here? Also, I suggest a different version of the case expression, rather

CASE when [Earned Hour ] = [laDetail].[fCompQty] + ...
then [ladetail.fquantity] else [fcompqty] + [fscrpqty] end

In other words, change the CASE and get rid of single quotes.

Also, in SQL server you put strings in single quotes. So, your last expressions should have single quotes

IN ('BAR')

= '3'

etc.

PluralSight Learning Library

 

[MattSTech]

Alrighty, The case statement worked great. Thanks so much for steering me in the right direction! Here is the translated version for VB

strSQL = "SELECT ladetail.fdate,ladetail.fshift,ladetail.fcompqty,ladetail.fscrpqty," & _
                 "ladetail.fpro_id,ladetail.fempno,inrtgs.fuprodtime,inrtgs.fsetuptime,jomast.fquantity," & _
                 "((fsetuptime/fquantity)+fuprodtime)* (Case WHEN([fcompqty]+[fscrpqty])>[fquantity] THEN [fquantity] ELSE ([fcompqty]+[fscrpqty]) END) AS Earned_Hr " & _
                 "FROM ladetail INNER JOIN (jomast INNER JOIN inrtgs ON jomast.fpartno = inrtgs.fpartno) ON (ladetail.foperno = inrtgs.foperno) AND " & _
                 "(ladetail.fjobno = jomast.fjobno) " & _
                 "WHERE (((ladetail.fdate)='" & DateAdd("d", -1, Format(Now(), "mm/dd/yyyy")) & "') AND " & _
                 "((ladetail.fshift)='1' Or (ladetail.fshift)='2') AND (([fcompqty]+[fscrpqty])>0) AND ((ladetail.fpro_id) In " & _
                 "('BAR')))" & _
                 "OR (((ladetail.fdate)='" & DateAdd("d", -2, Format(Now(), "mm/dd/yyyy")) & "') AND ((ladetail.fshift)='3') AND (([fcompqty]+[fscrpqty])>0) AND ((ladetail.fpro_id) In " & _
                 "('BAR')))"