Combining multiple SQL's into a single SQL

Question

Hi have the following queries

(SELECT COUNT(DISTINCT KUNDNR) CHECKED_CUSTOMER from CLNT0001.TCM_CHECK_SUMMARY
where '20170322000000000' <= HISTVON and HISTVON <  '20170323000000000' and INSTITUTSNR='0001')

and

SELECT clientNumber
,creationDate
,customerNumber
,checkedCustomer
,CLNT0001.TCM_CHECK_SUMMARY.COUNTRY_CODE countryCode
,CLNT0001.TCM_CHECK_SUMMARY.PST_KURZTEXT personStatus
,CLNT0001.TCM_CASE_COUNTRY_GROUP.COUNTRY_CODE homeCountryCode
,CLNT0001.TCM_CASE_COUNTRY_GROUP.PST_LFD_NR personStatusId
,CLNT0001.TCM_CASE_COUNTRY_GROUP.REGULATION regulation
,caseStatus
,COC_SCORE_COUNT cocCaseCount
 FROM (
   SELECT GEPRUEFT_JN checkedCustomer
     ,INSTITUTSNR clientNumber
     ,KUNDNR customerNumber
     ,CASE_STATUS caseStatus
     ,MAX(CREATION_DATE) creationDate
   FROM CLNT0001.TAXACTCASE
   WHERE GEPRUEFT_JN = 'J' AND CREATION_DATE>='20170322000000000' AND 
CREATION_DATE<='20170323000000000'
   GROUP BY KUNDNR
     ,INSTITUTSNR
     ,GEPRUEFT_JN
     ,CASE_STATUS
 ) T1
 INNER JOIN CLNT0001.TCM_CHECK_SUMMARY ON T1.customerNumber = CLNT0001.TCM_CHECK_SUMMARY.KUNDNR
 INNER JOIN CLNT0001.TCM_CASE_COUNTRY_GROUP ON T1.customerNumber = CLNT0001.TCM_CASE_COUNTRY_GROUP.KUNDNR
 WHERE T1.creationDate <= CLNT0001.TCM_CHECK_SUMMARY.HISTBIS
  AND T1.creationDate >= CLNT0001.TCM_CHECK_SUMMARY.HISTVON

I need the CHECKED_CUSTOMER column as a part of the second query's result set, i am not able to figure out a way to do this, is this possible ?

Alfaiz Ahmed · Answer 1 · 2017-09-26T06:07:36.473

SELECT clientNumber,creationDate,customerNumber,checkedCustomer
      ,CLNT0001.TCM_CHECK_SUMMARY.COUNTRY_CODE countryCode
      ,CLNT0001.TCM_CHECK_SUMMARY.PST_KURZTEXT personStatus
      ,CLNT0001.TCM_CASE_COUNTRY_GROUP.COUNTRY_CODE homeCountryCode
      ,CLNT0001.TCM_CASE_COUNTRY_GROUP.PST_LFD_NR personStatusId
      ,CLNT0001.TCM_CASE_COUNTRY_GROUP.REGULATION regulation
      ,caseStatus,COC_SCORE_COUNT cocCaseCount ,CHECKED_CUSTOMER
 FROM   (
        SELECT   GEPRUEFT_JN checkedCustomer,INSTITUTSNR clientNumber ,KUNDNR customerNumber ,CASE_STATUS caseStatus,MAX(CREATION_DATE) creationDate,COUNT(DISTINCT b.KUNDNR) CHECKED_CUSTOMER 
        FROM     CLNT0001.TAXACTCASE 
        LEFT JOIN  CLNT0001.TCM_CHECK_SUMMARY b ON CLNT0001.TAXACTCASE.KUNDNR=b.KUNDNR
        WHERE    GEPRUEFT_JN = 'J' AND CREATION_DATE>='20170322000000000' AND 
                 CREATION_DATE<='20170323000000000'
        GROUP BY KUNDNR,INSTITUTSNR ,GEPRUEFT_JN,CASE_STATUS

        ) T1 INNER JOIN CLNT0001.TCM_CHECK_SUMMARY ON T1.customerNumber = CLNT0001.TCM_CHECK_SUMMARY.KUNDNR

 INNER JOIN CLNT0001.TCM_CASE_COUNTRY_GROUP ON T1.customerNumber = CLNT0001.TCM_CASE_COUNTRY_GROUP.KUNDNR
 WHERE T1.creationDate <= CLNT0001.TCM_CHECK_SUMMARY.HISTBIS
  AND T1.creationDate >= CLNT0001.TCM_CHECK_SUMMARY.HISTVON

OOps just Aliasing Table CLNT0001.TCM_CHECK_SUMMARY as b mistakenly T2 I typed — Alfaiz Ahmed, Sep 26 '17 at 06:08

Combining multiple SQL's into a single SQL

1 Answers1