query giving zero output

Question

the tables i am using are retail_str_sales_detail,retail_store_prod,retail_store Actualy the main query this query is working but it is taking more time to execute so i tried to change that query into more efficient way

SELECT SET2.PROD_NM,SET2.TherapeuticClass,set2.TOTAL,SET2.QTY as QUANTITY,
set2.MFG as MFG,set2.monthname as MONTHNAME,set2.year as YEAR,
ROUND(((set2.TOTAL/SET3.TOTAL)*100),2) as SHARE
FROM (select
set1.PROD_NM AS PROD_NM,
set1.MFG AS MFG,
set1.monthname AS monthname,
set1.year AS year,
sum(set1.TOTAL) AS TOTAL,
sum(set1.qty) as QTY,
set1.TH_CLASS_1 AS TH_1,
set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,
set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select 
retail_store_prod.TH_CLASS_4 as TH_CLASS_4,
retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.MFG as MFG,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,
retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,
retail_store.str_nm,
sum(retail_Str_sales_detail.qty) as qty,
retail_Str_sales_detail.prod_nm  as PROD_NM,
monthname(retail_str_sales_detail.sale_date) as monthname,
year(retail_str_sales_detail.sale_date) as year,
round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,
retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id  and
retail_store.store_id = retail_store_prod.store_id
AND retail_store_prod.TH_CLASS_4 != 'NULL' AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL' AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != '' AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != '' AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,retail_store_prod.MFG,retail_str_sales_detail.sale_date,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1
group by set1.PROD_NM,set1.TH_CLASS_1,set1.TH_CLASS_2,set1.TH_CLASS_3,set1.TH_CLASS_4,set1.year,set1.monthname,set1.mfg,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
order by set1.PROD_NM)  as SET2
FULL OUTER JOIN
(select
sum(set1.TOTAL) AS TOTAL,sum(set1.qty) as QTY,
set1.TH_CLASS_1 AS TH_1,set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select  retail_store_prod.TH_CLASS_4 as TH_CLASS_4,retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,retail_store.str_nm,sum(retail_Str_sales_detail.qty) as qty,
retail_Str_sales_detail.prod_nm  as PROD_NM,round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id  and
retail_store.store_id = retail_store_prod.store_id
AND retail_store_prod.TH_CLASS_4 != 'NULL' AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL' AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != '' AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != '' AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1
group by set1.TH_CLASS_1,set1.TH_CLASS_2,set1.TH_CLASS_3,set1.TH_CLASS_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
) AS SET3
ON
SET3.TH_1= SET2.TH_1 AND SET3.TH_2 = SET2.TH_2 AND
SET3.TH_3= SET2.TH_3 AND SET3.TH_4 = SET2.TH_4

and i changed it to In this query i am trying to get the share of a product on the basis of combined therapeutic class

SELECT prod_nm
,      th_class_1 || ',' || th_class_2 || ',' ||
       th_class_3 || ',' || th_class_4              AS therapeuticclass
,      total
,      qty
,      mfg
,      yearmonth/100 as year
,      MONTHNAME(TO_DATE(yearmonth*100+1,'YYYYMMDD'))      as monthname 
,      Round(((
        total / SUM(total) OVER(PARTITION BY 
                       th_class_4, th_class_3, th_class_2, th_class_1 )
       )*100),2) AS share 
FROM
(   SELECT
           sd.prod_nm
    ,      sp.mfg    
    ,      sp.th_class_4
    ,      sp.th_class_3
    ,      sp.th_class_2
    ,      sp.th_class_1 
--  ,      sp.store_id      
--  ,      rs.str_nm 
    ,      INTEGER(sd.sale_date)/100  AS yearmonth
    ,      SUM(sd.qty)                AS qty
    ,      SUM(sd.total)              AS total
    FROM        
                retail_str_sales_detail sd 
    INNER JOIN  retail_store_prod       sp  ON  sd.prod_nm  = sp.prod_nm 
                                            AND sd.store_id = sd.store_id
    INNER JOIN  retail_store            rs  ON  rs.store_id = sp.store_id
    WHERE
            sp.th_class_4 NOT IN ('NULL','') 
    AND     sp.th_class_3 NOT IN ('NULL','') 
    AND     sp.th_class_2 NOT IN ('NULL','') 
    AND     sp.th_class_1 NOT IN ('NULL','') 
    GROUP BY
            sd.prod_nm 
    ,       sp.th_class_4
    ,       sp.th_class_3
    ,       sp.th_class_2
    ,       sp.th_class_1 
    ,       sp.mfg
--  ,       sp.store_id 
--  ,       rs.str_nm 
    ,       INTEGER(sd.sale_date)/100 
) s

