Checking if table exists if not use another for a 'SELECT' statement

0
votes

I am still very new to SQL. I am working on a system which uses Derby database in development and Oracle in production. I want to have an SQL Statement which works in both. Here is my code:

SELECT rma.crspdt AS bic_crspndt,
               rma.issr   AS bic_issr
        FROM   rma
        WHERE  (rma.tp = 'Issued' OR rma.tp = 'Received')
        AND    rma.rmasts = 'Enabled'
        AND    rma.svcnm = 'swift.fin') r
       INNER JOIN (SELECT 1 ID FROM SYSIBM.SYSDUMMY1 UNION ALL
                   SELECT 2 ID FROM SYSIBM.SYSDUMMY1) dummy ON (dummy.id = 1 AND r.bic_crspndt IS NOT NULL)
                                                   OR (dummy.id = 2 AND r.bic_issr IS NOT NULL)

I am using here 'SYSIBM.SYSDUMM1' table. Oracle has an exact alternative table for 'SYSIBM.SYSDUMM1' named 'DUAL'. The problem is that when I run my code in development (derby) this code works fine but in production (oracle) I get an error saying something like unknown table.

What I want to do is that in my code do an IF-ELSE/CASE-WHEN or something like this to check in runtime if 'SYSIBM.SYSDUMMY1' table exists and if it does not exist (like in oracle) then I want to use 'DUAL' table. I am very new to SQL and would like some help in this matter.

3
mysqlsqloraclederby
What is the exact error that you get in production? - Nitish
@Nitish When I use 'DUAL' in place of 'SYSIBM.SYSDUMMY1' in development it says 'WARN | SQL Error: 20000, SQLState: 42X05 ERROR | Table/View 'DUAL' does not exist.' But when I use 'SYSIBM.SYSDUMM1' it works just fine. - ITguy
Dual is there only in Oracle. Not in Derby - Nitish
@Nitish This is the case when I use 'DUAL' instead of 'SYSIBM.SYSDUMMY1' in my code printed here in the question. The other error in production/eclipse I don't have at Hand at the moment. My question has more to do with SQL, precisely how to tell the Compiler to use the table which exists in this case and not the other one. - ITguy
Can you use a VIEW, and have the view be different in the two configurations? - Bryan Pendleton

3 Answers

0
votes

You don't say which Oracle version you are using. In Oracle 12c there is the SQL Translation Framework. With this example you could set up a translation such that SYSIBM.SYSDUMMY1 is translated to DUAL. I've seen demonstrations but haven't used it personally. I suggest the Oracle docs (as usual) for information - https://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA131.

0
votes

Can you not just creat a DUAL table

0
votes

There are some problem in your code from Oracle point of view which I can think of. So from comments what I get it that you are not able to use Dual. Dual exists in Oracle. So try running select 1 from dual and if it doesn't work, your query is not running in oracle for sure. Apart from it there are couple of more problem with your query.

  1. Using where before inner join.
  2. Extra closing braces for r

Based of above input, this query might work for you if you are running it in Oracle. Replace dual with sysibm.sysdummy if you are not using Oracle.

Note: You should use proper join syntax(INNER JOIN). I wasn't able to figure out joining condition hence I am using comma to join.

SELECT rma.crspdt AS bic_crspndt,
       rma.issr AS bic_issr
FROM rma r,
  (SELECT 1 ID FROM dual UNION ALL
   SELECT 2 ID FROM dual) dummy
WHERE ( (dummy.id = 1
         AND r.bic_crspndt IS NOT NULL)
       OR (dummy.id = 2
           AND r.bic_issr IS NOT NULL) 
     )
  AND (rma.tp = 'Issued'
       OR rma.tp = 'Received')
  AND rma.rmasts = 'Enabled'
  AND rma.svcnm = 'swift.fin'