Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: SQL / UNION / temporary table

    Hi

    I have the following query:

    Select distinct FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027,99020,99021,99 023,99024,99027,99028,99029 ) and STATUS <> 4 and (PLZ='8600')
    UNION
    Select distinct FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and (PLZ='8600')

    How can I get the number of rows of the result WITHOUT use of a temporary table ?

    (UDB 8.1, Unix)

    Marc

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Marc,

    Try:

    with temp1 as (Select distinct FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027,99020,99021,99
    023,99024,99027,99028,99029 ) and STATUS <> 4 and (PLZ='8600')
    UNION
    Select distinct FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and (PLZ='8600') ) select count(*) from temp1;

    HTH

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Solution of ARwinner also generates temporaly table.

    I thing there is no way you can do it without temporaly table.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tip: remove the DISTINCT from each of the subqueries in the union, it is not necessary because UNION itself removes duplicate rows

    does this work? i don't have db2 to test on --
    Code:
    select count(*)
      from (
    select FK_ADRESSE_UUID 
      from ADU.UTADU_DET_PRAEMIE 
     where UNTERBRANCHE 
        in ( 93001,93006,93009,98020,98021,98027
           , 99020,99021,99023,99024,99027,99028,99029 )
       and STATUS <> 4 
       and PLZ='8600'
    union 
    select FK_ADRESSE_UUID 
      from ADU.UTADU_VERTRAG
     where BRANCHE 
        in ( 94,52010,88000,89000 )
       and STATUS <> 4 
       and PLZ='8600'
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Posts
    50
    Thanks, it works like this:

    select count(*) from (select FK_ADRESSE_UUID from ADU.UTADU_DET_PRAEMIE where UNTERBRANCHE in ( 93001,93006,93009,98020,98021,98027 , 99020,99021,99023,99024,99027,99028,99029 ) and STATUS <> 4 and PLZ='8600'union select FK_ADRESSE_UUID from ADU.UTADU_VERTRAG where BRANCHE in ( 94,52010,88000,89000 ) and STATUS <> 4 and PLZ='8600' ) as T

    -> I had to put "as T" at the end of your statement

    Marc

Posting Permissions

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