Surely you don't have the string `'NULL'` in the columns? The proper way to check for `NULL` is `IS (NOT) NULL` — HoneyBadger, Mar 21 '18 at 08:27
Well, we don't know your data, how do you suppose we tell you why you're not getting results? Please read [mcve]. — HoneyBadger, Mar 21 '18 at 09:37
Please see my comments on your original question https://stackoverflow.com/questions/49377928/how-to-get-monthname-without-using-in-group-by-in-query-and-more-efficient/49394784#4939478 — Paul Vernon, Mar 21 '18 at 09:47
hye i know that but i just tried the thing which you are mentioning in comment — ROHIT JHA, Mar 21 '18 at 09:50
still not working giving a zero result should i share you a demo data — ROHIT JHA, Mar 21 '18 at 09:57

score 0 · Answer 1 · answered Mar 21 '18 at 10:44

0

By "zero result" you mean that the share column has a 0 value right (rather than you get zero rows back)? Ok, so I'm guessing that your "total" column is an INTEGER or BIGINT, and so we are doing an integer divide. Your original code included the ROUND() function that will covert things to DECIMAL. OK, so put that back in. ROUND(SUM(sd.total),2) AS total

answered Mar 21 '18 at 10:44

Paul Vernon

3,818
1
10
23

actually is executed succesfully but no data is returned in output – ROHIT JHA Mar 21 '18 at 10:59
if you are getting no row back, then you should be able to work out why that is quite easily. One approach would be to keep commenting things out of the SQL until you get some rows returned. E.g. comment out the filters, comment out some of the tables& joins. Check you have rows in all your tables. Replace the select list with a COUNT(*).. eventually you will work it out... – Paul Vernon Mar 21 '18 at 11:01
ok thanks for your help let me try if things doesnt work i will let you know dsure – ROHIT JHA Mar 21 '18 at 11:11

score 0 · Accepted Answer · answered Mar 21 '18 at 11:11

0

With these tables and data, the query returns two rows, so the query is OK. It must by your data that is at fault.

create table retail_str_sales_detail (prod_nm int, store_id int, sale_date date, qty int, total int); create table retail_store_prod (mfg int, prod_nm int, store_id int, th_class_1 char(4), th_class_2 char(4), th_class_3 char(4), th_class_4 char(4)); create table retail_store (store_id int, str_nm varchar(20)); insert into retail_str_sales_detail values (1,1,current date,1,1),(2,1,current date,5,5); insert into retail_store_prod values (1,1,1,1,1,1,1),(1,2,1,1,1,1,1); insert into retail_store values (1,1); Although if your total column is an INTEGER, you will need to convert it to a DECIMAL to get a non-zero share calc. E.g. DECIMAL(SUM(sd.total),19,2) AS total

answered Mar 21 '18 at 11:11

Paul Vernon

3,818
1
10
23

hye i ouput which i am not getting it is due to sd.sale_date/100 AS yearmonth without this i am able to get the result but if i remove this i am not getting the monthname and year – ROHIT JHA Mar 21 '18 at 11:27
What data type of sale_date. If it is a TIMESTAMP or CHAR or VARCHAR then you will need to do `INTEGER(DATE(sale_date))/100`. BTW if you are getting an error message, don't just say "I get no output", tell use what the error message is! Were you getting ""SQL0402N The data type of an operand of an arithmetic function or operation "/" is invalid. SQLSTATE=42819". – Paul Vernon Mar 21 '18 at 12:39
great it worked now thanks for your help no was not getting any error message – ROHIT JHA Mar 21 '18 at 13:10

query giving zero output

2 Answers2