473,322 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SELECT Assertion

NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,[PR_COST]
  3.       ,CASE WHEN ([PR_COST]=0)
  4.        THEN 'TRUE'
  5.        ELSE 'FALSE' END AS [CostMissing]
  6.  
  7. FROM   [DBManagement].[dbo].[vwCustStock]
  8.  
  9. WHERE  ([PR_RANGE] In(70,91))
  10.  
  11. GO
The two failed versions I've tried are below, with their associated error messages :
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,[PR_COST]
  3.       ,([PR_COST]=0) AS [CostMissing]
  4.  
  5. FROM   [DBManagement].[dbo].[vwCustStock]
  6.  
  7. WHERE  ([PR_RANGE] In(70,91))
  8.  
  9. GO
  10.  
  11. Msg 102, Level 15, State 1, Line 4
  12. Incorrect syntax near '='.
...and using a CAST :
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,[PR_COST]
  3.       ,(DT_BOOL)([PR_COST]=0) AS [CostMissing]
  4.  
  5. FROM   [DBManagement].[dbo].[vwCustStock]
  6.  
  7. WHERE  ([PR_RANGE] In(70,91))
  8.  
  9. GO
  10.  
  11. Msg 102, Level 15, State 1, Line 4
  12. 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.
Jun 30 '10 #1
11 2891
hi try this
Expand|Select|Wrap|Line Numbers
  1. SELECT [PR_PRODUCT]
  2.       ,[PR_COST]
  3.       ,CASE [PR_COST] WHEN 0
  4.        THEN 'TRUE'
  5.        ELSE 'FALSE' END AS [CostMissing]
  6.  
  7. FROM   [DBManagement].[dbo].[vwCustStock]
  8.  
  9. WHERE  ([PR_RANGE] In(70,91))
Jun 30 '10 #2
NeoPa
32,556 Expert Mod 16PB
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 :)
Jun 30 '10 #3
ck9663
2,878 Expert 2GB
Modifying your fist sample code. Try if this works

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [PR_PRODUCT]
  3.       ,[PR_COST]
  4.       ,cast([PR_COST] as bit) ^ 1 as  [CostMissing] 
  5. FROM   [DBManagement].[dbo].[vwCustStock]
  6. WHERE  ([PR_RANGE] In(70,91))
  7.  
  8. GO
  9.  
  10.  
  11.  
You still have to display 'TRUE' for 1 and 'FALSE' for 0.

Happy Coding!!!

~~ CK
Jun 30 '10 #4
NeoPa
32,556 Expert Mod 16PB
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.
Jun 30 '10 #5
NeoPa
32,556 Expert Mod 16PB
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.
Jul 1 '10 #6
ck9663
2,878 Expert 2GB
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..ELSE..END statement used. This, too, is achieved.

Did I miss anything?

~~ CK
Jul 1 '10 #7
NeoPa
32,556 Expert Mod 16PB
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.
Jul 1 '10 #8
ck9663
2,878 Expert 2GB
Yes. In SQL Server, there's a POWER() function to handle the raise to the power requirement :)

Good Luck!!!

~~ CK
Jul 1 '10 #9
NeoPa
32,556 Expert Mod 16PB
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).
Jul 1 '10 #10
ck9663
2,878 Expert 2GB
Understood :) I just thought I was not able to help solve your problem...

Happy Coding!!!

~~ CK
Jul 2 '10 #11
NeoPa
32,556 Expert Mod 16PB
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 ;)
Jul 2 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Todd Miller | last post by:
Hi, I recently discovered an assertion failure in the Python garbage collection system when scripts using our C extension (numarray) exit. The assertion is activated for Pythons configured using...
4
by: Morgan Leppink | last post by:
Hey all - We are running SQL 2000 with ALL available service packs, etc. applied. We just built a brand new database server, which has dual 2Ghz XEONs, 2GB memory, and the following disk...
3
by: crispin.proctor | last post by:
Hi All, I have started getting Assertion Errors in SQL. It appears when I process a cube (Most of the time) Other SQL statements, usually with a join or 6 do the same thing. Whaving a scratch...
1
by: Timur Safin | last post by:
Hi All, Sorry if it is offtopic here, I wasn't able to find any more relevant group... I'm slowly approaching AMD64 build for our product (as my personal fun project). And after I ran that...
5
by: Ron Louzon | last post by:
I have some C++ code that uses the CSingleLock( CCriticalSection *) constructor. In visual C++ 6.0, this code compiles and runs fine in both Debug and release modes. However, in Visual Studio...
29
by: pb648174 | last post by:
I have the following basic statements being executed: Create a temp table, #TempPaging Insert Into #TempPaging (Col1, Col2) Select Col1, Col2 From SomeOtherTable Order By Col2, Col1 Select...
13
by: howa | last post by:
e.g. <div class="main"> <p>ssssss</p> <p>ssssss</p> <p>ssssss</p> <p>ssssss</p>
0
by: Tooshy | last post by:
Hello everyone. I have a problem with assertions as I found that there's no create assertion in Sql Server 2005 so I'd like to have help in converting that SQL code to SQL Server code that achieve...
1
by: gbharanipriya | last post by:
I have a problem with assertions as I found that there's no create assertion in Sql Server 2005 so I'd like to have help in converting that SQL code to SQL Server code that achieve the same thing, I...
1
by: gbharanipriya | last post by:
I have a problem with assertions as I found that there's no create assertion in Sql Server 2005 so I'd like to have help in converting that SQL code to SQL Server code that achieve the same thing, I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.