SQL Server Query

[Kayleigh]

Hi,
Having trouble with syntax in a pass-through query. Keep getting a syntax error and I don't know T-SQL well enough to understand where I've gone wrong.
My SQL is:

SELECT dbo.tblOrders.*, dbo.tblAddress.*, CAST(ISNULL(([fldototalquote]*([fldOvatpercentage]/100), 0)*([fldovatrate]/100)) AS MONEY) AS cfVat, CAST((ISNULL([fldototalquote], 0)+ISNULL([cfvat], 0))*(ISNULL(([fldoagentcommision]/100),0))*((ISNULL([fldovatrate])/100)+1, 0) AS MONEY) AS cfAgent, ISNULL([fldototalquote], 0)+[cfvat]+[cfAgent] AS cfGrandTotal, CAST(ISNULL([cfgrandtotal]*([fldodepositpercentage]/100), 0) AS MONEY) AS cfDepositAmt, (ISNULL([fldOTotalquote]*((100-[fldovatpercentage])/100), 0)) AS cfMzDue, IIf([fldofinance]=False,CAST(ISNULL([cfgrandtotal]-[cfmzdue],0) AS MONEY), 0) AS cfTfrDue, IIf([fldofinance]=True, CAST(ISNULL((([cfgrandtotal]-[cfmzdue])*0.965),0) AS MONEY),0) AS cfFinanceDue, [cftfrdue]+[cfmzdue]+[cfFinanceDue] AS cfGrandTotalDueIn
FROM dbo.tblAddress RIGHT JOIN (dbo.tblOrders LEFT JOIN dbo.lkptblOrderStatus ON dbo.tblOrders.fldOStatusID = dbo.lkptblOrderStatus.fldOrderStatusID) ON dbo.tblAddress.fldAddressID = dbo.tblOrders.fldOAddressID
WHERE ((dbo.lkptblOrderStatus.fldOrderStatusID) <>12) AND ((dbo.lkptblOrderStatus.fldOSSort)>45 OR (dbo.tblOrders.fldOAnticipatedPayDate IS NOT NULL))
ORDER BY dbo.lkptblOrderStatus.fldOSSort;

 

[Galaxiom]

Use 1 for True and 0 for False in the IIF()

 

[Minty]

If they are bit fields don't they translate to -1 for true and 0 for false?
Or is that if they have been imported from Access?

 

[Galaxiom]

If they are bit fields don't they translate to -1 for true and 0 for false?
Or is that if they have been imported from Access?

In SQL Server, True and False are 1 and 0 respectively for both bit fields and Boolean values.

In Access zero is interpreted as False and all other values as True. The -1 as True in Access is an arbitrary manifestation of Two Compliment where the number -1 is represented by all bits in the word being on and hence each bit as True (being binary 1).

SQL Server stores up to eight bit columns as single byte. So if all the bits were True the numerical value of the byte would indeed be -1.

 

[Kayleigh]

Thank you. I have looked through the syntax and corrected a couple of other things. I find that the main issue is that I can't use a calculated field from the query in another calculated field in the same query. What do you suggest I do?

 

[Minty]

You can, but not by referring to as the alias, you have to repeat the calculation. e.g. This won't work.

SELECT Field1 * Field2 as Result1 , Result1 / Field3 as Result2

But this will

SELECT Field1 * Field2 as Result1 , (Field1 * Field2 ) / Field3 as Result2

It looks cumbersome but it removes any ambiguity.

The other route is to make a query with all the base calculations then query that as a subquery this works well if you have a lot of calculations you need to reuse again;
Something like (AirCode)

SELECT (Stage1.Result1 * 23) as Thing1, (Stage1.Result2 / 1.25) as Thing2 , Stage1.Result1 * Stage1.Result2 as TotalThingy
FROM
(SELECT Field1 * Field2 as Result1 , (Field1 * Field2 ) / Field3 as Result2
FROM MyTable) as stage1

This is really easy to write in SSMS - create the inner sub-query first then do your complex stuff in the outer main query.

 

[Kayleigh]

Thanks. Worked it out in the end