Help on case statement - 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

Other SQL Server 2008 Topics

Help on case statement

Author

Topic

Codesearcher
Starting Member

8 Posts

Posted - 2011-11-24 : 02:03:57

Need help in framing a case statement in SQl 2008 for below scenario

case(select COUNT(*) from tblOwnerInterest where RECV_TOA_GRP_STATUS like '%880156GAS%')=0
then (select CO_NO from tblOwnerInterest where CO_NO='%1%')
when (select COUNT(*) from tblOwnerInterest where RECV_TOA_GRP_STATUS like '%880156GAS%') <> 0
then (select RECV_TOA_GRP_STATUS from tblOwnerinterestPend where RECV_TOA_GRP_STATUS like '%880156GAS%')

when (select RECV_TOA_GRP_STATUS from tblOwnerinterestPend where RECV_TOA_GRP_STATUS like '%880156GAS%') <> 0
then (select COUNT(*) from tblOwnerInterestPend where RECV_TOA_GRP_STATUS like '%880156GAS%')
when (select RECV_TOA_GRP_STATUS from tblOwnerinterestPend where RECV_TOA_GRP_STATUS like '%880156GAS%') = 0
then (select CO_NO from tblOwnerInterestPend where CO_NO='%1%')

when (select COUNT(*) from tblOwnerInterest where RECV_TOA_GRP_STATUS like '%880156GAS%')=0 and (select CO_NO from tblOwnerInterest where CO_NO='%1%')= 0
then (select CURRENT_PAYMENT_STATUS from tblOWNERINTEREST where CO_NO like '%1%' and RECV_TOA_GRP_STATUS like '%880156GAS%')

or can this logic be implemented using if-else?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 02:46:10

You can use if exists/(not exists)

if not exists(select * from tblOwnerInterest where RECV_TOA_GRP_STATUS like '%880156GAS%)
select CO_NO from tblOwnerInterest where CO_NO='%1%'
.
.
.

Madhivanan

Failing to plan is Planning to fail

Codesearcher
Starting Member

8 Posts

Posted - 2011-11-24 : 04:07:42

Hi Sir,
Can you please help me with the syntax for If NOT Exists?
as i also have few other columns to be selected along with this condition.
Eg:
Select col_1,col_2,
Case (select COUNT(*) from tblOwnerInterest where RECV_TOA_GRP_STATUS like '%880156GAS%')=0
then...
end as col3, col_4
from table1
where <<join conditions>>

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-24 : 06:16:32

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-30 : 10:10:42

Here we are an example of if not exist

IF EXISTS
    (
    SELECT 1
    FROM Timesheet_Hours
    WHERE Posted_Flag = 1
    AND Staff_Id = @PersonID
    )

    BEGIN
        RAISERROR('Timesheets have already been posted!', 16, 1)
        ROLLBACK TRAN
    END
ELSE
    IF NOT EXISTS
        (
        SELECT 1
        FROM Timesheet_Hours
        WHERE Staff_Id = @PersonID
        )

        BEGIN
            RAISERROR('Default list has not been loaded!', 16, 1)
            ROLLBACK TRAN
        END

http://stackoverflow.com/questions/1175217/sql-server-if-not-exists-usage

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources