Paste Search Dynamic
Recent pastes
mainSelectGeneration
  1. function mainSelectGeneration(sourceTableColumns, targetTableColumns, options){
  2.  
  3.         let {whereConditon = "", trimCols=[], lenFunction = "False", caseCol = ""} = options;
  4.     const sourceTableColumnsList = sourceTableColumns.split(",");
  5.     const targetTableColumnsList = targetTableColumns.split(",");
  6.         let mainSelect = [];
  7.  
  8.     for(let i=0;i<sourceTableColumnsList.length-1;i++)
  9.     {
  10.         if(trimCols.includes(sourceTableColumnsList[i])){
  11.                 mainSelect.push(`LTRIM(RTRIM(${sourceTableColumnsList[i]})) as ${targetTableColumnsList[i]}`)
  12.         }
  13.         else if(sourceTableColumnsList[i] == caseCol){
  14.                 mainSelect.push(`case
  15.                   WHEN LENGTH(${sourceTableColumnsList[i]}) > 0
  16.                   THEN ${sourceTableColumnsList[i]}
  17.                   else
  18.                   '0' END as ${targetTableColumnsList[i]}`);
  19.         }
  20.         else if(sourceTableColumnsList[i] == "end_tms"){
  21.                 if(lenFunction == "False"){
  22.                         mainSelect.push(`case
  23.                   WHEN ${sourceTableColumnsList[i]} is NOT null
  24.                   THEN ${sourceTableColumnsList[i]}
  25.                   else
  26.                   TIMESTAMP('9999-12-31 00:00:00') END as ${targetTableColumnsList[i]}`);
  27.                 }
  28.                 else{
  29.                         mainSelect.push(`case
  30.                   WHEN LENGTH(${sourceTableColumnsList[i]}) > 0
  31.                   THEN ${sourceTableColumnsList[i]}
  32.                   else
  33.                   TIMESTAMP('9999-12-31 00:00:00') END as ${targetTableColumnsList[i]}`);
  34.                 }
  35.         }
  36.         else{
  37.                  mainSelect.push(`${sourceTableColumnsList[i]} as ${targetTableColumnsList[i]}`);       
  38.         }
  39.     }
  40.  
  41.     mainSelect.push(`nCore as ${targetTableColumnsList[targetTableColumnsList.length-3]}`);
  42.     mainSelect.push(`CURRENT_TIMESTAMP as ${targetTableColumnsList[targetTableColumnsList.length-2]}`);
  43.     mainSelect.push(`${sourceTableColumnsList[sourceTableColumnsList.length-1]} as ${targetTableColumnsList[targetTableColumnsList.length-1]}`);
  44.     mainSelect.join(",");
  45.  
  46.     if(whereConditon !== ""){
  47.       whereConditon = `AND ${whereConditon}`;
  48.     }
  49.  
  50.     return {mainSelect, whereConditon};
  51. }
  52.  
  53. function queryGeneration(sourceTableColumns, targetTableColumns, rowNumPartitionByCols, rowNumOrderByCols, source_db_batch, actual_feed_name, orderByCols, options={}){
  54.  
  55.         let {mainSelect, whereConditon} = mainSelectGeneration(sourceTableColumns, targetTableColumns, options);
  56.  
  57.     finalQuery = `SELECT
  58.     ${mainSelect}
  59.     FROM
  60.     (
  61.     SELECT
  62.         ${sourceTableColumns},
  63.         ROW_NUMBER() OVER(PARTITION BY ${rowNumPartitionByCols.join(",")} ORDER BY ${rowNumOrderByCols.join(",")} DESC) as row_id
  64.     FROM
  65.     ${source_db_batch}.${actual_feed_name}_hist
  66.     ) as a
  67.     WHERE row_id = 1
  68.     ${whereConditon}
  69.     ORDER BY
  70.     ${orderByCols.join(",")}`
  71.  
  72.     console.log(finalQuery);
  73. }
  74. let sourceTableColumns = "issuer_dilution_factor_id,company_id,effective_date,dilution_factor,dilution_factor_footnote,dilution_factor_type_code,special_dilution_flag,dilution_factor_comment,gsfk_inst_mnem, start_tms,end_tms,etlloaddate";
  75. let targetTableColumns = "issuer_dilution_factor_id,company_id,effective_date,dilution_factor,dilution_factor_footnote,dilution_factor_type_code,special_dilution_flag,dilution_factor_comment,gsfk_institution_mnemonic,start_tms,end_tms,record_source,record_date_created,etlloaddate";
  76. let rowNumPartitionByCols = ["issuer_dilution_factor_id"];
  77. let rowNumOrderByCols = ["source_file_date"];
  78. let source_db_batch = "stage_ncore";
  79. let actual_feed_name = "vw_asx_issuer_dilution_factor";
  80. let orderByCols = ["issuer_dilution_factor_id", "start_tms"];
  81. let trimCol = ["special_dilution_flag"];
  82. let whereConditons="stat_def_id != 'LASUPTMS'";
  83. let lenFunction = "True";
  84. let caseCol = "dilution_factor_footnote";
  85. queryGeneration(sourceTableColumns, targetTableColumns, rowNumPartitionByCols, rowNumOrderByCols, source_db_batch, actual_feed_name, orderByCols, {whereConditon:whereConditons, trimCols:trimCol, lenFunction:lenFunction, caseCol:caseCol});
  86.  
Parsed in 0.017 seconds