title: Recurssive DB2 Sql query to be used in informatica Source Qualifier Transformation
body: <p>i need to calculate metric values for 50 columns with same calculation  pattern. i cannot use Union Query for all 50 columns as Source Qualifier        in Informatica designer can hold upto 32767 characters.can anyone        suggest ways TO achieve this ?  </p>
  
  <pre><code>    SELECT djm.jobcode|| '_' || djm.job_region || '_' || 
        md.srvy_country_cd || '_' || djm.match_year AS srvy_label, 
             djm.match_year, 
             'NA'            AS SRVY_PUBLISHER, 
             'Blend'         AS cut, 
             'MI_UNIV_BLEND' AS srvy_name, 
             md.srvy_country_cd, 
       ( md.base_25 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_base_25, 
  ( md.base_50 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff )    adj_base_50, 
  ( md.base_75 ) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_base_75, 
             NULL  AS adj_COMM_25, 
             NULL   AS adj_COMM_50, 
             NULL AS adj_COMM_75, 
             djm.weight, 
             Sum(djm.weight) OVER (partition BY 
  djm.jobcode,djm.job_region,djm.match_year) sum_weight, 
             djm.weight*100 / Sum(djm.weight) OVER (partition BY 
  djm.jobcode,djm.job_region,djm.match_year) adj_weight 
  FROM       md 
  INNER JOIN djm 
  ON         djm.sk = md.sk 
  AND        djm.jobcode ='1234' 
  AND        djm.job_region ='USAB' 
  AND        djm.match_year ='2016' 
  AND        ( ( Nvl(md.base_25, 0) &gt; 0 AND Nvl(md.base_50, 0) &gt; 0 
              AND Nvl(md.base_75, 0) &gt; 0 ) ) 
  UNION 
  SELECT djm.jobcode|| '_' || djm.job_region || '_' || md.srvy_country_cd || 
  '_' || djm.match_year AS srvy_label,  
             djm.match_year, 
             'NA'            AS SRVY_PUBLISHER, 
             'Blend'         AS cut, 
             'MI_UNIV_BLEND' AS srvy_name, 
             md.srvy_country_cd, 
             NULL AS adj_base_25, 
             NULL AS adj_base_50, 
             NULL  AS adj_base_75, 
   ( md.comm_25) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_25, 
   ( md.comm_50) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_50, 
   ( md.comm_75) * ( 1 + djm.adjustment ) * ( djm.geo_diff ) AS adj_COMM_75, 
             djm.weight, 
             Sum(djm.weight) OVER (partition BY 
   djm.jobcode,djm.job_region,djm.match_year)sum_weight, 
             djm.weight*100 / Sum(djm.weight) OVER (partition BY 
  djm.jobcode,djm.job_region,djm.match_year) adj_weight 
  FROM       md 
  INNER JOIN djm 
  ON         djm.sk = md.sk 
  AND        djm.jobcode ='12178' 
  AND        djm.job_region ='USAB' 
  AND        djm.match_year ='2016' 
  AND        ( (  Nvl(md.comm_25, 0) &gt; 0 AND Nvl(md.comm_50, 0) &gt; 0 
              AND Nvl(md.comm_75, 0) &gt; 0 ) )  
  </code></pre>