February 26, 2008 at 12:39 pm
In the SQL below, I also need to return 'Y' when SYCFStore is empty.
Everything I've tried fails with a 0 records returned.
What needs to be done here to return the desired result?
SELECT CASE WHEN s.Store_Inactive_Flag = 'N' AND sy.STORE_SYSTEM_FLAG = 'N'
THEN 'N'
ELSE 'Y'
END
FROM SYCFStore s, SYCFSYSTEM sy
WHERE s.Store_Key = @Store_Key
February 26, 2008 at 12:45 pm
Couple problems here.
You're missing a join between the two tables SYCFStore s, SYCFSYSTEM sy. Means you'll get a cross join if they have data in them.
The from clause should be something like this
FROM SYCFStore s INNER JOIN SYCFSYSTEM sy ON < Join condition here >
That aside, the case statement won't exclude data. It's in the select clause. If you run the following, what rowcount do you get?
SELECT count(*) AS TheRowCount
FROM SYCFStore s, SYCFSYSTEM sy
WHERE s.Store_Key = @Store_Key
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2008 at 1:20 pm
These tables are not related and contain no foreign keys to each other.
February 26, 2008 at 1:29 pm
You might have to add:
if not exists (select * from SYCFStore where store_key = @store_key)
select 'Y'
That might do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2008 at 1:31 pm
MrBaseball34 (2/26/2008)
These tables are not related and contain no foreign keys to each other.
Then whatever you're passing as @storekey is not returning anything.
Edit: as in - it matches no record in "s".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 1:34 pm
It would help if you could give us sample input and desired output. I think I'm still misunderstanding what you have and what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 26, 2008 at 1:47 pm
ok...the SQL above is used as a filter for building the menu for our software
and if the result of the query is 'Y', the menu is filtered out and not shown.
SYCFStore keeps store info.
SYCFSYSTEM keeps system info.
If we do an initial install, there is not yet any store info in SYCFSTORE thus
if there are no records, yet, we don't want to show the menu.
If there is a store with the passed store_key and the Store_Inactive_Flag = 'N'
and the SYCFSYSTEM.Store_System_Flag = 'N' then we want to return 'N",
meaning the menu will show.
February 26, 2008 at 1:49 pm
BTW...
SYCFStore can have many records, SYCFSystem can have only one record.
February 26, 2008 at 2:16 pm
MrBaseball34 (2/26/2008)
ok...the SQL above is used as a filter for building the menu for our softwareand if the result of the query is 'Y', the menu is filtered out and not shown.
SYCFStore keeps store info.
SYCFSYSTEM keeps system info.
If we do an initial install, there is not yet any store info in SYCFSTORE thus
if there are no records, yet, we don't want to show the menu.
If there is a store with the passed store_key and the Store_Inactive_Flag = 'N'
and the SYCFSYSTEM.Store_System_Flag = 'N' then we want to return 'N",
meaning the menu will show.
Its not written as a single select statement, but you may want to try something like this:
if(
(exists (select * from SYCFStore WHERE s.Store_Key = @Store_Key AND s.Store_Inactive_Flag = 'N')
AND (exists (select * from SYCFSYSTEM sy where sy.STORE_SYSTEM_FLAG = 'N'))
)
select 'Y'
Else
Select 'N'
As was noted before in the thread, the way you have it written it will try to do a crossjoin, and a cross join with an empty table returns nothing. An empty table is not the same as a table with null values.
Which incidentally is the other way to get what you want from the query as you have it written. Instead of letting either table be empty, ensure it is always populated with at least one row, even if that row is all nulls. Of course, doing that you will get a result set back which consists of one Y or N for every row that is in the table with many rows, using the if statement outside the select will return back precisely one Y or one N.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 26, 2008 at 2:22 pm
using your code:
declare @StoreKey int
select @StoreKey = 1
IF ((exists (select * from SYCFStore s WHERE s.Store_Key = @StoreKey AND s.Store_Inactive_Flag = 'N') AND
(exists (select * from SYCFSYSTEM sy where sy.STORE_SYSTEM_FLAG = 'N')))
select 'Y'
else
select 'N'
I get an error:
Incorrect syntax near the keyword 'select'. on the select 'Y' line.
February 26, 2008 at 5:31 pm
Sorry, I dropped off a parenthesis in there. Try:
declare @StoreKey int
select @StoreKey = 1
IF ((exists (select * from SYCFStore s WHERE s.Store_Key = @StoreKey AND s.Store_Inactive_Flag = 'N') AND
(exists (select * from SYCFSYSTEM sy where sy.STORE_SYSTEM_FLAG = 'N'))))
select 'Y'
else
select 'N'
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 27, 2008 at 9:05 am
BTW, because of the way things are being run, I cannot use a DECLARE or IF, so...
Here is the final working code:
SELECT CASE
WHEN EXISTS(SELECT 1 FROM SYCFStore s WHERE s.Store_Key = @Store_Key)
THEN (SELECT CASE WHEN s.Store_Inactive_Flag = 'N' AND sy.STORE_SYSTEM_FLAG = 'N'
THEN 'N'
ELSE 'Y'
END
FROM SYCFStore s, SYCFSYSTEM sy
WHERE s.Store_Key = @Store_Key)
ELSE 'Y'
END
Thanks for taking a look at my problem and helping me out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply