Help, My head hurts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help, My head hurts

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-19 : 09:19:34

I have been going round in circles with this.
SELECT COUNT(CLientLedger)
FROM tbBOSS_TransactionIMP
WHERE tbBOSS_TransactionIMP.LedgerKey = @LedgerKey
AND IMPSequence = 1 AND ClientLedger IS NULL

The selected data either all records will have a client ledger or the client ledger will be a null.

i tried writing a case statement to return a 1 if the client ledger is Null, or 0 if there are client ledgers

help !

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 09:23:49

I'm not sure what do you want to see as the result...
Isn't it possible to just do a COUNT(*)?

No, you're never too old to Yak'n'Roll if you're too young to die.

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-19 : 09:30:44

quote:
Originally posted by webfred

I'm not sure what do you want to see as the result...
Isn't it possible to just do a COUNT(*)?

No, you're never too old to Yak'n'Roll if you're too young to die.

I have tried, but for some reason , i just get the count of total records in the table regardless if the ClientLedger is a Null or Not

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-19 : 09:34:47

What are you trying to get as a result? It's very unclear to me...

Anyway, you might want to add some parentheses, like so:


SELECT COUNT(CLientLedger) 
FROM tbBOSS_TransactionIMP 
WHERE (tbBOSS_TransactionIMP.LedgerKey = @LedgerKey )
  AND 
      ((IMPSequence = 1) 
        AND (ClientLedger IS NULL))

FischMan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 09:39:45

create table test_tbBOSS_TransactionIMP(LedgerKey int,IMPSequence int,ClientLedger int)

insert test_tbBOSS_TransactionIMP
select 47,1,NULL union all
select 47,1,90 union all
select 47,1,NULL union all
select 33,0,NULL union all
select 47,0,NULL union all
select 47,1,NULL

declare @LedgerKey int
set @LedgerKey = 47

SELECT COUNT(CLientLedger), COUNT(*)
FROM test_tbBOSS_TransactionIMP
WHERE test_tbBOSS_TransactionIMP.LedgerKey = @LedgerKey
AND IMPSequence = 1 AND ClientLedger IS NULL

drop table test_tbBOSS_TransactionIMP

--(6 row(s) affected)

------------- -----------
--0 3
--Warning: Null value is eliminated by an aggregate or other SET operation.

--(1 row(s) affected)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 09:40:58

As you can see COUNT(ClientLedger) gives 0 and COUNT(*) gives 3 (which is correct).

No, you're never too old to Yak'n'Roll if you're too young to die.

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-01-19 : 09:45:45

quote:
Originally posted by FischMan2

What are you trying to get as a result? It's very unclear to me...

Anyway, you might want to add some parentheses, like so:

SELECT COUNT(CLientLedger)
FROM tbBOSS_TransactionIMP
WHERE (tbBOSS_TransactionIMP.LedgerKey = @LedgerKey )
AND
((IMPSequence = 1)
AND (ClientLedger IS NULL))

FischMan

Hi I tried that and no change in the result.

If the select statement was to return say 10 records, what i want is a count of how many of those records where ClientLedger is a NULL value

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-19 : 09:48:08

Slight rewrite:


DECLARE @test_tbBOSS_TransactionIMP TABLE (LedgerKey int, IMPSequence int, ClientLedger int)

insert @test_tbBOSS_TransactionIMP (LedgerKey, IMPSequence, ClientLedger)
select 47, 1, NULL union all
select 47, 1, 90   union all
select 47, 1, NULL union all
select 33, 0, NULL union all
select 47, 0, NULL union all
select 47, 1, NULL

declare @LedgerKey int
set @LedgerKey = 47

SELECT COUNT(ClientLedger) as CLCount, COUNT(*) AS AllCount 
FROM @test_tbBOSS_TransactionIMP test_tbBOSS_TransactionIMP 
WHERE (test_tbBOSS_TransactionIMP.LedgerKey = @LedgerKey) 
AND ((IMPSequence = 1) AND (ClientLedger IS NULL))

--drop table test_tbBOSS_TransactionIMP

Gives:


CLCount	AllCount
0	3

Which is correct. Which means you're brilliant.

FischMan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-19 : 09:52:31

quote:

Originally posted by FischMan2

Slight rewrite:


DECLARE @test_tbBOSS_TransactionIMP TABLE (LedgerKey int, IMPSequence int, ClientLedger int)

insert @test_tbBOSS_TransactionIMP (LedgerKey, IMPSequence, ClientLedger)
select 47, 1, NULL union all
select 47, 1, 90   union all
select 47, 1, NULL union all
select 33, 0, NULL union all
select 47, 0, NULL union all
select 47, 1, NULL

declare @LedgerKey int
set @LedgerKey = 47

SELECT COUNT(ClientLedger) as CLCount, COUNT(*) AS AllCount 
FROM @test_tbBOSS_TransactionIMP test_tbBOSS_TransactionIMP 
WHERE (test_tbBOSS_TransactionIMP.LedgerKey = @LedgerKey) 
AND ((IMPSequence = 1) AND (ClientLedger IS NULL))

--drop table test_tbBOSS_TransactionIMP

Gives:


CLCount	AllCount
0	3

Which is correct. Which means you're brilliant.

FischMan

thx

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-19 : 11:16:59

Don't pat yourselves on the back too soon

Pete_N: Something you said didn't really add up,

quote:

The selected data either all records will have a client ledger or the client ledger will be a null.
....

But the SQL you wrote doesn't do that.


SELECT COUNT(CLientLedger) 
FROM tbBOSS_TransactionIMP 
WHERE tbBOSS_TransactionIMP.LedgerKey = @LedgerKey 
AND IMPSequence = 1 AND ClientLedger IS NULL

So thats any row where: LedgerKey is equal to the variable AND the IMPSequence equals 1 AND the ClientLedger IS NULL.

That means that you won't ever get rows where ClientLedger is not NULL. Which is a condradiction to what you say (in English) you wanted.

I can't possibly see how you would get the result you stated:

quote:

I have tried, but for some reason , i just get the count of total records in the table regardless if the ClientLedger is a Null or Not

That is unless EVERY row in the table had a ClientLedger of NULL
a COUNT on clientLedger will *always* give you a 0 result because of your WHERE clause (asuming ANSI NULL settings)

I don't know exactly what you want. Can you give us some sample data and expected results please?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-19 : 11:19:27

quote:
Originally posted by FischMan2

What are you trying to get as a result? It's very unclear to me...

Anyway, you might want to add some parentheses, like so:

SELECT COUNT(CLientLedger)
FROM tbBOSS_TransactionIMP
WHERE (tbBOSS_TransactionIMP.LedgerKey = @LedgerKey )
AND
((IMPSequence = 1)
AND (ClientLedger IS NULL))

FischMan

There is no difference. The Parentheses do nothing except hide the code.

3 AND conditions will always flatten down to:


a = b
AND c = d
AND e = f

it doesn't matter how you dress them up.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources