0

Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2.

The root query looks roughly like this:

Select 
    Col1, Col2, Col3
From (
    Select A.Col1, B.Col2, B.Col3
    From SchemaA.TableA as A
        Inner Join SchemaA.TableB as B
            On A.Id = B.ParentId
    Where 
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select

    Union

    Select A.Col1, B.Col2, B.Col3
    From SchemaB.TableA as A
        Inner Join SchemaB.TableB as B
            On A.Id = B.ParentId
    Where 
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select
) as Hybrid

Because Fetch First X Rows is not allowed in subselects or with unions, this isn't going to work. Since we're limiting the output, it makes sense to limit the input.

I figure I can achieve a fake union by feeding my subselects into temp tables instead and returning that output instead.

In T-SQL, I can create a temp table on the fly with a query like this how would I do that in DB2?

Select *
Into #Temp
From TableA

How would I do that in DB2? Or would do I need to create the table before inserting data into it?

2
  • 2
    What version of Db2 are you using? – Lennart May 29 '20 at 20:18
  • 1
    Can you please also include the source that backs up your statement: This is not allowed in a union or sub select. – mustaccio May 29 '20 at 21:46
0

Without an order by your query does not really make sense (IMHO). But nevertheless you can still use a query like:

select * from 
( select A.x 
  from A 
  order by x
  fetch first 2 rows only
)  
union all
(  select B.x 
   from B
   order by x
   fetch first 3 rows only
)  
; 

I made up a small example for you to play with at Fiddle

0

I think this can be solved by using a CTE for each of the two selects.

I created an example for you by joining the tables SYSIMB.TABLES and SYSIBM.VIEWS. From both I fetch the first 5 rows only.

WITH
    tables
    (
        name
    )
    AS
    (SELECT
            tab.name
       FROM
            sysibm.SYSTABLES tab
      FETCH
            FIRST 5 rows only
    ) 
    , views
    (
        name
    )
    AS
    (SELECT
            vie.name
       FROM
            sysibm.SYSVIEWS vie
      FETCH
            FIRST 5 rows only
    )

SELECT
    name
FROM
    tables
    
 UNION 

SELECT
    name
FROM
    views;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.