SELECT Assertion

[NeoPa]

I am trying to return a boolean value within a SELECT clause using an assertion. The working code is below, but it uses a CASE statement which I find quite clumsy :

SELECT [PR_PRODUCT]
      ,[PR_COST]
      ,CASE WHEN ([PR_COST]=0)
       THEN 'TRUE'
       ELSE 'FALSE' END AS [CostMissing]

FROM   [DBManagement].[dbo].[vwCustStock]

WHERE  ([PR_RANGE] In(70,91))

GO

The two failed versions I've tried are below, with their associated error messages :

SELECT [PR_PRODUCT]
      ,[PR_COST]
      ,([PR_COST]=0) AS [CostMissing]

FROM   [DBManagement].[dbo].[vwCustStock]

WHERE  ([PR_RANGE] In(70,91))

GO

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.

...and using a CAST :

SELECT [PR_PRODUCT]
      ,[PR_COST]
      ,(DT_BOOL)([PR_COST]=0) AS [CostMissing]

FROM   [DBManagement].[dbo].[vwCustStock]

WHERE  ([PR_RANGE] In(70,91))

GO

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'PR_COST'.

I have another question related to this, which is How to Return TRUE/FALSE Values. As you can see, I'm currently working with string values, which is not my eventual intention.

[romiverma]

hi try this

SELECT [PR_PRODUCT]
      ,[PR_COST]
      ,CASE [PR_COST] WHEN 0
       THEN 'TRUE'
       ELSE 'FALSE' END AS [CostMissing]

FROM   [DBManagement].[dbo].[vwCustStock]

WHERE  ([PR_RANGE] In(70,91))

[NeoPa]

Hi Romi.

Unfortunately this is not an assertion (as requested), but just another way of writing the CASE statement. I know how to use a CASE statement (as shown in my first post). What I'm looking to find is an equivalent assertion.

Thank you anyway for offering your time to make the suggestion :)

[ck9663]

Modifying your fist sample code. Try if this works

SELECT [PR_PRODUCT]
      ,[PR_COST]
      ,cast([PR_COST] as bit) ^ 1 as  [CostMissing] 
FROM   [DBManagement].[dbo].[vwCustStock]
WHERE  ([PR_RANGE] In(70,91))
 
GO

You still have to display 'TRUE' for 1 and 'FALSE' for 0.

Happy Coding!!!

~~ CK

[NeoPa]

Clever thinking CK. I'm more looking for a generic way of handling conversion of any boolean expression into a usable value though. The clearer it is when read the better too. I'd rather avoid anything too obscure, though I admit to liking the concept that anything that doesn't equal zero resolves to 1 when raised to the power of 0 (that may have been a typo in your suggestion I'm guessing), whereas zero resolves to zero. A perfect concept for dealing with booleans :) In fact, so perfect I may use it more generally after all.

[NeoPa]

Well, I've just discovered that 0^0 is not so much 0 as #Error :(

Well, it was good thinking anyway. ^1 provides no benefit I can see (It does treat 0 as 0, but so would it before applying ^1), so I think I'm back to base with this one. I spoke with a couple of T-SQL experts at work and they can't comprehend why I don't simply use CASE WHEN blah blah blah END. I used to think of Access's IIf() function as clumsy and hard to determine the meaning of. Oh well. We deal with the environment we find ourselves in I suppose.

I'm open to ay further ideas, but I suspect this is a dead-end.

[ck9663]

I'm a little confuse on the requirement...

The 0 ^ 0 will return 0. The ^ operator returns 0 if both of expressions are equal. Hence 1 ^ 1 = 0; 1 ^ 0 = 1; 0 ^ 1 = 1; 0 ^ 0 = 0.

The code cast([PR_COST] as bit) ^ 1 will convert your PR_COST to bit.

Requirement 1:
All non-zero value of PR_COST will be converted to bit 1. So 1 ^ 1 = 0(False). Which is based on your CASE statement, all non-zero PR_COST should have a CostMissing = FALSE. Then it's achieved.

Requirement 2:
All zero value of PR_COST will be converted to bit 0. So 0 ^ 0 = 1 (True). Which is based on your CASE statement, all zero PR_COST should have a CostMissing = TRUE. This, too, is achieved.

Requirement 3:
No CASE..WHEN..ELS E..END statement used. This, too, is achieved.

Did I miss anything?

~~ CK

[NeoPa]

The carat (^) referring then to an Exclusive Or (XOR) then, rather than raise to the power of (As in =3^4 in Excel gives 81).

Let me look at this again in this new context.

[ck9663]

Yes. In SQL Server, there's a POWER() function to handle the raise to the power requirement :)

Good Luck!!!

~~ CK

[NeoPa]

All you say is true, except the first requirement is to work from an assertion. I am looking at this generally, and the [PR_COST] issue is simply an example. Simplified to illustrate, rather than define the issue. I'm thinking of utilising the concept more generally to flag criteria that may be met in exception reporting (Just trying to give some background as to why I'm asking to understand something that most don't seem to care about). If I have many exceptions to find, and each is defined by a set of criteria of undefined complexity, then I want to include the the set of criteria in the WHERE clause, as well as illustrate what caused the particular record to be included in the exception report. Each possible reason, or set of criteria, would have it's own column indicating the truth or otherwise of that reason for inclusion.

You have found a clever way of converting a value to a boolean value. What I need for this to work though, is to do the same for an assertion. An assertion would be defined as a set of criteria that could be used in a WHERE clause. ([PR_COST=0) is an assertion. Unfortunately, [PR_COST] on its own is not. It does lend itself however, to boolean manipulation to produce a good boolean result. I don't believe the same could be said of a more complex assertion such as (([PR_COST] IN(0,5,7,99)) AND ([PR_SELL] IS NOT NULL)) though.

Please don't think I'm trying to be picky. I'm impressed with your solution. It simply misses the point about the assertion (as far as I can see).

[ck9663]

Understood :) I just thought I was not able to help solve your problem...

Happy Coding!!!

~~ CK

[NeoPa]

Cheers CK.

I'm expecting to be doing more SQL Server work in future and I'm reassured to know you're around. I'm afraid my questions are unlikely to be very basic, but I'll always do my best to explain them clearly at least. They may also reflect a certain way of thinking that's more in tune with Access than SQL Server, but I expect I'll get there in the end ;)