replace union with case statement

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

General SQL Server Forums

New to SQL Server Programming

replace union with case statement

Author

Topic

dsscholar
Starting Member

6 Posts

Posted - 2011-09-11 : 01:03:34

Hi all,

I have this following query. This needs to be changed to a single query with case statement. Please advise.

Select t1.a,t2.b,t3.c as s
from t1,t2,t3 where t1.x = t2.x
and t2.y = t3.y
and t1.a <> 0

union

Select t1.a,t2.b,null as s
from t1,t2 where t1.x = t2.x
and t1.a = 0

In the second sql statement one join condition is not there and condition t1.a = 0 is there instead of t1.a <> 0. Is it posssible to replace this with case statement.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 11:32:24

its possible assuming the relationship b/w t2 and t3 is one to one. make it like


Select t1.a,t2.b,
case when t1.a <> 0 then t3.c else null end as s
from t1
inner join t2
on t1.x = t2.x
left join t3  
on t2.y = t3.y

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dsscholar
Starting Member

6 Posts

Posted - 2011-09-12 : 02:10:54

select BALANCES.*
, COUNTRY.code AS InternalCounterpatyCountryCode
, COUNTRY.name AS InternalCounterpatyCountry
, (Left(BALANCES.[BankAccountNumber],Len(BALANCES.[BankAccountNumber])-4)) AS InternalCounterparty
, CPARTY.ext_name AS InternalCounterpartyName
, Year(BALANCES.CashflowDate) AS [Year]
, Month(BALANCES.CashflowDate) AS [Month]
, TRANS.deal_dt AS DealDate
, TRANS.mature_dt AS MaturityDate
, TRANS.Comments
, ACCTS.BankExtName as InHouseBankName
, ACCTS.BankName as InHouseBankCode
from j0808_live_risk.dbo.jci_internal_balances BALANCES
, j0808_live_qtm.dbo.jci_bankaccounts ACCTS
, j0808_live_qtm.dbo.cparty CPARTY
, j0808_live_qtm.dbo.country COUNTRY
, j0808_live_qtm.dbo.jci_transactions TRANS
where balances.bankaccountnumber = accts.bankaccountnumber
and balances.dealnumber = trans.deal_no
and country.thekey = cparty.country
and (Left(BALANCES.[BankAccountNumber],Len(BALANCES.[BankAccountNumber])-4))=CPARTY.[name]
AND CPARTY.deal_int='Y'
AND BALANCES.DealNumber<>0
and balances.reportnumber in (select max(reportnumber) from j0808_live_risk.dbo.jci_internal_balances
group by year(cashflowdate), month(cashflowdate))

UNION

select BALANCES.*
, COUNTRY.code AS InternalCounterpatyCountryCode
, COUNTRY.name AS InternalCounterpatyCountry
, Left(BALANCES.BankAccountNumber,Len(BALANCES.BankAccountNumber)-4) AS InternalCounterparty
, CPARTY.ext_name AS InternalCounterpartyName
, Year(BALANCES.CashflowDate) AS [Year]
, Month(BALANCES.CashflowDate) AS [Month]
, null AS DealDate
, null AS MaturityDate
, null AS Comments
, ACCTS.BankExtName AS InHouseBankName
, ACCTS.BankName AS InHouseBankCode
from j0808_live_risk.dbo.jci_internal_balances BALANCES
, j0808_live_qtm.dbo.jci_bankaccounts ACCTS
, j0808_live_qtm.dbo.cparty CPARTY
, j0808_live_qtm.dbo.country COUNTRY
where balances.bankaccountnumber = accts.bankaccountnumber
and country.thekey = cparty.country
and (Left(BALANCES.[BankAccountNumber],Len(BALANCES.[BankAccountNumber])-4))=CPARTY.[name]
and cparty.deal_int = 'Y'
and balances.dealnumber = 0
and balances.reportnumber in (select max(reportnumber) from j0808_live_risk.dbo.jci_internal_balances
group by year(cashflowdate), month(cashflowdate))

This is the query. I want to make it as one statement with case statement or someother means if possible. In second query join between TRANS and BALANCWES is not there. please note it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 02:55:03

do as i suggested by replacing the extra join with left join and case expression in the select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dsscholar
Starting Member

6 Posts

Posted - 2011-09-12 : 14:55:54

Logic is wrong.Please check it once. It produce wrong results. I did exactly as u said.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-12 : 15:45:46

I'm not following your question. Perhaps this link will help you post you DDL, DML and expected output so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 00:28:19

quote:
Originally posted by dsscholar

Logic is wrong.Please check it once. It produce wrong results. I did exactly as u said.

Thanks.

do you want somone to check your logic and fix your code? we dont have much idea so i dont think anybody will take the pain to do that.
as i suggested try to rewrite query,test and fix your logic. if you're having issues in implemeting any particular scenario then post it and somebody will help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources