[BPAWthe1st]

I'm trying to create a query where if the first field is null, it will go to another fields. I tried something like this:

SELECT table1.column1, table1.column2, table2.column1, table2.column2
CASE
WHEN table3.column1 IS NULL THEN table4.column1
ELSE table3.column1
END
FROM table1, table2, table3, table4

But it was not working. Is case not the right thing to use here or is my formatting off? Any help would be appreciated.

[June7]

What database app? Where is this statement - code, form, report, saved query? If code could select all the fields then use nested in-line if to set a variable? I have seen case structure in a query but never built one. However, you are missing JOIN or UNION clause for the tables.

If this is Access can use Nz funtion and maybe (test it in query designer):

SELECT Nz(table1.column1,Nz(table1.column2,Nz(table2.column1,Nz(table2.column2,table3.column1)))) As MyData FROM ... JOIN ... WHERE ... ORDER BY ...

This post has been edited by June7: 20 June 2009 - 10:35 AM

[Robin Marsh]

A SQL Server query could be as follows...

SELECT
	 tbl1.[field1],
	 tbl2.[field2],
	 Case When tbl1.[field1] Is Not Null Then tbl1.[field1] Else tbl2.[field1] End
FROM Table1 tbl1
LEFT JOIN Table2 tbl2 On tbl2.[id] = tbl1.[id]
WHERE tbl1.[id] > 0

[BPAWthe1st]

It is in a query and there is a WHERE clause but that doens't impact the selection. I tried to second post's code and it gave me the same error as when I tried it, saying "missing keyword." I'm using Oracle SQL Developer to eventually be placed in Oracle Reports Builder.