Help with a Join !!!!!

[Stevennn]

Hello,

Here I'm doing two JOIN queries.

I need help with inserting 2nd query into 1st.

in the final result I would like to see all the transaction that are 'PP' w customer_name and the rest of the transactions that are not 'PP' i would like to see them as 'N/A' for customer_name


Thank-you for your HELP!

1ST QUERY

SELECT

b.pl_ent,
e.pl_sa,
c.pl_cc,
d.pl_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,
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

WHERE
a.PP_ENT = b.EN_ENT AND
c.CC_CSTCTR= UPPER (a.PP_CC) AND
d.acct_acc = a.pl_acc AND
e.sa_sub = a.pl_sa AND
a.pl_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



2ND QUERY


SELECT a.pl_ent,
a.pl_sa,
a.pl_cc,
a.pl_acc,
b.customer_name,
SUM(a.gl_amt)

FROM F_ACCT_TRX_HIST_STG1 a,
D_CUSTOMER b,
f_sales_invoice c

WHERE a.pl_tr_type = 'PP' AND
a.pl_doc= c.inv_nbr AND
c.inv_cust_bill_to_nbr = b.cust_nbr

GROUP BY a.pl_ent,
a.pl_sa,
a.pl_cc,
a.pl_acc,
b.customer_name

 

[blom0344]

What do you mean by inserting 2nd query into 1st?

Are you attempting to get one resultset? Then you should create a union construction with constants for the fields that occur in the first query and not in the 2nd. (And one constant vice-versa for customer_name that is in the 2nd and not in the 1st)

Ties Blom

 

[Stevennn]

Yes one result…………..2nd query is done separately because it is only for PP transactions. I did put them together but then I only get PP transactions ………. if it is not a PP transaction I would like to get a ‘N/A’ in customer_name and if it is a PP transaction then it will get populated from d_customer!!!!

Does this help???

 

[Stevennn]

I tried to write what i need CASE statment (it's not working), maybe this will help to understand more clearly!!!!

My problem is how Im writting this 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'

 

[blom0344]

SELECT
b.pl_ent,
e.pl_sa,
c.pl_cc,
d.pl_acc,
'N/A' as customername
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)

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

WHERE
a.PP_ENT = b.EN_ENT AND
c.CC_CSTCTR= UPPER (a.PP_CC) AND
d.acct_acc = a.pl_acc AND
e.sa_sub = a.pl_sa AND
a.pl_eff_dt = f.calendar_date AND
a.pl_tr_type <> 'PP' AND a.pl_tr_type IS NOT NULL


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
union

SELECT a.pl_ent,
a.pl_sa,
a.pl_cc, 
a.pl_acc, 
b.customer_name,
null,
null,
null,
null ,
null,
null,
null,
SUM(a.gl_amt)

FROM F_ACCT_TRX_HIST_STG1 a, 
D_CUSTOMER b, 
f_sales_invoice c

WHERE a.pl_tr_type = 'PP' AND
a.pl_doc= c.inv_nbr AND
c.inv_cust_bill_to_nbr = b.cust_nbr

GROUP BY a.pl_ent,
a.pl_sa,
a.pl_cc, 
a.pl_acc, 
b.customer_name

Ties Blom

 

[Stevennn]

Thank-you

My last post where i try to use CASE statment.....

there is no way to make that work, i only can use UNION?

 

[Stevennn]

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

 

[blom0344]

If you want to use a CASE to manipulate the join strategy, you need to use it in the where clause, otherwise every CASE construct will be the equivalent of an object in the select.

How can i return all the records if they dont equal to SO???

Using an expression like 'a.gl_tr_type <>'SO' '
will not be enough if a.gl_tr_type is nullable.
Then you need to add:

a.gl_tr_type <>'SO' or a.gl_tr_type is null

Ties Blom

 

[Stevennn]

Hello and thanks for the help,

Do you thik subquery will help???
I'm trying to write a subquery in my WHERE clause.
the way i wanted to work is. If gl_tr_type = 'SO' then execute the rest. But then i need g.cust_name to be in the group by between d. ACCT_ACC_LNG , f.FISCAL_MONTH,?

Thank-you for your Help!!!

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 ,

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

WHERE a.gl_eff_dt BETWEEN '02-april-2007' AND '02-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

(SELECT g. cust_name


FROM FINMART.D_CUSTOMER g,
DSSMART.F_SALES_INVOICE h

WHERE 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 ,

f. FISCAL_MONTH,
f. FISCAL_YEAR_LNG,
d. ACCT_TYPE

 

[blom0344]

Let's get back to basics. What you need to repeat in the group by is the list of objects in the select clause with the exception of the objects that are aggregated.
What you define in the where clause is not of importance in that respect.

The way you try to build a subquery is pretty senseless, cause there is no correlation with the main query..



Ties Blom