3 Replies - 687 Views - Last Post: 22 June 2009 - 10:38 AM Rate Topic: -----

#1 BPAWthe1st  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-June 09

Can I use CASE to determine which table columns to select?

Posted 19 June 2009 - 12:43 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Can I use CASE to determine which table columns to select?

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Can I use CASE to determine which table columns to select?

Posted 19 June 2009 - 03:43 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 Robin Marsh  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 07-May 09

Re: Can I use CASE to determine which table columns to select?

Posted 20 June 2009 - 06:07 AM

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

Was This Post Helpful? 0
  • +
  • -

#4 BPAWthe1st  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 19-June 09

Re: Can I use CASE to determine which table columns to select?

Posted 22 June 2009 - 10:38 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1