Clever Geek Handbook

Combine 3 Firebird queries

I have created 3 queries against my Firebird database.

1 - Get all families by date

select 
     distinct oh.FAMILY
from 
     TRACKCODE_SUMMARY ts, 
     ORDER_HEAD oh 
where 
     oh.AMF_NUM = ts.AMF_NUM 
     and ts.DATE_INDUCTION >= '2017-04-06';

2 - Get counter by date and family

select 
     count(*) AS Produced 
from 
     trackcode_summary ts, 
     order_head oh 
where 
     ts.AMF_NUM = oh.AMF_NUM 
     and ts.date_induction >= '2017-04-06'
     and oh.FAMILY like 'TULIP15BROAD';

3 - Get the number of unsuccessful ones by date and family

select 
    count(*) AS Failed 
from 
    trackcode_summary ts, 
    order_head oh 
where 
    ts.AMF_NUM = oh.AMF_NUM 
    and ts.date_induction >= '2017-04-06' 
    and (ts.emr >= 1 or ts.current_status = 200) 
    and oh.FAMILY like 'TULIP15BROAD';

These 3 queries are perfectly separated. I need to combine into one request.

How can i do this?

+3


source to share


1 answer


What about

SELECT 
     oh.FAMILY

     ,
     SUM
     (
        CASE WHEN oh.FAMILY LIKE 'TULIP15BROAD' THEN 1 ELSE 0 END
     ) AS Produced  

     ,
     SUM
     (
        CASE WHEN (ts.emr >= 1 OR ts.current_status = 200) AND oh.FAMILY LIKE 'TULIP15BROAD' THEN 1 ELSE 0 END
     ) AS Failed  

FROM  
     TRACKCODE_SUMMARY ts, 
     ORDER_HEAD oh 
WHERE  
     oh.AMF_NUM = ts.AMF_NUM 
     AND ts.DATE_INDUCTION >= '2017-04-06';

GROUP BY oh.FAMILY

By the way, instead LIKE 'TULIP15BROAD'

you should probably use= 'TULIP15BROAD'



Also, I think you are better off using JOIN keywords for better clarity.

FROM TRACKCODE_SUMMARY AS ts

INNER JOIN ORDER_HEAD AS oh 
    ON oh.AMF_NUM = ts.AMF_NUM
    AND ts.DATE_INDUCTION >= '2017-04-06'
+3


source






More articles:


All Articles