Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2009
    Posts
    17

    Unanswered: Temporary table creation

    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.
    Vyas| Miracle Happens

  2. #2
    Join Date
    Jul 2009
    Posts
    58
    you can use the CTE in db2 ....

    syntax will be

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

  3. #3
    Join Date
    Jul 2009
    Posts
    17

    Red face Getting error with that

    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)
    Vyas| Miracle Happens

  4. #4
    Join Date
    Jul 2009
    Posts
    58
    can you paste me your query???

  5. #5
    Join Date
    Jul 2009
    Posts
    17
    Here you go...

    with temp1 AS (SELECT TM_ZN_DSCR FROM IBSPDM1A.TPDTM_ZN)
    Vyas| Miracle Happens

  6. #6
    Join Date
    Jul 2009
    Posts
    58
    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

  7. #7
    Join Date
    Jul 2009
    Posts
    17
    Thx..buddy..it's working...
    Vyas| Miracle Happens

  8. #8
    Join Date
    Jul 2009
    Posts
    17

    Can we use multiple temp table??

    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.
    Vyas| Miracle Happens

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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

  10. #10
    Join Date
    Jul 2009
    Posts
    58
    cool man..

  11. #11
    Join Date
    May 2010
    Posts
    21

    With

    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.

  12. #12
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •