Reputation: 63
I am trying to create a pass-through query in MS Access. The query connects to Oracle DB via ODBC.
My query works well in SQL Developer, but it contains CASE (...) END
construction.
As i found here there is a problem with Oracle ODBC driver with the word end
interpretation. And I get an error.
Is there any way to get around that problem?
My query:
SELECT
CASE
WHEN PHORGEN.NR_wniosku LIKE 'PHR%'
THEN 'text1'
WHEN PHORGEN.NR_wniosku LIKE 'PBR%'
THEN 'text2'
ELSE 'text3'
END field1,
CASE
WHEN PHORGEN.NR_wniosku IS NULL
THEN reklcomp.solution_date
ELSE
CASE
WHEN phorsol.OPIS_ROZWIAZANIA = '3'
THEN rekldet.SOLUTION_SENDING_DATE
ELSE NVL(
(SELECT MIN(DATA_KSIEGOWANIA)
FROM phorbook
WHERE POWOD_KSIEGOWANIA = '1'
AND Nr_Wniosku = PHORGEN.Nr_Wniosku
),rekldet.SOLUTION_SENDING_DATE)
END
END field2
FROM rgen
JOIN reklcomp
ON rgen.UIDINSTANCEID = reklcomp.UIDINSTANCEID
JOIN rekldet
ON rgen.UIDINSTANCEID = rekldet.UIDINSTANCEID
JOIN reklclient
ON rgen.UIDINSTANCEID = reklclient.UIDINSTANCEID
LEFT JOIN phorgen
ON RGEN.NR_WNIOSKU = PHORGEN.NUMER_OAR
LEFT JOIN phorsol
ON PHORGEN.UIDINSTANCEID = phorsol.UIDINSTANCEID
Upvotes: 1
Views: 737
Reputation: 63
I have found the solution. It's ultra-simple :)
Oracle ODBC driver does not allow to use a semicolon after the word END
in a query. So i deleted the semicolon in the pass-through query definition and it works!
Thank you mates for any answer.
Upvotes: 0
Reputation: 1565
You can also use DECODE Function.
I'm not familiar with ORACLE SQL, but I've had some time searching and It should work I think.
SELECT
DECODE(SUBSTR(PHORGEN.NR_wniosku, 1, 3)
, 'PHR', 'text1'
, 'PBR', 'text2'
, 'text3') field1
, DECODE(PHORGEN.NR_wniosku
, NULL, reklcomp.solution_date
, DECODE(phorsol.OPIS_ROZWIAZANIA
, '3', rekldet.SOLUTION_SENDING_DATE
, NVL((SELECT MIN(DATA_KSIEGOWANIA)
FROM phorbook
WHERE POWOD_KSIEGOWANIA = '1'
AND Nr_Wniosku = PHORGEN.Nr_Wniosku
),rekldet.SOLUTION_SENDING_DATE))) field2
FROM rgen
JOIN reklcomp
ON rgen.UIDINSTANCEID = reklcomp.UIDINSTANCEID
JOIN rekldet
ON rgen.UIDINSTANCEID = rekldet.UIDINSTANCEID
JOIN reklclient
ON rgen.UIDINSTANCEID = reklclient.UIDINSTANCEID
LEFT JOIN phorgen
ON RGEN.NR_WNIOSKU = PHORGEN.NUMER_OAR
LEFT JOIN phorsol
ON PHORGEN.UIDINSTANCEID = phorsol.UIDINSTANCEID
Upvotes: 1