Is there any way to write SQL statement that if this statement cannot retrieve data then use the other SQL statement?

Question

(Is there any way to write SQL statement that if this statement cannot retrieve data (because some part of its table was null) then use the other SQL statement or make it show only data that available ?)

I have SQL statement to show the production lot and each of their latest activity which show below ...

q_maxdate :

SELECT 
    tbl_pdtn_startup.pdtn_st_id, 
    tbl_production.pdtn_id, 
    tbl_product.pd_name
FROM 
    (
        tbl_pdtn_startup 
        INNER JOIN tbl_product 
        ON tbl_pdtn_startup.pd_id = tbl_product.pd_id
    ) 
    INNER JOIN tbl_production 
    ON tbl_pdtn_startup.pdtn_st_id = tbl_production.pdtn_st_id;

which join with this SQL

SELECT 
    q_maxdate.pdtn_st_id, 
    Sum(tbl_pdtn_sizecolor.pdtn_st_qty_est) AS SumOfpdtn_st_qty_est,
    q_maxdate.MaxOfpdtn_date, 
    q_maxdate.pd_name, 
    q_maxdate.pdtn_st_pdNote, 
    q_maxdate.pd_id, 
    q_maxdate.MaxOfwk_stage, 
    q_maxdate.LastOflb_name, 
    q_maxdate.pdtn_st_date, 
    q_maxdate.pdtn_st_qty_act
FROM 
    q_maxdate 
    INNER JOIN tbl_pdtn_sizecolor 
    ON q_maxdate.pdtn_st_id=tbl_pdtn_sizecolor.pdtn_st_id
GROUP BY 
    q_maxdate.pdtn_st_id, 
    q_maxdate.MaxOfpdtn_date, 
    q_maxdate.pd_name, 
    q_maxdate.pdtn_st_pdNote, 
    q_maxdate.pd_id, 
    q_maxdate.MaxOfwk_stage, 
    q_maxdate.LastOflb_name, 
    q_maxdate.pdtn_st_date, 
    q_maxdate.pdtn_st_qty_act
ORDER BY 
    q_maxdate.pdtn_st_date DESC;

The problem of this is it won't show any record that have only data in tbl_pdtn_startup but null in tbl_production. So what I want is if this case occur then execute the new SQL statement that show only data that are available.

something like ... if sql1 <> "" then execute sql1 else execute sql2 end if

thank you very much

(EDITED) According to the answer below I've picked "IF NOT EXIST" to solve this problem by using as below:

IF NOT EXISTS (SELECT q_maxdate.pdtn_st_id, Sum(tbl_pdtn_sizecolor.pdtn_st_qty_est) AS SumOfpdtn_st_qty_est, q_maxdate.MaxOfpdtn_date, q_maxdate.pd_name, q_maxdate.pdtn_st_pdNote, q_maxdate.pd_id, q_maxdate.MaxOfwk_stage, q_maxdate.LastOflb_name, q_maxdate.pdtn_st_date, q_maxdate.pdtn_st_qty_act
FROM q_maxdate INNER JOIN tbl_pdtn_sizecolor ON q_maxdate.pdtn_st_id=tbl_pdtn_sizecolor.pdtn_st_id
GROUP BY q_maxdate.pdtn_st_id, q_maxdate.MaxOfpdtn_date, q_maxdate.pd_name, q_maxdate.pdtn_st_pdNote, q_maxdate.pd_id, q_maxdate.MaxOfwk_stage, q_maxdate.LastOflb_name, q_maxdate.pdtn_st_date, q_maxdate.pdtn_st_qty_act
ORDER BY q_maxdate.pdtn_st_date DESC ) 

THEN SELECT tbl_pdtn_startup.pdtn_st_id, tbl_product.pd_name, tbl_pdtn_startup.pdtn_st_date
    FROM (tbl_pdtn_startup INNER JOIN tbl_product ON tbl_pdtn_startup.pd_id = tbl_product.pd_id) INNER JOIN tbl_production ON tbl_pdtn_startup.pdtn_st_id = tbl_production.pdtn_st_id
    GROUP BY tbl_pdtn_startup.pdtn_st_id, tbl_product.pd_name, tbl_pdtn_startup.pdtn_st_date
    ORDER BY tbl_pdtn_startup.pdtn_st_date DESC 
END

However, it didn't work.. My SQL skill very limited. So could you please help me solve this.. thank you very much.

Do you have to use INNER JOIN? This is why you inly see records when there are records in all the tables. If you use LEFT JOIN, you will get records even if there are no records in the other tables. However, you have to be careful with WHERE statements, you may have to use, for example, `WHERE Colour="Red" Or Is Null` — Fionnuala, Sep 10 '12 at 11:21
well.. this solution work well in ACCESS, however when I put it to my page, it fail.. It said.. "Data provider or other service returned an E_FAIL status.". — Alxan, Sep 10 '12 at 12:51

score 2 · Answer 1 · edited Sep 10 '12 at 03:59

2

You could use @@ROWCOUNT to determine how many rows are returned by your sql1. If rowcount is 0, then probably it returned nothing, so use sql2

You could also use IF NOT EXISTS

edited Sep 10 '12 at 03:59

Vikdor

23,934
10
61
84

answered Sep 10 '12 at 03:57

Mandar

498
1
4
17

could you please give me some sample ?? i've made the sql query according to your post, but it said Syntax error. – Alxan Sep 10 '12 at 06:02
Could you paste the query that you have created? – Mandar Sep 10 '12 at 06:03
can you check by replacing EXIST with EXISTS ? – Mandar Sep 10 '12 at 06:08
Already use "EXISTS" but the access still said "Invalid SQL Statement" Is it possible that this syntax not work in MS ACCESS? – Alxan Sep 10 '12 at 06:15
I dont know its equivalent in MS ACCESS but What you could do is after select statement check in your CODE what recordset returns, and then decide. – Mandar Sep 10 '12 at 06:22

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

There are several ways to accomplish this.

Oracle PL/SQL and MSSQL T-SQL, for example, both allow "if-then-else".

The portable way, however, is to use the ANSI-SQL "case" statement. For example:

edited May 23 '17 at 12:27

Community

1
1

answered Sep 10 '12 at 03:56

paulsm4

114,292
17
138
190

The tags says MS Access. SQL is a generic term. – Fionnuala Sep 10 '12 at 11:18

score 0 · Answer 3 · answered Sep 10 '12 at 13:01

0

I think the easiest way would be to write stored procedure you can check below link

http://www.databasejournal.com/features/msaccess/article.php/3407531/How-to-Execute-SQL-Stored-Procedures-from-Microsoft-Access.htm

Happy coding!!!

answered Sep 10 '12 at 13:01

Ravi Vanapalli

9,805
3
33
43

Is there any way to write SQL statement that if this statement cannot retrieve data then use the other SQL statement?

3 Answers3