topic Re: SQL group by and aggregation functions not working in QlikView

SQL group by and aggregation functions not working

Fri, 22 Feb 2013 20:00:44 GMT

Hi,

I have a SQL which I am hitting from Qlikview. The SQL is as follows:

SELECT

DM_STORE.LOCATION_NUMBER,

DM_HOUR.HOUR_DESCRIPTION,

DM_HOUR.HOUR_NUMBER,

sum(DM_SALE_HOUR.US_AMOUNT),

sum(DM_SALE_HOUR.UNIT_QTY)

FROM

DM_SALE_HOUR,

DM_HOUR,

DM_DAY,

DM_STORE,

DM_PRODUCT

WHERE

( DM_SALE_HOUR.HOUR_ID=DM_HOUR.HOUR_ID )

AND ( DM_DAY.DAY_ID=DM_SALE_HOUR.DAY_ID )

AND ( DM_PRODUCT.PRODUCT_ID=DM_SALE_HOUR.PRODUCT_ID )

AND ( DM_SALE_HOUR.STORE_ID=DM_STORE.STORE_ID )

AND (

(DM_PRODUCT.PSA_CATEGORY_CODE IN ('10.02', '10.03')

OR DM_PRODUCT.PSA_SUBCATEGORY_CODE IN ('11.01.02'))

AND DM_DAY.MONTH_ID = 241

AND DM_HOUR.HOUR_DESCRIPTION IN ('2 PM to 3 PM', '3 PM to 4 PM', '4 PM to 5 PM', '5 PM to 6 PM', '6 PM to 7 PM', '7 PM to 8 PM')

)

GROUP BY

DM_STORE.LOCATION_NUMBER,

DM_HOUR.HOUR_DESCRIPTION,

DM_HOUR.HOUR_NUMBER

This doesn't work and the script stalls ( It doesn't even error out, it just stalls. The script time elapsed stalls at 1 second). But, if I remove the Group by Clause and "sum" in select statements, Qlikview pulls data. I did hit the above query in the database & it just works fine. I am not understanding what I am missing here. Please help.

Thanks,

Tutan

Re: SQL group by and aggregation functions not working

Sat, 23 Feb 2013 02:25:27 GMT

Has somebody any idea why would a aggregation and group by statement not work in this case? Can't Qlikview read a SQL in this format?

Re: SQL group by and aggregation functions not working

phaneendra_kunc — Sat, 23 Feb 2013 03:51:29 GMT

Have you tried running againest database directly? was it successful?

And what database?

Re: SQL group by and aggregation functions not working

Sat, 23 Feb 2013 03:53:54 GMT

Yes I did. Worked perfectly fine for the Oracle 9i database.

Thanks,

Tutan

Re: SQL group by and aggregation functions not working

phaneendra_kunc — Sat, 23 Feb 2013 04:29:13 GMT

I dont see any issue for that SQL..or that i am not aware...

May be you can do a trial and error method...

1.) Add "SQL" keyword before SELECT statement.... ana a LOAD too..

EX: Load *;

SQL Select .........;

2.) Try adding Schema Names infront of the Tables and add the Alias table names and use them..

Ex: Database01.Table_Name x

3.) Take some of the Joins Out and see if it still throws an error....

4.) finally Try script like this...I havent validated below once check for corrections...

Load *;

SQL SELECT

d.LOCATION_NUMBER,

b.HOUR_DESCRIPTION,

b.HOUR_NUMBER,

sum(a.US_AMOUNT),

sum(a.NIT_QTY)

FROM

DM_SALE_HOUR a

Join DM_HOUR b

on a.HOUR_ID=b.HOUR_ID

join DM_DAY c

on c.DAY_ID=a.DAY_ID

and c.DAY_ID=a.DAY_ID

Join DM_STORE d

on a.STORE_ID=d.STORE_ID

join DM_PRODUCT e

on e.PRODUCT_ID=a.PRODUCT_ID

WHERE (

(e.PSA_CATEGORY_CODE IN ('10.02', '10.03')

OR e.PSA_SUBCATEGORY_CODE IN ('11.01.02'))

AND c.MONTH_ID = 241

AND b.HOUR_DESCRIPTION IN ('2 PM to 3 PM', '3 PM to 4 PM', '4 PM to 5 PM', '5 PM to 6 PM', '6 PM to 7 PM', '7 PM to 8 PM')

)

GROUP BY

d.LOCATION_NUMBER,

b.HOUR_DESCRIPTION,

b.HOUR_NUMBER ;