April 18, 2013 at 10:02 am
Table with Values
CREATE TABLE InitialData
(
recno int PRIMARY KEY,
Dept Varchar(30),
entry_name Varchar(50),
entry varchar(500),
orgID int
)
INSERT INTO InitialData Values
(1, 'Marketing', 'Timesservedstartdate', '8/6/2012 12:00:00 AM', 1234),
(2, 'Sales', 'Timesservedstartdate', '8/6/2012 12:00:00 AM', 1234),
(3, 'Development', 'Reporting', 'Somevalue', 1234),
(4, 'HumanResources', 'Reporting', '1', 1234),
(5, 'Support', 'Reporting', '1', 1234);
Trying to test the condition that i developed...i want to compare all the rows with the entry value '1'. Any values other than 1 should be equal to 0 is the condition. NOTE: entry is a varchar column
IF (SELECT COALESCE((SELECT dI.[entry] FROM dbo.InitialData AS dI WITH(NOLOCK) WHERE dI.Dept = 'Reporting'
AND dI.entry_name = 'PledgeRequireBatch'
AND dI.orgID = 1234
AND dI.[entry] <> 1), 0)) = 1
BEGIN
Select * from InitialData
END
ELSE
Select TOP 1 * FROM InitialData
I am getting the else statement result always but that is not what i want
1MarketingTimesservedstartdate8/6/2012 12:00:00 AM1234
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 10:32 am
becuase it is searching for a not null value. no row returned is being treated as null that sounds reasonable.
can you check the result of below query? this will answer your question.
SELECT COALESCE( (select entry from InitialData where 1=2 ),null, null, null, null,null,null,'finally')
Seraj
April 18, 2013 at 10:35 am
select isnull((select 1 where 1=2 ),1)
April 18, 2013 at 10:40 am
Sorry but I dint understand how the solution u provided will answer my question..
I have no doubt about the coalesce function i used... But the string comparision is my question.. it is supposed to return the first statement after if condition..
In other words as the condition i used is right, it is supposed to execute the first statement after 'if'
but it is executing the statemnt after else i.e, it is failing
So the error is in string conversions for sure as the sysntax there ie perfectly allright..
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 10:44 am
Yes, only else part will execute as the condition is not true. What is confusion? do you think 0=1 is correct?
April 18, 2013 at 10:56 am
Seraj Alam-256815 (4/18/2013)
Yes, only else part will execute as the condition is not true. What is confusion? do you think 0=1 is correct?
My requirement is to
-Get the value stored in the InitialData.entry column where the Dept is ‘Support’, the entry_name is ‘Reporting’ and the OrgID is equal to 1234.
oThe value of InitialData.entry can be ‘1’, ‘0’, NULL or record not present in my actual table.
oIf the value of InitialData.entry is not equal to ‘1’ then assume ‘0’
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 11:23 am
and assume what if no row qualified?
April 18, 2013 at 11:33 am
which is NULL and equals to 0
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 11:36 am
prathibha_aviator (4/18/2013)
Seraj Alam-256815 (4/18/2013)
Yes, only else part will execute as the condition is not true. What is confusion? do you think 0=1 is correct?My requirement is to
-Get the value stored in the InitialData.entry column where the Dept is ‘Support’, the entry_name is ‘Reporting’ and the OrgID is equal to 1234.
oThe value of InitialData.entry can be ‘1’, ‘0’, NULL or record not present in my actual table.
oIf the value of InitialData.entry is not equal to ‘1’ then assume ‘0’
Your nested select is asking for a value where dI.Dept = 'Reportin AND dI.entry_name = 'PledgeRequireBatch'; but there is no row that satisfies either of those conditions because PledgeRequireBatch isn't anywhere in the data, and Reporting is an entry_name not a Dept, so the nested select delivers an empty rowset, not a singleton; the empty rowset enclosed in brackets and treated as a value converts to NULL since there's no [entry] value for it to deliver. So the if condition amounts to
IF (select coalesce(NULL, 0)) = 1
which simplifies naturally to
IF 0 = 1
which certainly is going to take the else branch, not the then branch.
Tom
April 18, 2013 at 11:43 am
sry i gave wrong values in the condition.. pls excuse
here is the condition now
IF (SELECT COALESCE((SELECT dI.[entry] FROM dbo.InitialData AS dI WITH(NOLOCK) WHERE dI.Dept = 'Support'
AND dI.entry_name = 'Reporting'
AND dI.orgID = 1234
AND dI.[entry] <> 1), 0)) = 1
BEGIN
Select * from InitialData
END
ELSE
Select TOP 1 * FROM InitialData
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 11:54 am
Why are you using <>1? If you want the condition to be true it needs to be dI.[Entry]=1. Otherwise it will always result in a null which coalesce converts to 0.
April 18, 2013 at 11:56 am
Your modified query also does not return any rows, so it will still fire the else part.
- Rex
April 18, 2013 at 12:03 pm
okay what if we do this
IF (SELECT COALESCE((SELECT dI.[entry] FROM InitialData AS dI WITH(NOLOCK) WHERE dI.dept= 'Support'
AND dI.entry_name = 'Reporting'
AND orgID = 1234) , 0)) = 1
BEGIN
Select * from InitialData
END
ELSE
Select TOP 1 * FROM InitialData
still the first statement executes...:(
--Pra:-):-)--------------------------------------------------------------------------------
April 18, 2013 at 12:08 pm
prathibha_aviator (4/18/2013)
Table with Values...
Trying to test the condition that i developed...i want to compare all the rows with the entry value '1'....
Am I missing something?
What's wrong with
select * from dI where [entry] = 1
April 18, 2013 at 12:12 pm
This is an example i gave u... with created tables and column values...and datatypes exactly replicating my original table at work...but I am missing some string conversions in my program.. so i want to know that..
Did u notice that my [entry] column is a varchar(500) datatype???
so while comparing it with numeric values it is not working properly...
this is where I am stuck
--Pra:-):-)--------------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply