SQL query help

Tom Dunne

It's scary how much SQL I have forgotten since I haven't been using it in so long. I'm struggling with the following scenario (Oracle).

I have a table that has a status field and an ID (among others). So I do a SELECT ID, STATUS from TABLE and I get data such as the following:

ID Status
123
124
125 5
126
127 9

If the status is null, I use a decode to return a value of Active. So I get

ID Status
123 Active
124 Active
125 5

Now here's the part I am struggling with.

If status returns a value (see above) I need to join these with another table to lookup the status code and return the status. So I need either the decode or the lookup returned, but obviously not both.

I would really appreciate any insights to this.

Peace

Probably wrong but here:

SELECT TABLE1.ID, TABLE1.STATUS, TABLE2.STAUS_DETAIL FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.STATUS = TABLE2.STATUS_DETAIL

You should look at INNER JOIN. Isn't there a query designer in ORACLE? THere is in SQL Server and i rustle up those sort of queries in seconds without even turning my brain on, it does the work for me.

Tom Dunne

Doesn't work, I'm afraid.

That excludes the rows where the status is Null (i.e. the one which I use the decode on, so I don't get any status of Active).

Oracle has loads of tools, but for the most part, you have to buy them. We don't have query designer here, unfortunately.


Edit: Got it working. Here's the code for anyone interested:

select t1.id, decode(t2.status,'','Active',t2.status)
from table1 t1, table2 t2
where t1.status=t2.status(+)

amen

gald you got it working.
You wanted to look at OUTER JOINS not Inner joins

what the (+) syntax? Looks like Oracle specific not ANSI standard

Tom Dunne

Yeah, the (+) in this case is an outer join.

More details from Oracle's documentation here.