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.

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

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.

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!

Can you give examples (~25 rows--enough to demonstrate the problem well) of what you are getting?

Andy

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.

What version of DB2 are you using?

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

Andy

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.

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

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.