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 : - 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.
11 2895
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 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 :)
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 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.
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.
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
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.
Yes. In SQL Server, there's a POWER() function to handle the raise to the power requirement :)
Good Luck!!!
~~ CK
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).
Understood :) I just thought I was not able to help solve your problem...
Happy Coding!!!
~~ CK
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 ;)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: howa |
last post by:
e.g.
<div class="main">
<p>ssssss</p>
<p>ssssss</p>
<p>ssssss</p>
<p>ssssss</p>
|
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...
|
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...
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |