Results 1 to 14 of 14

Thread: query help

  1. #1
    Join Date
    Mar 2004
    Posts
    54

    Unanswered: query help

    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

  2. #2
    Join Date
    Dec 2004
    Posts
    11
    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

  3. #3
    Join Date
    Dec 2004
    Posts
    11

    Smile

    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

  4. #4
    Join Date
    Dec 2004
    Posts
    11
    sorry I've given you an ambiguous reference to scrn_lbl_txt, it should read

    coalesce(t1.scrn_lbl_txt, ' ') as srcn_lbl_txt

  5. #5
    Join Date
    Mar 2004
    Posts
    54

    Angry

    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

  6. #6
    Join Date
    Dec 2004
    Posts
    11
    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

  7. #7
    Join Date
    Mar 2004
    Posts
    54
    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

  8. #8
    Join Date
    Dec 2004
    Posts
    11
    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.

  9. #9
    Join Date
    Dec 2004
    Posts
    11
    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.

  10. #10
    Join Date
    Mar 2004
    Posts
    54
    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.
    Last edited by manth; 12-23-04 at 11:21.

  11. #11
    Join Date
    Dec 2004
    Posts
    11
    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?

  12. #12
    Join Date
    Mar 2004
    Posts
    54
    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

  13. #13
    Join Date
    Dec 2004
    Posts
    11
    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.

  14. #14
    Join Date
    Mar 2004
    Posts
    54

    Thumbs up

    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
    Last edited by manth; 12-29-04 at 11:12.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •