SQL group by and aggregation functions not working

[#1]

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

[#2]

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?

[phaneendra_kunc]

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

And what database?

[#4]

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

Thanks,

Tutan

[phaneendra_kunc]

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 ;