Professional Web Applications Themes

left join problem - IBM DB2

if you just need something unique in the result set, 1. combination (NJIDATA.GLPMSTR.ID, NJIDATA.GLPMTRN.ID) is unique 2. you could also use row_number()...

  1. #1

    Default Re: left join problem

    if you just need something unique in the result set,
    1. combination (NJIDATA.GLPMSTR.ID, NJIDATA.GLPMTRN.ID) is unique
    2. you could also use row_number()
    AK Guest

  2. #2

    Default Re: left join problem

    > ... NJIDATA.GLPMSTR.id from ...
    > i can't get the column id in this view be unique,
    >
    > if i do this way , i get column id from glpmtrn, i can't get all the
    > id column filled up, some of them are blank, but i need to have the id
    > column have value
    >
    > ... NJIDATA.GLPMTRN .id from ...
    >
    Use this expression instead of "NJIDATA.GLPMSTR.id" or
    "NJIDATA.GLPMTRN .id".
    CASE WHEN NJIDATA.GLPMTRN .id <> '' THEN NJIDATA.GLPMTRN .id ELSE
    NJIDATA.GLPMSTR.id END
    Tokunaga T. Guest

  3. #3

    Default Re: left join problem

    hello, yes i need the id column to be unique, how to do combination?
    can you give me the sample? how about row_number(), please advise.
    xixi Guest

  4. #4

    Default Re: left join problem

    hi, i have tried submit the query but doesn't work, is this the right
    way? thanks

    create view NJIDATA.GLJMSTR1 (GLAM01, GLAM02, GLBL01, systimestamp,
    loginname, id ) as select NJIDATA.GLPMSTR.GLAM01,
    NJIDATA.GLPMSTR.GLAM02, NJIDATA.GLPMSTR.GLBL01, NJIDATA.GLPMTRN
    ..systimestamp, NJIDATA.GLPMTRN .loginname, CASE WHEN NJIDATA.GLPMSTR
    ..id <> '' THEN NJIDATA.GLPMSTR .id ELSE NJIDATA.GLPMTRN .id END
    from
    NJIDATA.GLPMSTR left join NJIDATA.GLPMTRN on NJIDATA.GLPMSTR.GLNO01 =
    NJIDATA.GLPMTRN.GLNO01 and NJIDATA.GLPMSTR.GLNO03 =
    NJIDATA.GLPMTRN.GLNO03 and NJIDATA.GLPMSTR.GLNO04 =
    NJIDATA.GLPMTRN.GLNO04 and NJIDATA.GLPMSTR.GLNO06 =
    NJIDATA.GLPMTRN.GLNO06 and NJIDATA.GLPMSTR.GLNO02 =
    NJIDATA.GLPMTRN.GLNO02 and NJIDATA.GLPMSTR.GLNO07 =
    NJIDATA.GLPMTRN.GLNO07 and NJIDATA.GLPMSTR.GLNO27 =
    NJIDATA.GLPMTRN.GLNO27 and NJIDATA.GLPMSTR.GLNO08 =
    NJIDATA.GLPMTRN.GLNO08


    [email]tonkumajp.ibm.com[/email] (Tokunaga T.) wrote in message news:<8156d9ae.0308021724.62bf43deposting.google. com>...
    > > ... NJIDATA.GLPMSTR.id from ...
    > > i can't get the column id in this view be unique,
    > >
    > > if i do this way , i get column id from glpmtrn, i can't get all the
    > > id column filled up, some of them are blank, but i need to have the id
    > > column have value
    > >
    > > ... NJIDATA.GLPMTRN .id from ...
    > >
    > Use this expression instead of "NJIDATA.GLPMSTR.id" or
    > "NJIDATA.GLPMTRN .id".
    > CASE WHEN NJIDATA.GLPMTRN .id <> '' THEN NJIDATA.GLPMTRN .id ELSE
    > NJIDATA.GLPMSTR.id END
    xixi Guest

  5. #5

    Default Re: left join problem

    [email]dai_xi[/email] (xixi) wrote in message
    > hi, i have tried submit the query but doesn't work, is this the right
    What error did you got?
    Have you got error from CREATE VIEW statement?
    Or, the result of "SELECT * FROM NJIDATA.GLJMSTR1" was not as you expected?
    Tokunaga T. Guest

  6. #6

    Default Re: left join problem

    [email]tonkumajp.ibm.com[/email] (Tokunaga T.) wrote in message news:<8156d9ae.0308052103.57bced6dposting.google. com>...
    > [email]dai_xi[/email] (xixi) wrote in message
    > > hi, i have tried submit the query but doesn't work, is this the right
    > What error did you got?
    > Have you got error from CREATE VIEW statement?
    > Or, the result of "SELECT * FROM NJIDATA.GLJMSTR1" was not as you expected?
    Some hints.
    1. you have interchanged GLPMSTR and GLPMTRN.
    Yours:
    CASE WHEN NJIDATA.GLPMSTR. id <> '' THEN NJIDATA.GLPMSTR .id ELSE
    NJIDATA.GLPMTRN .id END

    Mine:
    CASE WHEN NJIDATA.GLPMTRN .id <> '' THEN NJIDATA.GLPMTRN .id ELSE
    NJIDATA.GLPMSTR.id END

    2. Are some of NJIDATA.GLPMTRN .id blank? Are they not NULL?
    If some of NJIDATA.GLPMTRN .id are NULL, you can use:
    COALESCE(NJIDATA.GLPMTRN .id, NJIDATA.GLPMSTR.id)
    Tokunaga T. Guest

  7. #7

    Default Re: left join problem

    HI,

    i got the error when i try to create the view, here is the view and
    exception

    create view NJIDATA.GLJMSTR1 (GLAM01, GLAM02, GLBL01, GLBL02, GLCC02,
    GLCC04, GLCD14, GLCD15, GLDN03, GLDY02, GLDY04, GLFL03, GLMO02,
    GLMO04, GLNM02, GLNO01, GLNO02, GLNO03, GLNO04, GLNO06, GLNO07,
    GLNO08, GLNO27, GLYR02, GLYR04, GLAM03, GLCC03, GLCD13, GLDN04,
    GLDY03, GLMO03, GLNO05, GLNO10, GLYR03, systimestamp, loginname, id )
    as select NJIDATA.GLPMSTR.GLAM01, NJIDATA.GLPMSTR.GLAM02,
    NJIDATA.GLPMSTR.GLBL01, NJIDATA.GLPMSTR.GLBL02,
    NJIDATA.GLPMSTR.GLCC02, NJIDATA.GLPMSTR.GLCC04,
    NJIDATA.GLPMSTR.GLCD14, NJIDATA.GLPMSTR.GLCD15,
    NJIDATA.GLPMSTR.GLDN03, NJIDATA.GLPMSTR.GLDY02,
    NJIDATA.GLPMSTR.GLDY04, NJIDATA.GLPMSTR.GLFL03,
    NJIDATA.GLPMSTR.GLMO02, NJIDATA.GLPMSTR.GLMO04,
    NJIDATA.GLPMSTR.GLNM02, NJIDATA.GLPMSTR.GLNO01,
    NJIDATA.GLPMSTR.GLNO02, NJIDATA.GLPMSTR.GLNO03,
    NJIDATA.GLPMSTR.GLNO04, NJIDATA.GLPMSTR.GLNO06,
    NJIDATA.GLPMSTR.GLNO07, NJIDATA.GLPMSTR.GLNO08,
    NJIDATA.GLPMSTR.GLNO27, NJIDATA.GLPMSTR.GLYR02,
    NJIDATA.GLPMSTR.GLYR04, NJIDATA.GLPMTRN.GLAM03,
    NJIDATA.GLPMTRN.GLCC03, NJIDATA.GLPMTRN.GLCD13,
    NJIDATA.GLPMTRN.GLDN04, NJIDATA.GLPMTRN.GLDY03,
    NJIDATA.GLPMTRN.GLMO03, NJIDATA.GLPMTRN.GLNO05,
    NJIDATA.GLPMTRN.GLNO10, NJIDATA.GLPMTRN.GLYR03,
    NJIDATA.GLPMTRN.systimestamp, NJIDATA.GLPMTRN.loginname, CASE WHEN
    NJIDATA.GLPMTRN .id <> '' THEN NJIDATA.GLPMTRN .id ELSE
    NJIDATA.GLPMSTR.id END
    from NJIDATA.GLPMSTR left join NJIDATA.GLPMTRN on
    NJIDATA.GLPMSTR.GLNO01 = NJIDATA.GLPMTRN.GLNO01 and
    NJIDATA.GLPMSTR.GLNO03 = NJIDATA.GLPMTRN.GLNO03 and
    NJIDATA.GLPMSTR.GLNO04 = NJIDATA.GLPMTRN.GLNO04 and
    NJIDATA.GLPMSTR.GLNO06 = NJIDATA.GLPMTRN.GLNO06 and
    NJIDATA.GLPMSTR.GLNO02 = NJIDATA.GLPMTRN.GLNO02 and
    NJIDATA.GLPMSTR.GLNO07 = NJIDATA.GLPMTRN.GLNO07 and
    NJIDATA.GLPMSTR.GLNO27 = NJIDATA.GLPMTRN.GLNO27 and
    NJIDATA.GLPMSTR.GLNO08 = NJIDATA.GLPMTRN.GLNO08

    SQLerror, SQLCODE:-401, SQLSTATE:42818,SQLERRMC:<>

    GLPMTRN AND GLPMSTR BOTH have ID COLUMN and IS NOT NULL, because IT IS
    IDENTITY COLUMN. THE REASON I HAVE GLPMSTR FIRST THEN GLPMTRN BECAUSE
    GLMPSTR IS THE MASTER TABLE LEFT JOIN THE OTHER TABLE, BUT EVEN I TRY
    THE WAY YOU GIVE , IT STILL NOT WORK, PLEASE HELP.
    xixi Guest

  8. #8

    Default Re: left join problem

    hello both of them are integer type, they are identity column which
    generate automatically when data insert.

    please help.
    xixi Guest

  9. #9

    Default Re: left join problem

    [email]dai_xi[/email] (xixi) wrote in message news:<c0f33a17.0308071121.4b63619cposting.google. com>...
    > hello both of them are integer type,
    So, they can't be blank! If it is not zero nor any number, it must be
    NULL.

    Although, NJIDATA.GLPMTRN.id and NJIDATA.GLPMSTR.id are both declared
    as NOT NULL.
    Selected NJIDATA.GLPMTRN.id in the view can be NULL, because you used
    outer join and GLPMTRN is an inner table.

    How about this?
    COALESCE(NJIDATA.GLPMTRN.id, NJIDATA.GLPMSTR.id)
    Tokunaga T. Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. left outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  3. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  4. left join limit 1
    By in forum MySQL
    Replies: 7
    Last Post: March 27th, 04:38 AM
  5. Left join isn't joining
    By Bill in forum MySQL
    Replies: 7
    Last Post: December 23rd, 04:47 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139