Temporary table creation

Vaibhav Vyas · #1 (**permalink**) 06-02-10, 06:07

I have read only access to my database. I am using the query that bit too complex or long.

Can i create temporary table even though i have read only access, I fso can provide me the syntax.

mac4rfree · #2 (**permalink**) 06-02-10, 06:26

you can use the CTE in db2 ....

syntax will be

Quote:

with temp1 as (select.................)
select * from temp1

Vaibhav Vyas · #3 (**permalink**) 06-02-10, 06:50

Error: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "M TABLE1)". Expected tokens may include: "<query_expr_body>". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

mac4rfree · #4 (**permalink**) 06-02-10, 07:15

can you paste me your query???

Vaibhav Vyas · #5 (**permalink**) 06-02-10, 07:42

Here you go...

with temp1 AS (SELECT TM_ZN_DSCR FROM IBSPDM1A.TPDTM_ZN)

mac4rfree · #6 (**permalink**) 06-02-10, 08:07

after that statement, you have to fetch the records .. this temporary table is valid only for that run alone.

your query needs to be like...

with temp1 as (SELECT TM_ZN_DSCR FROM IBSPDM1A.TPDTM_ZN)
select * from temp1

Vaibhav Vyas · #7 (**permalink**) 06-02-10, 08:15

Thx..buddy..it's working...

Vaibhav Vyas · #8 (**permalink**) 06-02-10, 09:06

Hi As one temp table working but if say i wanna use two temp table

with temp1 as (select...)

with temp2(
select A.* from t1 a,table2 b
where a.id=b.id ) select * from temp2

Can we do like that?? i tried but getting some error.

Stealth_DBA · #9 (**permalink**) 06-02-10, 09:51

Close, but the WITH is only used once:

Code:

with temp1   as (select...
                      )
   , temp2   as (select A.* 
                 from t1 a
                    , table2 b
                 where a.id=b.id 
                ) 
select * from temp2

mac4rfree · #10 (**permalink**) 06-02-10, 10:19

cool man..

jkuyken · #11 (**permalink**) 06-02-10, 11:51

WITH can increase clarity and improve queries by forcing a re-examining of the query, but a query rewritten using 'WITH' can still error out because it is too long or complex.

I had a query that was very complex. In my experience long case statements often create queries that fail. I replaced a failing long case by placing very simple case statements in a pre-query using with like the following:

Code:

with source as
select
field 1,
field 2,
case when field1 < somevalue then 0 else 1 end as flg1,
case when field2 > somevalue then 0 else 1 end as flg2
from tablename
where cond

This meant that my query logic was much simplified and it ran:

Code:

select
field1,
field2,
case 
when flg1 = 0 and flg2 = 0 then 1
when flg1 = 0 and flg2 = 1 then 2
when flg1 = 1 and flg2 = 0 then 3
when flg1 = 1 and flg2 = 1 then 4
end as mycasename
from source

The logic is much more exposed and it ran, but with does not guarantee the query will not be too complex.

dav1mo · #12 (**permalink**) 06-02-10, 12:52

Vyas, you might, also, have better luck if you tell us what problem you were having with the original query and perhaps run a corrected version of that, rather than adding complexity to it.
Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech