Perform a union only if table exists in database

Question

I have a composite query where I perform UNION between multiple table. Now My query fails when table does not exists. (Use case is that a table on which select query is perform is created on 5th of every month so my query fails till 5th of the month)

So I want my query to return data even if UNION OR JOIN fails due to Table does not exists error.

Now I tried using IF EXISTS but I am not sure how I should be using it and it resulted in syntax error.

Query Faills where table name is : THE TABLE WHICH DOES NOT EXISTS

This is my query :

SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       src AS clid,
       `column1`,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `Table1`
WHERE `accocode`='123456'
  AND calleedate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND callee LIKE 'Incoming%'
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calleedate,
       (CASE
            WHEN (position('SIP/' IN channel)>0) THEN substring(channel,position('/' IN channel)+1,position('_' IN channel)-position('/' IN channel)-1)
            ELSE substring(channel,position('/' IN channel)+1,position('@' IN channel)-position('/' IN channel)-1)
        END) AS clid,
       (CASE
            WHEN (`callee` = 'Outgoing') THEN CONCAT(`dst`,' - ',`country`)
            ELSE `dst`
        END)AS dst1,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `Table1`
JOIN
  (SELECT *
   FROM mvc_v2.isd_code
   ORDER BY `code` DESC)isd ON locate(isd.code,`dst`)=1
WHERE `accountcode`='1184372641'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND NOT (calltype LIKE 'Incoming%')
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       src AS clid,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `THE TABLE WHICH DOES NOT EXISTS`
WHERE `accountcode`='1184372641'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND calltype LIKE 'Incoming%'
UNION
SELECT CONCAT(CONVERT_TZ(calldate,'-4:00','+5:30'),' IST') AS calldate,
       (CASE
            WHEN (position('SIP/' IN channel)>0) THEN substring(channel,position('/' IN channel)+1,position('_' IN channel)-position('/' IN channel)-1)
            ELSE substring(channel,position('/' IN channel)+1,position('@' IN channel)-position('/' IN channel)-1)
        END) AS clid,
       (CASE
            WHEN (`calleeetype` = 'Outgoing') THEN CONCAT(`dst`,' - ',`country`)
            ELSE `dst`
        END)AS dst1,
       `columnn`,
       `columnn`,
       `columnn`,
       `columnn`
FROM `THE TABLE WHICH DOES NOT EXISTS`
JOIN
  (SELECT *
   FROM mvc_v2.isd_code
   ORDER BY `code` DESC)isd ON locate(isd.code,`dst`)=1
WHERE `accocode`='123456'
  AND calldate > DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND NOT (calleetype LIKE 'Incoming%')
ORDER BY `calleeedate` DESC

You will have to create a dummy table, the SQL parser will check that all the tables exist before running any statement. — Nigel Ren, Aug 02 '17 at 08:29
@NigelRen is that a good approach or its just temporary solution ? — Rajan, Aug 02 '17 at 08:31
Is this *MySQL* or *SQL Server*? Don't tag multiple database servers. — DavidG, Aug 02 '17 at 08:31
TBH I think it's a bad approach only creating tables when needed. You could just truncate the data when there is no valid data. But deleting the table IMHO is not a good idea. — Nigel Ren, Aug 02 '17 at 08:35
Is `IF EXISTS` useful for me ? I basically want to perform a UNION operation only if that tables exists or else give result for other UNION operations. — Rajan, Aug 02 '17 at 08:38
The "S" in SQL stands for "Structured". That means, amongst other things, that you are supposed to provide and know the structure of your tables. If you don't want/cannot do that, you have to find workarounds (e.g. check if the table exists before you execute the query, and depending on that, execute a different query with/without the `join`). At the time you execute the query, all used tables/columns/functions/... have to exist, otherwise you get an error. — Solarflare, Aug 02 '17 at 08:56
@Solarflare that's what I am asking how do I do that ? How do I perform a select or union statement if table exists ? — Rajan, Aug 02 '17 at 08:59
As I said: check if the table exist, then decide on the query. How you do that depends on how/where you run your query. You can use "show tables;" or query the information_schema (`select * from information_schema.tables where ...`) to check if the table exists. If you do this is a stored procedure (or e.g. php), you can use `IF...ELSE` based on the result. You could use dynamic sql and construct the query to use based on the result. Or you can just execute your query, and if it raises an error, catch it and execute the query without the join. Or simply just don't delete the table. — Solarflare, Aug 02 '17 at 09:08

Vijunav Vastivch · Answer 1 · 2017-08-03T00:12:26.340

1

You can use information_schema to check if tables exists or not

set @tablecount= 0;
select  count(TABLE_NAME) into @tablecount from information_schema.`COLUMNS` where TABLE_NAME = 'tableNameHere';
select @tablecount

You can use the count here:

if(@tablecount>0)
Your code here.......

edited Aug 03 '17 at 00:12

answered Aug 02 '17 at 09:36

Vijunav Vastivch

4,153
1
16
30

Perform a union only if table exists in database

1 Answers1