SQL 'Order By' problem

vinooma · #1 (**permalink**) 02-17-04, 16:06

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.

r123456 · #2 (**permalink**) 02-17-04, 21:25

Code:

TableA

      VAR1   VAR2
---------- ----------
         1          2
         1          3

TableB

      VAR1    VAR2   VAR3
---------- ---------- ----------
         1          2        NULL
         1          3          1

select a.var1, a.var2, (select NVL(var3, 0) from tableB where var1 = a.var1 AND var2 = a.var2) As "NEW"
from tableA a
ORDER BY NEW DESC;

ORDER BY "NEW" and ORDER BY 3 are also valid ORDER BY clauses.

This solution has been tested on Oracle 9i and SQL Server 2000. To execute the query in SQL Server 2000, replace NVL with ISNULL.

vinooma · #3 (**permalink**) 02-18-04, 08:59

I am running this query on DB2 and the syntax that I am using is all working fine, it is just that the 'Order By' does not seem to work.

Anyway thanks for taking the time to help out.

Internet Services

Web Development

Digital Marketing

Consumer Tech