CSUM ISSUE

Database

CSUM ISSUE

Please look at the two querys below.
When I add a filter in the first select i get the correct results.
I did this to check if i was getting correct results for a hotel_id.
I am calculating the till date count and current month count of rooms and suites reserved.
calendar_mth_id is just an intiger. It is like 200701,200712 where 200701 is jan2007 and 200712 is dec 2007

SELECT
b.hotel_id,
b.calendar_mth_id,
b.rooms_reserved_td,
b.suites_reserved_td,
rooms_reserved_cm,
suites_reserved_cm

FROM
(
SELECT
a.hotel_id,
a.calendar_mth_id,
CSUM(a.rooms_reserved_count, a.hotel_id,a.calendar_mth_id) as rooms_reserved_td,
CSUM(a.suites_reserved_count, a.hotel_id,a.calendar_mth_id) as suites_reserved_td

FROM
(
SELECT
hotel_id,
calendar_mth_id,
SUM(rooms_reserved_date_count) as rooms_reserved_count,
SUM(suites_reserved_date_count) as suites_reserved_count
FROM
PRODTAB.hotel_info_table
where hotel_id = 1500
GROUP BY
1,2)a)b

LEFT JOIN

(
SELECT
hotel_id,
calendar_mth_id,
rooms_reserved_date_count as rooms_reserved_count,
suites_reserved_date_count as suites_reserved_count
FROM
PRODTAB.hotel_info_table
where hotel_id = 1500
GROUP BY 1,2)c
ON
b.hotel_id=c.hotel_id
a.calendar_mth_id = c.calendar_mth_id

CORRECT RESULT SET EXAMPLE and I get this for the above query correctly.

1500 200708 0 0 0 0
1500 200709 3 2 3 2
1500 200710 4 4 1 2
1500 200711 7 8 3 4

-------------------------------------------------------------------------------------------------
Now when I create a view with the above query removing the filter and later filter on the view I do not get the same results.

REPLACE VIEW PRODVWS.HOTEL_MTH_COUNT_VW
AS

SELECT
b.hotel_id,
b.calendar_mth_id,
b.rooms_reserved_td,
b.suites_reserved_td,
rooms_reserved_cm,
suites_reserved_cm

FROM
(
SELECT
a.hotel_id,
a.calendar_mth_id,
CSUM(a.rooms_reserved_count, a.hotel_id,a.calendar_mth_id) as rooms_reserved_td,
CSUM(a.suites_reserved_count, a.hotel_id,a.calendar_mth_id) as suites_reserved_td

FROM
(
SELECT
hotel_id,
calendar_mth_id,
SUM(rooms_reserved_date_count) as rooms_reserved_count,
SUM(suites_reserved_date_count) as suites_reserved_count
FROM
PRODTAB.hotel_info_table

GROUP BY
1,2)a)b

LEFT JOIN

(
SELECT
hotel_id,
calendar_mth_id,
rooms_reserved_date_count as rooms_reserved_count,
suites_reserved_date_count as suites_reserved_count
FROM
PRODTAB.hotel_info_table

GROUP BY 1,2)c
ON
b.hotel_id=c.hotel_id
a.calendar_mth_id = c.calendar_mth_id

Now when I do a
SELECT * FROM PRODVWS.HOTEL_MTH_COUNT_VW
WHERE hotel_id = 1500

I donot get the same results. I tried a max on the counts and added a group by even that did not work for the views, I get a lot of duplicate rows with uncorrect numbers.

Can any one tell me where my mistake is...
Thanks in Advance

2 REPLIES

Re: CSUM ISSUE

Can you provide me some data and final data how should it look like or you can mail me prashanth.teradata@gmail.com
N/A

Re: CSUM ISSUE

Could you please post the actual source code, because both queries are not valid SQL.

The different result sets might be correct based on some scoping rules in SQL.

Btw, if you remove that deprecated CSUM with a SUM OVER you probably don't need that join anymore, because aggregates might be nested in OLAP functions.

Dieter