Union Query

G

Guest

Hi:

Can you please tell me how can I avoid the duplicate rows in the below qry.;
the second SELECT gives me on row with all the fields and one with clsl1 and
clsl2 balnk??

Thanks a lot,

Dan

SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] left JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].org_unit = AttMap.org)


UNION SELECT distinct [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].prod = AttMap.product)

where clsl1 is not null and clsl2 is not null


UNION SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].seq = AttMap.family) AND
([qrpt_GL-to-Generated_Matching_v6].roll_up = AttMap.[roll up])

where clsl2 is not null and clsl1 is not null
 
G

Guest

Null is not the same as blank. A text field may contain a zero lenght string
and will apear blank but is not null.
Try it this way --
where clsl2 is not null and clsl2 <>"" and clsl1 is not null and clsl1 <>""

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

Can you please tell me how can I avoid the duplicate rows in the below qry.;
the second SELECT gives me on row with all the fields and one with clsl1 and
clsl2 balnk??

Thanks a lot,

Dan

SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] left JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].org_unit = AttMap.org)


UNION SELECT distinct [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].prod = AttMap.product)

where clsl1 is not null and clsl2 is not null


UNION SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].seq = AttMap.family) AND
([qrpt_GL-to-Generated_Matching_v6].roll_up = AttMap.[roll up])

where clsl2 is not null and clsl1 is not null
Click to expand...
 
G

Guest

Thanks a lot Karl!

I will try Monday and let you kno!

Dan

KARL DEWEY said:
Null is not the same as blank. A text field may contain a zero lenght string
and will apear blank but is not null.
Try it this way --
where clsl2 is not null and clsl2 <>"" and clsl1 is not null and clsl1 <>""

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

Can you please tell me how can I avoid the duplicate rows in the below qry.;
the second SELECT gives me on row with all the fields and one with clsl1 and
clsl2 balnk??

Thanks a lot,

Dan

SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] left JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].org_unit = AttMap.org)


UNION SELECT distinct [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].prod = AttMap.product)

where clsl1 is not null and clsl2 is not null


UNION SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].seq = AttMap.family) AND
([qrpt_GL-to-Generated_Matching_v6].roll_up = AttMap.[roll up])

where clsl2 is not null and clsl1 is not null
Click to expand...
Click to expand...
 
G

Guest

Hi Karl:

It is not working! I am going nuts... any other way, please?

Thanks,

Dan

KARL DEWEY said:
Null is not the same as blank. A text field may contain a zero lenght string
and will apear blank but is not null.
Try it this way --
where clsl2 is not null and clsl2 <>"" and clsl1 is not null and clsl1 <>""

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

Can you please tell me how can I avoid the duplicate rows in the below qry.;
the second SELECT gives me on row with all the fields and one with clsl1 and
clsl2 balnk??

Thanks a lot,

Dan

SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] left JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].org_unit = AttMap.org)


UNION SELECT distinct [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].account = AttMap.account) AND
([qrpt_GL-to-Generated_Matching_v6].prod = AttMap.product)

where clsl1 is not null and clsl2 is not null


UNION SELECT [qrpt_GL-to-Generated_Matching_v6].org_group,
[qrpt_GL-to-Generated_Matching_v6].org_unit,
[qrpt_GL-to-Generated_Matching_v6].roll_up,
[qrpt_GL-to-Generated_Matching_v6].PnL_Officer,
[qrpt_GL-to-Generated_Matching_v6].prod,
[qrpt_GL-to-Generated_Matching_v6].PL,
[qrpt_GL-to-Generated_Matching_v6].seq,
[qrpt_GL-to-Generated_Matching_v6].ref,
[qrpt_GL-to-Generated_Matching_v6].AG8,
[qrpt_GL-to-Generated_Matching_v6].account,
[qrpt_GL-to-Generated_Matching_v6].sub,
[qrpt_GL-to-Generated_Matching_v6].cust,
[qrpt_GL-to-Generated_Matching_v6].ccy,
[qrpt_GL-to-Generated_Matching_v6].ME_FX_rate,
[qrpt_GL-to-Generated_Matching_v6].GL_CEQ_b4_adj,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_CEQ,
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_GL_CEQ],
[qrpt_GL-to-Generated_Matching_v6].[Adj'd_OPTEX_CEQ],
[qrpt_GL-to-Generated_Matching_v6].Difference_CEQ,
[qrpt_GL-to-Generated_Matching_v6].GL_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].to_adj_GL_orig,
[qrpt_GL-to-Generated_Matching_v6].OPTEX_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].[to_Adj_OPTEX-orig],
[qrpt_GL-to-Generated_Matching_v6].Attestation_Sch_M_adj,
[qrpt_GL-to-Generated_Matching_v6].Difference_orig_ccy,
[qrpt_GL-to-Generated_Matching_v6].GL_MTD,
[qrpt_GL-to-Generated_Matching_v6].GL_YTD,
[qrpt_GL-to-Generated_Matching_v6].AG7,
[qrpt_GL-to-Generated_Matching_v6].AG1,
[qrpt_GL-to-Generated_Matching_v6].AG2_Ac_Type,
[qrpt_GL-to-Generated_Matching_v6].commentary,
[qrpt_GL-to-Generated_Matching_v6].support_doc, AttMap.ClsL1, AttMap.ClsL2
FROM [qrpt_GL-to-Generated_Matching_v6] inner JOIN AttMap ON
([qrpt_GL-to-Generated_Matching_v6].seq = AttMap.family) AND
([qrpt_GL-to-Generated_Matching_v6].roll_up = AttMap.[roll up])

where clsl2 is not null and clsl1 is not null
Click to expand...
Click to expand...
 
Top