Help with case/decode statement

can anyone please help me, I am making a sql statement compatible with Oracle 8i and SQL Server.

In the SQL statement I have a CASE and a LEFT JOIN. I am relatively new to Oracle so am having a bit of trouble in tring to convert it.

the following code is below:



As I said I am relatively new to Oracle so any help will be greatly appreciated.

Tom

Your query is big enough and table stucture is not provided that's why it is hard for anyone to reply you back as quickly as much. however what i found in your query that there is some problem in using case...when condition and left join, i dont know either it is working or not, now take a look:

select CASE LO_SEQ
when LLL_PARENT_FKEY_LO_SEQ then LLL_CHILD_FKEY_LO_SEQ
when LLL_CHILD_FKEY_LO_SEQ then LLL_PARENT_FKEY_LO_SEQ else LO_SEQ end END as LO_SEQ,
LO_ROOM_NUMBER, BG_SITE, LO_DESCRIPTION, LO_SEATING_STD, BK_REF,
BKD_SEQ,BKD_ADATE_START,BKD_ADATE_ENDS,BKD_FKEY_BK_SEQ,BKD_FKEY_LO_SEQ
from FLOCATE, F_BOOK_DATES,F_BOOK_HEADER,FAREALO,
LEFT JOIN F_LNK_LO_LO -- i assume it is a table name
ON (LLL_PARENT_FKEY_LO_SEQ = LO_SEQ or LLL_CHILD_FKEY_LO_SEQ = LO_SEQ or LLL_PARENT_FKEY_LO_SEQ = 0)
and BKD_ADATE_START >=to_date('20040824 00:00:00','YYYYMMDD HH24:MI:SS')
and BKD_ADATE_START < to_date('20040823 00:00:00','YYYYMMDD HH24:MI:SS')
and LO_SEQ not in (0)
and LO_SEQ > 0
and BK_STATUS <> 'CX'
and BG_SEQ = LO_FKEY_BG_SEQ
and BKD_FKEY_LO_SEQ = LO_SEQ
and BK_SEQ = BKD_FKEY_BK_SEQ ORDER BY BG_SITE, LO_SEQ, BKD_ADATE_START, LO_ROOM_NUMBER

Modification that i made marked by red color. Query is still not guaranteed to be run successfully.

by mistakenly i add END at the end of case...when exp. please dont add it in your query at line# 3 (marked with red color)
when LLL_CHILD_FKEY_LO_SEQ then LLL_PARENT_FKEY_LO_SEQ else LO_SEQ end END as LO_SEQ,

Yeah sorry about the code structure and size of it, someone else wrote the code and since then has left, leaving me to deal with it.

But thanks for that shafique, I will give it a try at work tomorrow.

shafique

afraid to say that doesnt work, it is erroring on the first 'when'.

the error message it is returning is:
ORA-00923: FROM keyword not found where expected

Put the case....when expression within a braket then try to run it. If you also send me the table structure so I could help you better.

select (CASE LO_SEQ
when LLL_PARENT_FKEY_LO_SEQ then LLL_CHILD_FKEY_LO_SEQ
when LLL_CHILD_FKEY_LO_SEQ then LLL_PARENT_FKEY_LO_SEQ else LO_SEQ END) LO_SEQ,

I have sorted I found that I never needed the left join in the query. So now it works, thanks for your help anyway.