• 

  Paige Ringhofer

  • 2021年08月26日 19:28

  Hey Inessa,

  Thanks for reaching out! Just to get a little more information, do you have a column in the tbl_CCAR_Summary named Qtr?

  Thanks!

  0
• 

  Inessa Besi

  • 2021年08月26日 19:32

  Hi! Yes. I use CASE statement to create New_Column and assigm values to it. But if I want to use New_Column in Where statement or as a condition in another CASE statement the system wont recognize it. I've tried "Table_Name"."New_Column" and just "New_Column" format, unfortunatly neither worked. 

  0
• 

  Jeff Hickey

  • 2021年08月26日 19:50

  Hi Inessa. From the partial query you posted, it looks like you're trying to only select rows where Qtr is NA. If that is the case, try filtering on "cell_name" in the WHERE clause like you did in the CASE statement. Below is an example:

  SELECT
  "tbl_CCAR_Summary"."ID",
  (CASE WHEN "tbl_CCAR_Summary"."cell_name" Like 'C_______' THEN 'PQ0' ELSE 'NA' END) AS "Qtr
  FROM "<account_id>"."<table_id>" as "tbl_CCAR_Summary"
  WHERE "tbl_CCAR_Summary"."cell_name" NOT LIKE 'C_______'

  Without knowing your dataset, you may be able to eliminate the CASE statement. Example is below:

  SELECT
  "tbl_CCAR_Summary"."ID",
  'NA' as "Qtr"
  FROM "<account_id>"."<table_id>" as "tbl_CCAR_Summary"
  WHERE "tbl_CCAR_Summary"."cell_name" NOT LIKE 'C_______'

  0
• 

  Inessa Besi

  • 2021年08月26日 19:59

  Thanks, but that won't work. I'm trying to pick all the values that I need and drop everything else. I've assisted NA to the values that I dont want to use. 

  So, I need to be able to call "Qtr" column. Also, I need to use values from the "Qtr" column in another CASE statement to greate uniq key.

  SELECT

   

  "tbl_CCAR_Summary"."as_of_date" AS "Date",

  SUBSTR("tbl_CCAR_Summary"."source_of_the_data", 1, 7) AS "Source of the data",

  "tbl_CCAR_Summary"."worksheet_name" AS "Worksheet Name",

  SUBSTR("tbl_CCAR_Summary"."stream_values_combination",10) AS "Stream values combination",

  -- "tbl_CCAR_Summary"."cell_name" AS "Cell Name",

   

   (CASE

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'C_______'  THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  'C_______#___' ESCAPE '#' THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  'CCAR_C_______' THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  'DFAST_C_______' THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  '___#_C_______#___' ESCAPE '#' THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  '___#_C_______#__' ESCAPE '#' THEN 'PQ0'

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  'AFSOCIP_C_______#___' ESCAPE '#' THEN 'PQ0'

    

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'C________PQ_'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", -3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'C________PQ____'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", 10,3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'C________PQ___'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", 10,3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'CCAR_C________PQ_'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", -3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'DFAST_C________PQ_'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", -3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'CCAR_C________PQ___'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", 15,3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE 'DFAST_C________PQ___'  THEN SUBSTR("tbl_CCAR_Summary"."cell_name", 16,3)

     WHEN "tbl_CCAR_Summary"."cell_name" LIKE  '___#_C________PQ_#___' ESCAPE '#' THEN SUBSTR("tbl_CCAR_Summary"."cell_name", 14,3)

    ELSE 'NA'

        END) AS "Qtr",

  0
• 

  Jeff Hickey

  • 2021年08月26日 20:13

  I believe that WHERE is processed before SELECT so it won't know what "Qtr" is. The best path forward would probably be to use NOT IN. If you really need to use CASE, you may be able to build a query in the following structure

  SELECT <column1>, <column2>, etc
  FROM
  (
     SELECT CASE WHERE <condition> THEN <value> ELSE <value> END AS "tbl_CCAR_Summary" FROM <account_id>.<table_id>
  ) as "Qtr"
  WHERE "tbl_CCAR_Summary"."Qtr" = 'NA'

  0
• 

  Inessa Besi

  • 2021年08月26日 20:59

  Thanks Jeff Hickey  

  Still not working :-( 

  

  0
• 

  Jeff Hickey

  • 2021年08月28日 00:43
  • 編集済み

  Try moving all columns inside FROM like the following. For my tests, this worked:

  SELECT *
  FROM
  (
    SELECT
      "tbl"."as_of_date" AS "Date",
      SUBSTR("tbl"."source_of_the_data", 1, 7) AS "Source of the data",
      <etc>
      (CASE
         WHEN "tbl"."cell_name" LIKE 'C_____' THEN 'PQ0'
         WHEN "tbl"."cell_name" LIKE 'C_____#___' THEN 'PQ0'
         ELSE 'NA'
      END) AS "Qtr"
      FROM "QWNjb..."."3bf22..." AS "tbl"
  ) AS "temp"
  WHERE "temp"."Qtr" = 'NA'

  0
• 

  Inessa Besi

  • 2021年08月30日 14:57

  Thank you, Jeff! It worked!

   

  0

サインインしてコメントを残してください。