クラシックファイルタイプは2021年1月をもって使用できなくなりました。クラシックファイルを移行することも、PDFをダウンロードすることもできます。詳細はこちら

SQL Editor

回答済み
0

コメント

8件のコメント

  • Paige Ringhofer

    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

    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

    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

    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

    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

    Thanks Jeff Hickey  

    Still not working :-( 

    0
  • Jeff Hickey

    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

    Thank you, Jeff! It worked!

     

    0

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