Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2012
    Posts
    6

    Unanswered: Oracle to DB2 SQL problem

    Hi,

    I am trying to get some SQL to work in DB/2 (9.5) that works in Oracle. Here is the SQL in question:

    SELECT t1.ID
    FROM GR_CUSTOMER t1 ORDER BY (SELECT COUNT(*)
    FROM BANK_ACCT s1t1, GR_CUSTOMER s1t2, CUSTOMER_ACCT_LINK s1t3
    WHERE (s1t2.ID = t1.ID) AND ((s1t3.CUSTOMER_ID = s1t2.ID) AND (s1t1.ID = s1t3.ACCT_ID)))

    DB/2 does not like the t1.ID table reference in the ORDER BY sub-select and gives me this error:

    SQL0206N "T1.ID" is not valid in the context where it is used.
    SQLSTATE=42703

    I have researched this quite a bit and have not been able to determine why this does not work in DB/2. Can anyone shed any light on why this does not work or suggest a way around it?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It's DB2 not DB/2

    How can you ORDER BY the COUNT(*) in a subselect? What are you trying to do? In DB2 ORDER BY comes after the WHERE clause in either the main-select or sub-select.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2012
    Posts
    6

    Oracle to DB2

    The SQL is designed to return a list of the ID's of all the entries in the GR_CUSTOMER table , but ordered by the number of bank accounts that are associated with each one. The main select statement does not have a WHERE clause since it is returning all rows, so the WHERE clause in the statement I presented is associated with the select count(*) sub-select.

  4. #4
    Join Date
    Apr 2012
    Posts
    5
    If ID is a column in GR_CUSTOMER then just use ID instead of t1.ID

    Try the below query(untested):


    SELECT ID
    FROM GR_CUSTOMER t1 ORDER BY
    (SELECT COUNT(*)
    FROM BANK_ACCT s1t1, GR_CUSTOMER s1t2, CUSTOMER_ACCT_LINK s1t3
    WHERE (s1t1.ID = s1t2.ID)
    AND ((s1t3.CUSTOMER_ID = s1t2.ID)
    AND (s1t1.ID = s1t3.ACCT_ID)));

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not tested:

    SELECT s1t1.ID, COUNT(*)
    FROM BANK_ACCT s1t1,
    GR_CUSTOMER s1t2,
    CUSTOMER_ACCT_LINK s1t3
    WHERE s1t2.ID = t1.ID
    AND s1t3.CUSTOMER_ID = s1t2.ID
    AND s1t1.ID = s1t3.ACCT_ID
    GROUP BY s1t1.ID
    ORDER BY 2
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try this(move a subquery in ORDER BY clause into FROM clause).
    (Not tested.)

    Note: I assumed that ID of GR_CUSTOMER is unique. So, I removed "GR_CUSTOMER s1t2".
    Code:
    SELECT t1.ID
     FROM  GR_CUSTOMER t1
         , LATERAL /* or TABLE */
          (SELECT COUNT(*) AS count_bank_acct
            FROM  BANK_ACCT          s1t1
                , CUSTOMER_ACCT_LINK s1t3
            WHERE s1t3.CUSTOMER_ID = t1  .ID
              AND s1t1.ID          = s1t3.ACCT_ID
          )
     ORDER BY
           count_bank_acct
    ;

  7. #7
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    It's DB2 not DB/2
    Used to be DB2/2
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dr_te_z View Post
    Used to be DB2/2
    OK, but I said it was not DB/2.

    My recollection is that it has been called:
    • OS/2 Datababase Manager (Part of OS/2 EE).
    • DB2/6000 or DB2/2 (for OS/2)
    • DB2 UDB
    • DB2 LUW


    But never DB/2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Test result.

    Note: Change alias names and the sequence of tables in subquery.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      GR_CUSTOMER(ID) AS (
    VALUES
      100 , 200 , 300
    )
    , CUSTOMER_ACCT_LINK(CUSTOMER_ID , ACCT_ID) AS (
    VALUES
      ( 100 , 101 )
    , ( 100 , 102 )
    , ( 100 , 103 )
    , ( 300 , 301 )
    , ( 300 , 302 )
    )
    , BANK_ACCT(ID) AS (
    VALUES
      101 , 102 , 103
    , 301 , 302
    )
    SELECT t1.ID
     FROM  GR_CUSTOMER t1
         , LATERAL /* or TABLE */
          (SELECT COUNT(*) AS count_bank_acct
            FROM  CUSTOMER_ACCT_LINK s1
                , BANK_ACCT          s2
            WHERE s1.CUSTOMER_ID = t1.ID
              AND s2.ID          = s1.ACCT_ID
          )
     ORDER BY
           count_bank_acct
    ;
    ------------------------------------------------------------------------------
    
    ID         
    -----------
            200
            300
            100
    
      3 record(s) selected.

  10. #10
    Join Date
    Apr 2012
    Posts
    6

    re:Oracle to DB2 problem

    Thanks for the suggestion. The SQL is runnable, but the results do not come out the correct order.


    Quote Originally Posted by delta403 View Post
    If ID is a column in GR_CUSTOMER then just use ID instead of t1.ID

    Try the below query(untested):


    SELECT ID
    FROM GR_CUSTOMER t1 ORDER BY
    (SELECT COUNT(*)
    FROM BANK_ACCT s1t1, GR_CUSTOMER s1t2, CUSTOMER_ACCT_LINK s1t3
    WHERE (s1t1.ID = s1t2.ID)
    AND ((s1t3.CUSTOMER_ID = s1t2.ID)
    AND (s1t1.ID = s1t3.ACCT_ID)));

  11. #11
    Join Date
    Apr 2012
    Posts
    5
    Can you please give me some input data and the expected result. Thanks.

  12. #12
    Join Date
    Apr 2012
    Posts
    6

    re:Oracle to DB2 SQL problem

    Quote Originally Posted by delta403 View Post
    Can you please give me some input data and the expected result. Thanks.
    tonkuma posted a set of table creation data in this thread that is a good representation of data, and presented a solution that has the desired results (thanks tonkuma) .. I just tested it out.

    So I have a possible work-around in hand, but I am still wondering about scope rules of table references in DB2. I have not found documentation that spells out the rules of usage. Part of my problem is that I am porting an object to relational database framework and making adjustments to it so that it will work on DB2. It currently runs on Oracle, PostgreSql, MySQL , to name a few. The framework generates all of the SQL for the user, so my hands are tied a bit on how far I can deviate from what works on other platforms. So while tonkuma'a solution is a good one, I'm note sure if I can safely tweak the framework enough to make it produce that code .

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... It currently runs on Oracle, PostgreSql, MySQL , to name a few. The framework generates all of the SQL for the user, ...
    It means simply that you are using(or "an object to relational database framework" generates) common to those platforms(intentionaly or unconsiously).

    Are you using "RIGHT OUTER JOIN"?
    Although you can rewrite it by using "LEFT OUTER JOIN", there is no reason to avoid it.

    Some other examples:
    (a) SELECT without FROM clause.
    It would work on Microsoft SQL Server, MySQL.
    It wouldn't work on Oracle, DB2, PostgreSQL.

    (b) Multiple rows constructor. VALUES (...), (...), ...
    It would work on Microsoft SQL Server 2008, DB2 for LUW.
    It wouldn't work on Microsoft SQL Server 2005, Oracle.

  14. #14
    Join Date
    Apr 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    It means simply that you are using(or "an object to relational database framework" generates) common to those platforms(intentionaly or unconsiously).

    Are you using "RIGHT OUTER JOIN"?
    Although you can rewrite it by using "LEFT OUTER JOIN", there is no reason to avoid it.

    Some other examples:
    (a) SELECT without FROM clause.
    It would work on Microsoft SQL Server, MySQL.
    It wouldn't work on Oracle, DB2, PostgreSQL.

    (b) Multiple rows constructor. VALUES (...), (...), ...
    It would work on Microsoft SQL Server 2008, DB2 for LUW.
    It wouldn't work on Microsoft SQL Server 2005, Oracle.
    The SQL generator code originally generated a statement with a couple of INNER JOINs, but the t1.ID reference is still a blocking issue in db2. This is the inner join code:

    SELECT t1.ID
    FROM GR_CUSTOMER t1 ORDER BY (SELECT COUNT(*)
    FROM ((BANK_ACCT s1t1 INNER JOIN db2admin.CUSTOMER_ACCT_LINK s1t3 ON (s1t1.ID = s1t3.ACCT_ID)) INNER JOIN GR_CUSTOMER s1t2 ON (s1t3.CUSTOMER_ID = s1t2.ID))
    WHERE (s1t2.ID = t1.ID))

    I really appreciate your help on this issue. At this point , I am probably going to add this 'feature' to the list of things that won't work in DB2 version.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wondered if you might not understand the restriction of DB2 SQL for sort-key-expression.

    subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
    sort-key-expression

    An expression that is not simply a column name or an unsigned integer constant.
    The query to which ordering is applied must be a subselect to use this form of sort-key.
    The sort-key-expression cannot include a correlated scalar fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845).
    Any column-name within a sort-key-expression must conform to the rules described under "Column names in sort keys" below.

    There are a number of special cases that further restrict the expressions that can be specified.
    •DISTINCT is specified in the SELECT clause of the subselect (SQLSTATE 42822).
    The sort-key-expression must match exactly with an expression in the select list of the subselect (scalar-fullselects are never matched).

    •The subselect is grouped (SQLSTATE 42803).
    The sort-key-expression can:
    ◦be an expression in the select list of the subselect,
    ◦include a grouping-expression from the GROUP BY clause of the subselect
    ◦include an aggregate function, constant or host variable.
    The sort-key-expression cannot include a correlated scalar fullselect (SQLSTATE 42703)
    is just conform to the error you receive.
    SQL0206N "T1.ID" is not valid in the context where it is used.
    SQLSTATE=42703
    (Note: fullselect in DB2 includes subselect.)


    There is no other way than removing the correlated scalar fullselect from ORDER BY clause(like my example),
    wheather you can accept the fact or not.

Posting Permissions

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