Create temporary table with data within SQL?

[mark66]

Hi,

This may well be very obvious to some but here goes anyway....

I want to summarise orders received throughout the day by hour. I want my report to show every how from 8am to 8pm regardless of whether orders were recieved within those hours.

So:

8 = 0
9 = 5
10 = 12
11 = 4
12 = 0
13 = 1 etc etc

Currently to do this I have created a simple file named TIME which contains 13 records 8 through to 20. I summarise my order data by hour and then join from TIME using a LEFT OUTER JOIN to the summarised order data.

This works fine, but I would like to know if I can skip the step of having to have the TIME file actually residing in our system and to be able to do the whole query within SQL without the need to ship a new file to each environment we run it in.

So something like this:

CREATE TEMP TABLE TIME
(HOUR/A2)
INSERT RECORDS
(1, 2, 3, 4, 5, 6, 7, 8 ETC)
SELECT HOUR, SUM(ORDERS)
FROM TIME
LEFT OUTER JOIN ORDERFILE
...
...
GROUP BY HOUR

Cheers

Mark

[tomholden]

Re: Create temporary table with data within SQL?

perhaps something like this:

with t1 as (select 1 as hour,2,3,4,5,6,7 from sysdummy)
select hour,sum(orders) from t1
left outer join orderfile
...
...
group by t1.hour

[mark66]

Re: Create temporary table with data within SQL?

Thanks Tom,

However I don't seem to be having any luck using sysdummy?

"SYSDUMMY in *LIBL type *FILE not found"

I have done a bit of a search around and there are lots of examples of DB2 SQL's using sysdummy and sysdummy1, but none of them are working here?!

Another simple example was:

select current date from sysibm.sysdummy1

On this I get "Qualified object name SYSDUMMY1 not valid". Does this work for you?

Any suggestions appreciated!

Thanks

Mark

[tomholden]

Re: Create temporary table with data within SQL?

that's the sql naming notation and most likely you're using *SYS naming. change it to select current_date from sysibm/sysdummy1

[B.Hauser]

Re: Create temporary table with data within SQL?

IIRC you want to replace the table with a single column where your hours are stored?
If so and you are on release 6.1 you can use values to generate a temporary file:

Select ListHour, Sum(Orders)
From (values (8), (9), (10), .... (20)) as a (ListHour) 
     left outer join OrderFile on OrderHour = ListHour

If you are not yet on release 6.1 you may use UNION clauses:

With x (ListHour) as (Select 8 from SysIbm/SysDummy1
         Union Select 9 from SysIbm/SysDummy1
         Union Select 10 from SysIbm/SysDummy1
         ....
         Union Select 20 from SysIbm/SysDummy1)
Select ListHour, Sum(Orders)
from x Left outer Join Orders on ListHour = OrderHour

A 3rd solution if you are on release V5R4 would be a recursive CTE.

Birgitta

[mark66]

Re: Create temporary table with data within SQL?

Hi guys, thanks for your help.

Tom, it was indeed the naming notation, I should have spotted that! However your method did just created me one record with many columns. I really wanted 13 records with just one column.

Birgitta, we are on 5.4, so I have used you UNION method and it works a treat.

Thanks again all