CODEWITHSUNDEEP
sqlunixinformatica-powercenter
Amogh Pradhan
Amogh Pradhan

Reputation: 11

How do i find number of columns returned by a complex SQL query

Take the below sample query. I want to find the number of columns returned by the query 

SELECT '29-JAN-16' AS AS_OF_DATE,
       WBD.CASHPOOL_TREAS_CODE,
       WBD.CNTRPART_TREAS_CODE,
       WBD.PRIN_BAL_AMT,
       (SELECT EX.EOD_SPOT_CNV_RATE
          FROM EDW.T_FACT_EXCH_RATE_LONG EX,
               EDW.T_DIM_DATE            DT,
               EDW.T_DIM_CURR            FC,
               EDW.T_DIM_CURR            TC
         WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
           AND EX.FROM_CURR_KEY = FC.CURR_KEY
           AND EX.TO_CURR_KEY = TC.CURR_KEY
           AND EXISTS (SELECT DT.CAL_DATE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')
           AND EXISTS (SELECT FC.CURR_CODE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')) AS EOD_SPOT_CNV_RATE,

       (SELECT EX.MOR_CURR_CNV_RATE
          FROM EDW.T_FACT_EXCH_RATE_LONG EX,
               EDW.T_DIM_DATE            DT,
               EDW.T_DIM_CURR            FC,
               EDW.T_DIM_CURR            TC
         WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
           AND EX.FROM_CURR_KEY = FC.CURR_KEY
           AND EX.TO_CURR_KEY = TC.CURR_KEY
           AND EXISTS (SELECT DT.CAL_DATE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')
           AND EXISTS (SELECT FC.CURR_CODE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')) MOR_CURR_CNV_RATE,
       (SELECT EX.MNTHLY_GAP_CURR_CNV_RATE
          FROM EDW.T_FACT_EXCH_RATE_LONG EX,
               EDW.T_DIM_DATE            DT,
               EDW.T_DIM_CURR            FC,
               EDW.T_DIM_CURR            TC
         WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
           AND EX.FROM_CURR_KEY = FC.CURR_KEY
           AND EX.TO_CURR_KEY = TC.CURR_KEY
           AND EXISTS (SELECT DT.CAL_DATE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')
           AND EXISTS (SELECT FC.CURR_CODE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')) MNTHLY_GAP_CURR_CNV_RATE,
       (SELECT EX.QTRLY_GAP_CURR_CNV_RATE
          FROM EDW.T_FACT_EXCH_RATE_LONG EX,
               EDW.T_DIM_DATE            DT,
               EDW.T_DIM_CURR            FC,
               EDW.T_DIM_CURR            TC
         WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
           AND EX.FROM_CURR_KEY = FC.CURR_KEY
           AND EX.TO_CURR_KEY = TC.CURR_KEY
           AND EXISTS (SELECT DT.CAL_DATE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')
           AND EXISTS (SELECT FC.CURR_CODE
                  FROM EDW.T_FACT_EXCH_RATE_LONG EX,
                       EDW.T_DIM_DATE            DT,
                       EDW.T_DIM_CURR            FC,
                       EDW.T_DIM_CURR            TC
                 WHERE EX.FIN_DATE_KEY = DT.DATE_KEY
                   AND EX.FROM_CURR_KEY = FC.CURR_KEY
                   AND EX.TO_CURR_KEY = TC.CURR_KEY
                   AND TC.CURR_CODE = 'USD')) QTRLY_GAP_CURR_CNV_RATE
  FROM EDW.T_ICF_CASHPOOL_ACCT_DETL ICAD
  LEFT OUTER JOIN (SELECT MLC.PD_TO_DATE,
                          ICBD.BAL_RPTG_DATE,
                          ICBD.IHB_ACCT_ID,
                          ICBD.BAL_CURR_CODE,
                          ICBD.OPEN_PRIN_BAL_AMT,
                          ICBD.PRIN_ADDN_AMT,
                          ICBD.PRIN_RPYMT_AMT,
                          ICBD.CLOSG_PRIN_BAL_AMT,
                          ICBD.OPEN_INT_BAL_AMT,
                          ICBD.ACCR_INT_CHRG_AMT,
                          ICBD.INT_SETL_AMT,
                          ICBD.CLOSG_INT_BAL_AMT,
                          ICBD.CASHPOOL_TREAS_CODE,
                          ICBD.CNTRPART_TREAS_CODE,
                          ICBD.PRIN_BAL_AMT
                     FROM EDW.T_ICF_CASHPOOL_BAL_DETL ICBD
                     JOIN EDW.T_MD_LOAD_CNTL MLC
                       ON MLC.SRCE_SYS_NM = 'EDW'
                      AND ICBD.BAL_RPTG_DATE = MLC.PD_TO_DATE) WBD
    ON ICAD.IHB_ACCT_ID = WBD.IHB_ACCT_ID

I have tried using the logic in Informatica as: Between 'SELECT' and 'FROM' count the number of commas+1. But this logic fails when there are derived columns like above query. What can be the solution for this problem? Solutions can be from Informatica ,UNIX scripts.. Running the query in Database directly is not advisable

PS:I know that number of columns are 8 here.This query is just a sample.I want to count number of columns by some logic in Expression transformation or even UNIX script is fine

Upvotes: 1

Views: 113

Answers (2)

Sam
Sam

Reputation: 3155

Either count them manually as answered by CathalMF so simply or insert the result of this query into a new table using Select Into and use the following query to find out number of columns:

SELECT COUNT(*)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('MyTable')

And if Executing the query is not an option then You would have to right some sort of code to parse the sql Query and find out the number of columns. Like as you said counting number of commas. And for derived columns you would need to match the opening and closing brackets and not counting the commas within those brackets. That's one way to do this.

Upvotes: 0

CathalMF
CathalMF

Reputation: 10055

Count them.

There are 8 columns.

Upvotes: 1

Related Questions