Hi,

My first query gives me REF_ID, ATRG_NAM, NOTC_VWBL_CDE columns

SELECT REF_ID, ATRB_NAM, NOTC_VWBL_CDE from TNR256_TMPLT_ITM
WHERE ACTN_TYP_GRP_ID=1005 AND TMPLT_ID = 0 AND TMPLT_SCTN_ID = 0;

My second query gives me SCRN_LBL_TXT column from the same table as given below

SELECT SCRN_LBL_TXT from TNR256_TMPLT_ITM
WHERE ACTN_TYP_GRP_ID=6000 AND TMPLT_ID = 0 AND TMPLT_SCTN_ID = 0;

I would like to see both the results in one query .
I need REF_ID, ATRB_NAM, NOTC_VWBL_CDE when ACTN_TYP_GRP_ID=1005
for the above results i want the corresponding SCRN_LBL_TXT when ACTN_TYP_GRP_ID=6000
If any of the REF_ID does not have corresponding SCRN_LBL_TXT, it sould be blank.

Please suggest.

Thanks

I need a little more information. How does the first set of data relate to the second set of data e.g. what are the join keys?

Also a logical description of what you are trying to do would be helpful.

FunkyMonkey

This answer is based on what I think you're asking for which is the result of the first query joined to the possible existence of a match to the second query based on a join key of Ref_id:

Select
t0.ref_id, t0.atrb_namm, t0.notc_vwbl_cde, coalesce(scrn_lbl_txt, ' ') as srcn_lbl_txt
from
tnr256_tmplt_itm t0 left outer join
tnr256_tmplt_itm t1
on t0.ref_id = t1.ref_id .... add "and t0.x = t1.x" for other join keys
where
t0.actn_typ_grp_id = 1005 and
t0.tmplt_id = 0 and
t0.tmplt_sctn_id and
t1.actn_typ_grp_id = 6000

sorry I've given you an ambiguous reference to scrn_lbl_txt, it should read

coalesce(t1.scrn_lbl_txt, ' ') as srcn_lbl_txt

Hi,

I tried the following query, which has given me zero result. It should give me 57 rows. Your are correct ref_id is the join key (i.e. primary key).

Select
t0.ref_id, t0.atrb_nam, t0.notc_vwbl_cde, coalesce(t1.scrn_lbl_txt, ' ') as srcn_lbl_txt
from
tnr256_tmplt_itm t0 left outer join
tnr256_tmplt_itm t1
on t0.ref_id = t1.ref_id
where
t0.actn_typ_grp_id = 1005 and
t0.tmplt_id = 0 and
t0.tmplt_sctn_id =0 and
t1.actn_typ_grp_id = 6000

Thanks

Can you show me the example data and possibly the table layout?
Try the following amendment...

Select
t0.ref_id, t0.atrb_nam, t0.notc_vwbl_cde, coalesce(t1.scrn_lbl_txt, ' ') as srcn_lbl_txt
from
tnr256_tmplt_itm t0 left outer join
tnr256_tmplt_itm t1
on t0.ref_id = t1.ref_id and
t0.actn_typ_grp_id = 1005 and
t1.actn_typ_grp_id = 6000

where
t0.tmplt_id = 0 and
t0.tmplt_sctn_id =0

This query have given me all the rows those are in the table no matter whether the row belongs to ACTN_TYP_GRP_ID is 1005 or 6000.
For all of the rows it has SRCN_LBL_TXT as blank.

TNR256_TMPLT_ITM table
===============
REF_ID PK
ATRB_NAM
NOTC_VWBL_CDE
SCRN_LBL_TXT
TMPLT_ID
TMPLT_SCTN_ID
ACTN_TYP_GRP_ID
... e.t.c

do you need any other info?

Thanks

I think you need to give me some example data so I can build the table.

The query can only return rows that have (actn_typ_grp_id = 1005) or (actn_typ_grp_id = 1005 and another row with actn_typ_grp_id = 6000 for the same ref_id). It is impossible for it to return anything else and this is specified in the join.

Apologies, there was a line missing off the end of the where clause:

AND T0.ACTN_TYP_GRP_ID = 1005


That should give you the correct result.

Here is the some sample data with ACTN_TYP_GRP_ID 1005 as well as 6000. I think it will help you.

REF_ID ACTN_TYP_GRP_ID ACTN_GRP_MOD_CDE TMPLT_ID TMPLT_SCTN_ID TMPLT_SCTN_USE_NUM ATRB_NAM SCRN_LBL_TXT EDIT_RULE_NUM DSPLY_RULE_NUM FRMT_RULE_NUM VLDTN_RUL_NUM NOTC_VWBL_CDE
6000A000030000004000453 6000 A 30 4 453 UPDT_USR_ID Update ID 0 0 0 0 0
6000A000030000004000452 6000 A 30 4 452 UPDT_TSP Update Tsp 0 0 9 9 0
1005A000000000000000101 1005 A 0 0 101 EX_DTE EX-D 0 200 0 0 2
1005A000000000000000102 1005 A 0 0 102 EFF_DTE EFF-D 0 200 0 0 1

I guess we need to make a join on ATRB_NAM instead of REF_ID
I tried it, but it did not work.
I think you can help me out.

I think that my idea of what your data requirements are is incorrect.
Can you explain in english what your data is and how the results from query 1 relate to the resuls from query 2?

As i described in my very first post, it is simple.

I need ATRB_NAB, NOTC_VWBL_CDE & SCRN_LBL_TXT from TNR256 table when TMPLT_ID = 0, TMPLT_SCTN_ID = 0 and ACTN_TYP_GRP_ID = 1005 but i wanted to get SCRN_LBL_TXT only when ACTN_TYP_GRP_ID = 6000 for the respective ATRB_NAB and NOTC_VWBL_CDE.

I hope you understood my requirement.

Thanks

From the following text "If any of the REF_ID does not have corresponding SCRN_LBL_TXT, it sould be blank." I assumed you meant that REF_ID was the only corresponding key.

To fix this, add the other keys to the inner join statement;

.... and t0.ATRG_NAM = t1.ATRG_NAM and t0.NOTC_VWBL_CDE = t0.NOTC_VWBL_CDE

This will give you the result you want.

Thanks Funky
The following query is giving me the result i want

SELECT DISTINCT t0.atrb_nam, t0.notc_vwbl_cde, coalesce(t1.scrn_lbl_txt, ' ') as srcn_lbl_txt
FROM
tnr256_tmplt_itm t0 left outer join
tnr256_tmplt_itm t1
ON
(t0.atrb_nam = t1.atrb_nam AND
t1.actn_typ_grp_id = 6000)
WHERE
t0.tmplt_id = 0 and
t0.tmplt_sctn_id =0 and
t0.actn_typ_grp_id = 1005

I appreciate your support.

Thanks