Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9

    Unanswered: SQL 'Order By' Problem

    Hi All,
    I have a very simple version of the query that I am running and the 'Order By' part does not seem to work. The query does return all the rows that I need. For the 'Order By' tried doing an Ascend and Descend and it has no effect. Here is the query...

    SELECT
    p.c1, p.c2,
    (seLECT ifnull(b8, 0) from lib2.file2
    WHERE b1 = p.c1 and b2 = p.c2 ) as c3
    FROM
    Lib1.file1 as p
    WHERE
    < have some conditions>
    order by c3

    where c1 and c2 are columns in Lib1.file1 table
    b1, b2 and b8 are columns in lib2.file2 table
    c3 is the column name for the sub query that returns one
    row and one column

    Also. tried using the column number for the 'Order By' and then I get a syntax error.

    If you might know what the error is, please let me know.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I have a few questions:

    1) what is IFNULL function? It is not one of DB2 built in functions. Do you mean NULLIF? What is you intent with this function?

    2) What datatype is column B8 in table lib2.file2?

    3) Can you give examples of what is not working?

    Andy

  3. #3
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    Hi Andy,
    I have tried to answer your questions...

    OK...I am running this query on DB2 for the AS400.

    1. If column b8 should be null then I want the query to return a null.
    Here is an example from the reference guide for IFNULL
    When selecting the employee number (EMPNO) and salary (SALARY)
    from all the rows in the EMPLOYEE table, if the salary is missing (that
    is, null), then return a value of zero.
    SELECT EMPNO, IFNULL(SALARY,0)
    FROM EMPLOYEE

    2. It is a DECIMAL 7 2

    3. What is happening is the the final result table is not getting sorted by
    the column name c3. The query is returning all the rows that I need.
    I tried using the column number and I get a syntax error. All I need
    is for the order by to work so the rows get sorted by column c3.

    If you need more info, please let me know.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    The line in my previous post
    1. If column b8 should be null then I want the query to return a null.
    should read as...
    1. If column b8 should be null then I want the query to return a zero.

    Sorry!

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you give examples (~25 rows--enough to demonstrate the problem well) of what you are getting?

    Andy

  6. #6
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    Here are some sample rows....
    C1 C2 C3
    xxTL 50306 54.00
    xxTL 50307 54.00
    xxTL 50308 54.00
    xxTL 50309 54.00
    xxTL 56753 51.81
    xxTL 56754 51.81
    xxTL 58766 45.92
    xxTL 58767 45.92
    xxTL 58768 51.81
    xxTL 58769 51.81
    xxTL 76311 56.16
    xxTL 76312 56.16
    xxTL 76313 56.16
    xxTL 76314 56.16
    xxTL 76315 56.16
    xxTL 76316 56.16

    If you see above the column c3 is not getting sorted(Order By c3)

    Hope this helps..

    Thanks.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What version of DB2 are you using?

    Can you post the entire SQL that returned that result set?

    Andy

  8. #8
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    Hi Andy,
    The version is DB2 UDB for ISeries/AS400 v5.1.

    Here is the query with fake field and table names.

    SELECT
    t1xyz as c1, t1abc as c2,
    (seLECT ifnull(t2def, 0) from lib2.file2
    WHERE t2xyz = p.t1xyz and t2abc = p.t1abc )
    as c3
    FROM
    lib1.file1 as p
    WHERE
    (t1xyz = 'xxTL') and
    (t1ghi = 'ACTIVE' )
    order by c3

    Thanks for taking the time to help out.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not know. The version of DB2 is quite old, and I do not remember it much.

    What happend if you try:

    with temp1 as (
    SELECT
    t1xyz as c1, t1abc as c2,
    (seLECT ifnull(t2def, 0) from lib2.file2
    WHERE t2xyz = p.t1xyz and t2abc = p.t1abc )
    as c3
    FROM
    lib1.file1 as p
    WHERE
    (t1xyz = 'xxTL') and
    (t1ghi = 'ACTIVE' )
    ) select * from temp1
    order by c3


    You should also be able to write the query as a join:
    (I do not know if this is supported in V 5.1 od UDB.)

    SELECT
    p.t1xyz as c1, p.t1abc as c2,
    ifnull(q.t2def, 0) as c3
    FROM
    lib1.file1 as p, lib2 left outer join file2 as q on ( q.t2xyz = p.t1xyz and q.t2abc = p.t1abc )
    WHERE
    (t1xyz = 'xxTL') and
    (t1ghi = 'ACTIVE' )
    order by c3


    Andy

  10. #10
    Join Date
    Feb 2004
    Location
    Pennsylvania
    Posts
    9
    Andy,
    I really appreciate, for taking the time to help out...

    I tried the your first option and I am getting an error... It does not like the select for the column3... The second option works but actually there is one other column similar to c3 that I need to pull with a similar query as in c3 but with different where conditions. When I place these 2 conditions in the join then I get duplicate records.

    The error for the first option is...

    Message ID . . . . . . : SQL0104 Severity . . . . . . . : 30
    Message type . . . . . : Diagnostic

    Message . . . . : Token SELECT was not valid. Valid tokens: <IDENTIFIER> <INTEGER> <CHARSTRING> <GRAPHSTRING>.
    Cause . . . . . : A syntax error was detected at token SELECT. Token SELECT
    is not a valid token. A partial list of valid tokens is <IDENTIFIER>
    <INTEGER> <CHARSTRING> <GRAPHSTRING>. This list assumes that the statement
    is correct up to the token. The error may be earlier in the statement, but
    the syntax of the statement appears to be valid up to this point.
    Recovery . . . : Do one or more of the following and try the request again:
    -- Verify the SQL statement in the area of the token SELECT. Correct the
    statement. The error could be a missing comma or quotation mark, it could
    be a misspelled word, or it could be related to the order of clauses.
    -- If the error token is <END-OF-STATEMENT>, correct the SQL statement
    because it does not end with a valid clause.
    Bottom

    Thanks again for your help.

Posting Permissions

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