query help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • shorti
    #1

    query help

    I have another complicated query I could use help on. Here is what
    the tables look like:

    Table1

    col1 col2
    Name Main_number
    AA1 0
    AA2 1
    AA3 1
    AA4 2
    AA5 4
    AA6 4
    AA7 6


    Table2

    col1 col2
    Name Sec_name
    AA1 B00
    AA3 B01
    AA4 B00
    AA5 B02
    AA6 B02
    AA7 B04


    Table3

    col1 col2
    Name Pri_number
    B00 5
    B01 5
    B02 6
    B03 5
    B04 6
    B05 7

    So basically what I want to do is to update Table1.Main_num ber with
    Table2.Pri_numb er if Main_number is between the values of 1 and 4.

    I tried to do with in a SELECT and UPDATE statements but couldnt get
    the logic right. I first broke it down in pieces to get the logic
    right:

    1) find records that have a Main_number between 1 to 4
    select name, main_number from table1 where main_number between 1 and
    4

    2) locate the name in table2
    select name from table2 (where name equals name from table1)

    3) match the name to a sec_name
    select sec_name from table2 (where name matches from item 2 above)

    4) find the sec_name from table2 and get the pri_number
    select pri_number from table3 (where sec_name matches item 3 above)

    5) use it to update the the main_number
    update table1 set main_number = table2.pri_numb er


    So when I tried putting it together it looked like this:

    db2 "WITH
    s1(name, main_number) AS (SELECT name, main_number FROM table1
    WHERE main_number BETWEEN 1 AND 4),
    s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
    name FROM s1)),
    s3(sec_name) AS (SELECT sec_name FROM table2 WHERE name IN
    (SELECT name FROM s2)),
    s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
    sec_name IN (SELECT sec_name FROM s3)),
    u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
    main_number = (SELECT pri_number FROM s4)))
    SELECT results FROM u1"


    but it didnt work...received :

    SQL0407N Assignment of a NULL value to a NOT NULL column


    Any help would be appreciated. I am on DB2 UDB V8.2 if it matters.

    Thanks!

    Tags: None
    • mail2neeraj@gmail.com
      #2
      Re: query help

      On Oct 17, 10:44 am, shorti <lbrya...@juno. comwrote:
      I have another complicated query I could use help on.  Here is what
      the tables look like:
      >
      Table1
      >
      col1       col2
      Name    Main_number
      AA1       0
      AA2       1
      AA3       1
      AA4       2
      AA5       4
      AA6       4
      AA7       6
      >
      Table2
      >
      col1        col2
      Name     Sec_name
      AA1        B00
      AA3        B01
      AA4        B00
      AA5        B02
      AA6        B02
      AA7        B04
      >
      Table3
      >
      col1      col2
      Name   Pri_number
      B00      5
      B01      5
      B02      6
      B03      5
      B04      6
      B05      7
      >
      So basically what I want to do is to update Table1.Main_num ber with
      Table2.Pri_numb er if Main_number is between the values of 1 and 4.
      >
      I tried to do with in a SELECT and UPDATE statements but couldnt get
      the logic right.  I first broke it down in pieces to get the logic
      right:
      >
      1) find records that have a Main_number between 1 to 4
      select name, main_number from table1 where main_number between 1 and
      4
      >
      2) locate the name in table2
      select name from table2 (where name equals name from table1)
      >
      3) match the name to a sec_name
      select sec_name from table2 (where name matches from item 2 above)
      >
      4) find the sec_name from table2 and get the pri_number
      select pri_number from table3 (where sec_name matches item 3 above)
      >
      5) use it to update the the main_number
      update table1 set main_number = table2.pri_numb er
      >
      So when I tried putting it together it looked like this:
      >
      db2 "WITH
              s1(name, main_number) AS (SELECT name, main_number FROM table1
      WHERE main_number BETWEEN 1 AND 4),
              s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
      name FROM s1)),
              s3(sec_name) AS (SELECT sec_name FROM table2 WHERE name IN
      (SELECT name FROM s2)),
              s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
      sec_name IN (SELECT sec_name FROM s3)),
              u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
      main_number = (SELECT pri_number FROM s4)))
      SELECT results FROM u1"
      >
      but it didnt work...received :
      >
      SQL0407N  Assignment of a NULL value to a NOT NULL column
      >
      Any help would be appreciated.  I am on DB2 UDB V8.2 if it matters.
      >
      Thanks!
      try this...
      =============== ======
      MERGE INTO Table1 dest
      USING (
      select
      table2.name as name1,
      table3.name as name2,
      pri_number,
      sec_name
      from
      table2, table3
      where
      table2.sec_name = table3.name
      ) src
      ON (dest.name = src.name1)
      AND (dest.main_numb er between 1 and 4 )
      WHEN MATCHED THEN
      UPDATE SET
      dest.main_numbe r = src.pri_number
      ;
      =============== ======

        Comment

        • shorti
          #3
          Re: query help

          On Oct 17, 9:24 am, "mail2nee...@gm ail.com" <mail2nee...@gm ail.com>
          wrote:
          On Oct 17, 10:44 am, shorti <lbrya...@juno. comwrote:
          >
          >
          >
          >
          >
          I have another complicated query I could use help on.  Here is what
          the tables look like:
          >
          Table1
          >
          col1       col2
          Name    Main_number
          AA1       0
          AA2       1
          AA3       1
          AA4       2
          AA5       4
          AA6       4
          AA7       6
          >
          Table2
          >
          col1        col2
          Name     Sec_name
          AA1        B00
          AA3        B01
          AA4        B00
          AA5        B02
          AA6        B02
          AA7        B04
          >
          Table3
          >
          col1      col2
          Name   Pri_number
          B00      5
          B01      5
          B02      6
          B03      5
          B04      6
          B05      7
          >
          So basically what I want to do is to update Table1.Main_num ber with
          Table2.Pri_numb er if Main_number is between the values of 1 and 4.
          >
          I tried to do with in a SELECT and UPDATE statements but couldnt get
          the logic right.  I first broke it down in pieces to get the logic
          right:
          >
          1) find records that have a Main_number between 1 to 4
          select name, main_number from table1 where main_number between 1 and
          4
          >
          2) locate the name in table2
          select name from table2 (where name equals name from table1)
          >
          3) match the name to a sec_name
          select sec_name from table2 (where name matches from item 2 above)
          >
          4) find the sec_name from table2 and get the pri_number
          select pri_number from table3 (where sec_name matches item 3 above)
          >
          5) use it to update the the main_number
          update table1 set main_number = table2.pri_numb er
          >
          So when I tried putting it together it looked like this:
          >
          db2 "WITH
                  s1(name, main_number) AS (SELECT name, main_number FROMtable1
          WHERE main_number BETWEEN 1 AND 4),
                  s2(name) AS (SELECT name FROM table2 WHERE name IN (SELECT
          name FROM s1)),
                  s3(sec_name) AS (SELECT sec_name FROM table2 WHERE nameIN
          (SELECT name FROM s2)),
                  s4(pri_number) AS (SELECT pri_number FROM table3 WHERE
          sec_name IN (SELECT sec_name FROM s3)),
                  u1(results) AS (SELECT 1 FROM OLD TABLE (UPDATE table1 SET
          main_number = (SELECT pri_number FROM s4)))
          SELECT results FROM u1"
          >
          but it didnt work...received :
          >
          SQL0407N  Assignment of a NULL value to a NOT NULL column
          >
          Any help would be appreciated.  I am on DB2 UDB V8.2 if it matters.
          >
          Thanks!
          >
          try this...
          =============== ======
           MERGE INTO Table1 dest
                  USING (
                          select
                                  table2.name as name1,
                                  table3.name as name2,
                                  pri_number,
                                  sec_name
                          from
                                  table2, table3
                          where
                                  table2.sec_name = table3.name
                          ) src
                  ON  (dest.name = src.name1)
                  AND (dest.main_numb er between 1 and 4 )
                  WHEN MATCHED THEN
                          UPDATE SET
                                  dest.main_numbe r = src.pri_number
                  ;
          =============== ======- Hide quoted text -
          >
          - Show quoted text -
          Thanks so much ...this worked perfectly.

            Comment

            • Tonkuma
              #4
              Re: query help

              select
              table2.name as name1,
              table3.name as name2,
              pri_number,
              sec_name
              from
              table2, table3
              where
              table2.sec_name = table3.name
              I thought that "table3.nam e as name2" and "sec_name" were not
              neccesary.

                Comment

                • Tonkuma
                  #5
                  Re: query help

                  select
                  table2.name as name1,
                  table3.name as name2,
                  pri_number,
                  sec_name
                  from
                  table2, table3
                  where
                  table2.sec_name = table3.name
                  I thought that "table3.nam e as name2" and "sec_name" in the select
                  list were not necessary.



                    Comment

                    Previous template Next
                    Working...