Home » SQL & PL/SQL » SQL & PL/SQL » CASE help
CASE help [message #241843] Wed, 30 May 2007 13:10 Go to next message
russki
Messages: 14
Registered: May 2007
Junior Member
I tried to write what i need (it's not working), maybe this will help to understand!!!! problem in case statment........

Thank-you


SELECT *

FROM (SELECT
b.en_ent,
e.sa_sub,
c.cc_cstctr,
d.acct_acc,

g.cust_name,

b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE,
SUM(a.gl_amt),

CASE WHEN gl_tr_type = 'SO'

THEN
a.gl_doc = h.inv_nbr AND h.inv_cust_bill_to_nbr = g.cust_nbr

END

CASE WHEN gl_tr_type = 'IC'

THEN
a.gl_doc = h.tr_trnbr AND h.tr_nbr = g.channel_code

END

FROM

F_ACCT_TRX_HIST_STG1 a,
D_ENTITY_STG2 b,
D_COSTCTR_STG2 c,
D_ACCTS_STG2 d,
D_SUBACCTS_STG2 e,
D_PERIOD_STG1 f,
FINMART.D_CUSTOMER g,
DSSMART.F_SALES_INVOICE h,


WHERE

a.GL_ENT = b.EN_ENT AND
c.CC_CSTCTR= UPPER (a.GL_CC) AND
d.acct_acc = a.gl_acc AND
e.sa_sub = a.gl_sa AND
a.gl_eff_dt = f.calendar_date

GROUP BY b.EN_ENT,

e.sa_sub,
c.cc_cstctr,
d.acct_acc,
b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE
g.cust_name)

WHERE g.cust_name IS NULL THEN INSERT 'N/A'

Report message to a moderator

Re: CASE help [message #241844 is a reply to message #241843] Wed, 30 May 2007 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There are many errors.

What do you mean with "THEN a.gl_doc = h.inv_nbr AND h.inv_cust_bill_to_nbr = g.cust_nbr" in SELECT list?

"WHERE g.cust_name IS NULL THEN INSERT 'N/A' " is not Oracle, what do you want to do?

Regards
Michel

Report message to a moderator

Re: CASE help [message #241846 is a reply to message #241844] Wed, 30 May 2007 13:27 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member
Hi Michel, and thank-you

"WHERE g.cust_name IS NULL THEN INSERT 'N/A' " is not Oracle, what do you want to do?

here i'm trying to insert 'N/A' everywhere where cust_name will be blank because it neither PO or IC......

What do you mean with "THEN a.gl_doc = h.inv_nbr AND h.inv_cust_bill_to_nbr = g.cust_nbr" in SELECT list?

here I'm trying to JOIN the tables but only if gl_tr_type is PO or IC....

Does this help?

Thank-you

Report message to a moderator

Re: CASE help [message #241873 is a reply to message #241843] Wed, 30 May 2007 15:11 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Tables are not joined in the SELECT clause.
Try moving the CASE logic to the WHERE clause. Like ...

WHERE ...
a.gl_eff_dt = f.calendar_date AND
a.gl_doc =
CASE WHEN gl_tr_type = 'SO'
AND h.inv_cust_bill_to_nbr = g.cust_nbr
THEN h.inv_nbr

WHEN gl_tr_type = 'IC'
AND h.tr_nbr = g.channel_code
THEN h.tr_trnbr
END

Report message to a moderator

Re: CASE help [message #241884 is a reply to message #241873] Wed, 30 May 2007 18:00 Go to previous messageGo to next message
russki
Messages: 14
Registered: May 2007
Junior Member

I'm trying to put in CASE and still not working , can you please see my case statment

SELECT
b.en_ent,
e.sa_sub,
c.cc_cstctr,
d.acct_acc,

b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
g. cust_name,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE,
SUM(a.gl_amt)

FROM
F_ACCT_TRX_HIST_STG1 a,
D_ENTITY_STG2 b,
D_COSTCTR_STG2 c,
D_ACCTS_STG2 d,
D_SUBACCTS_STG2 e,
D_PERIOD_STG1 f,
FINMART.D_CUSTOMER g,
DSSMART.F_SALES_INVOICE h

WHERE a.gl_eff_dt BETWEEN '02-april-2007' AND '09-april-2007' AND
a.GL_ENT = b.EN_ENT AND
c.CC_CSTCTR = UPPER (a.GL_CC) AND
d.acct_acc = a.gl_acc AND
e.sa_sub = a.gl_sa AND
a.gl_eff_dt = f.calendar_date AND
CASE WHEN a.gl_tr_type = 'SO' AND a.gl_doc =h.inv_nbr
AND h.inv_cust_bill_to_nbr = g.cust_nbr
THEN '"'||trim(g.cust_name) ||'"'
END


GROUP BY b.EN_ENT, e.sa_sub, c.cc_cstctr, d.acct_acc,

b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
g. cust_name,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE

Report message to a moderator

Re: CASE help [message #241887 is a reply to message #241884] Wed, 30 May 2007 18:53 Go to previous messageGo to next message
scottwmackey
Messages: 515
Registered: March 2005
Senior Member
If you put a CASE statement in the WHERE clause, it has to be compared to something.
AND CASE WHEN a.gl_tr_type = 'SO'
          AND a.gl_doc = h.inv_nbr
          AND h.inv_cust_bill_to_nbr = g.cust_nbr THEN
     '"' || TRIM(g.cust_name) || '"' END = xxxxx

Report message to a moderator

Re: CASE help [message #241892 is a reply to message #241843] Wed, 30 May 2007 19:18 Go to previous message
russki
Messages: 14
Registered: May 2007
Junior Member
How can i return all the records if they dont equal to SO???

thank-you!!!

SELECT
b.en_ent,
e.sa_sub,
c.cc_cstctr,
d.acct_acc,

b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
g. cust_name,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE,
SUM(a.gl_amt)

FROM
F_ACCT_TRX_HIST_STG1 a,
D_ENTITY_STG2 b,
D_COSTCTR_STG2 c,
D_ACCTS_STG2 d,
D_SUBACCTS_STG2 e,
D_PERIOD_STG1 f,
FINMART.D_CUSTOMER g,
DSSMART.F_SALES_INVOICE h

WHERE

a.gl_eff_dt BETWEEN '02-april-2007' AND '09-april-2007' AND
a.GL_ENT = b.EN_ENT AND
c.CC_CSTCTR = UPPER (a.GL_CC) AND
d.acct_acc = a.gl_acc AND
e.sa_sub = a.gl_sa AND
a.gl_eff_dt = f.calendar_date
AND
(a.gl_tr_type ='SO' AND
a.gl_doc =h.inv_nbr AND
h.inv_cust_bill_to_nbr = g.cust_nbr)

GROUP BY b.EN_ENT, e.sa_sub, c.cc_cstctr, d.acct_acc,

b. EN_ENTITY_LNG,
e. SA_SUB_LNG,
c. COST_CTR_LNG,
d. ACCT_ACC_LNG ,
g. cust_name,
f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE

[Updated on: Wed, 30 May 2007 19:20]

Report message to a moderator

Previous Topic: query giving error of spool space
Next Topic: procedure parameter
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 01 10:27:33 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.