SQL Server Query (1 Viewer)

Kayleigh

Member
Local time
Today, 08:27
Joined
Sep 24, 2020
Messages
706
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: 222

Minty

AWF VIP
Local time
Today, 08:27
Joined
Jul 26, 2013
Messages
10,355
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, 19:27
Joined
Jan 20, 2009
Messages
12,849
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

Member
Local time
Today, 08:27
Joined
Sep 24, 2020
Messages
706
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, 08:27
Joined
Jul 26, 2013
Messages
10,355
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