CODEWITHSUNDEEP
sqloracle-databasems-accessodbc
Jarosław Szczepaniak
Jarosław Szczepaniak

Reputation: 63

MS Access pass-through query to Oracle VIA ODBC

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

Answers (2)

Jarosław Szczepaniak
Jarosław Szczepaniak

Reputation: 63

I have found the solution. It's ultra-simple :) Oracle ODBC driver does not allow to use a semicolon after the word ENDin a query. So i deleted the semicolon in the pass-through query definition and it works! Thank you mates for any answer.

Upvotes: 0

Tedo G.
Tedo G.

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

Related Questions