SQL Server Query (1 Viewer)

Krayna

Member
Local time
Today, 00:37
Joined
Sep 24, 2020
Messages
512
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:
SQL:
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;
 

Attachments

  • Screenshot 2021-10-20 071145.png
    Screenshot 2021-10-20 071145.png
    28.9 KB · Views: 20

Galaxiom

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Jan 20, 2009
Messages
12,391
Use 1 for True and 0 for False in the IIF()
 

Minty

AWF VIP
Local time
Today, 00:37
Joined
Jul 26, 2013
Messages
8,900
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

Super Moderator
Staff member
Local time
Today, 11:37
Joined
Jan 20, 2009
Messages
12,391
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.
 

Krayna

Member
Local time
Today, 00:37
Joined
Sep 24, 2020
Messages
512
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

AWF VIP
Local time
Today, 00:37
Joined
Jul 26, 2013
Messages
8,900
You can, but not by referring to as the alias, you have to repeat the calculation. e.g. This won't work.
Code:
SELECT Field1 * Field2 as Result1 , Result1 / Field3 as Result2
But this will
Code:
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)
Code:
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.
 

Users who are viewing this thread

Top Bottom