Combine two sql queries into one query

问题:

How can I combine following 2 queries so that I can get two columns PAYMODE and PAYTYPE. Both queries are similar and for same table.Combine two sql queries into one query so that I don't need to execute two separate queries.

SELECT ETBL_DESC_TXT as PAYMODE
FROM tedit
WHERE CO_ID = 'CP'
AND   ETBL_TYP_ID = 'PMODE'
AND   ETBL_VALU_ID = 'RC'


select ETBL_DESC_TXT as PAYTYPE
FROM tedit
WHERE CO_ID = 'CP'
AND   ETBL_TYP_ID = 'PTYPE'
AND   ETBL_VALU_ID = 'ER'

回答1:

Since the records appear in different rows of the source data, I will be difficult to retrieve them in a single result row. You can get the results in a single query that gives you two rows. Try this:

SELECT ETBL_DESC_TXT as PAYFIELD
FROM tedit
WHERE CO_ID = 'CP' AND (
      (ETBL_TYP_ID = 'PMODE' AND ETBL_VALU_ID = 'RC')
   OR (ETBL_TYP_ID = 'PTYPE' AND ETBL_VALU_ID = 'ER')
)
ORDER BY ETBL_TYP_ID

The first row will contain the paymode, and the second row will contain the paytype.

回答2:

SELECT 'PAYMODE' as RowType,ETBL_DESC_TXT as PayValue
FROM tedit
WHERE CO_ID = 'CP'
AND   ETBL_TYP_ID = 'PMODE'
AND   ETBL_VALU_ID = 'RC'

union all

select 'PAYTYPE' as RowType, ETBL_DESC_TXT as PayValue
FROM tedit
WHERE CO_ID = 'CP'
AND   ETBL_TYP_ID = 'PTYPE'
AND   ETBL_VALU_ID = 'ER'

回答3:

you always can do this

select
    (
        SELECT ETBL_DESC_TXT
        FROM tedit
        WHERE CO_ID = 'CP'
        AND   ETBL_TYP_ID = 'PMODE'
        AND   ETBL_VALU_ID = 'RC'
    ) as PAYMODE,
    (    
        select ETBL_DESC_TXT
        FROM tedit
        WHERE CO_ID = 'CP'
        AND   ETBL_TYP_ID = 'PTYPE'
        AND   ETBL_VALU_ID = 'ER'
    ) as PAYTYPE
from SYSIBM.SYSDUMMY1

In SQL Server you can do this (Can't test it in DB2, sorry)

        SELECT
            max(case when ETBL_VALU_ID = 'RC' and ETBL_TYP_ID = 'PMODE' then ETBL_DESC_TXT else null end) as PAYMODE,
            max(case when ETBL_VALU_ID = 'ER' and ETBL_TYP_ID = 'PTYPE' then ETBL_DESC_TXT else null end) as PAYTYPE
        FROM tedit
        where 
            CO_ID = 'CP' and
            (
                 (ETBL_VALU_ID = 'RC' and ETBL_TYP_ID = 'PMODE') or
                 (ETBL_VALU_ID = 'ER' and ETBL_TYP_ID = 'PTYPE')
             )