Datatype

Follow

Aditya Koyyaamudi

• June 19, 2018 14:16

 How to change the datatype of colum from string to boolean?

0

• 

  Aditya Koyyaamudi

  • June 19, 2018 14:22

  

  I'm trying to write an IF condition and I'm getting above error. Can someone help me to change the datatype of the column

  0

  Comment actions Permalink
• 

  Alan

  • June 19, 2018 14:27

  Hi Aditya,

  Please supply the formula you've written, as well as describe the contents of the columns.  Lastly please supply the description of what you're trying to do with the IF condition.

  Alan

  0

  Comment actions Permalink
• 

  Aditya Koyyaamudi

  • June 19, 2018 15:35

  Hi Alan,

  Thanks for  yours response. I have a column with values 'APPROVED',  'NOT APPROVED,  'PENDING' and the data type is STRING , now i want to create new column with condition

  'if APPROVED then 1', '

  if  NOT APPROVED then'2',

  if PENDING then '3'

  else '0'

   

  0

  Comment actions Permalink
• 

  Alan

  • June 19, 2018 15:43

  Hi Aditya,

  I would use the CASE function for this example:

  CASE(0;#ColumnName;"APPROVED";1;"NOT APPROVED";2;"PENDING";3)

  In this example we are using the first syntax type for CASE.

  CASE(<default value>; <any>; <case 1>; <return value 1>; <case 2>; <return value 2>; ...)

  So you see, the 'else' value(default value) is 0, then we say look at #ColumnName, and for any instance of "APPROVED" set the value of the new column to 1, "NOT APPROVED" to 2, and "PENDING" to 3.

  You can do this with an IF statement, but you have to build boolean conditions yourself.  Nested IF statements can get really complex, and for this example you would need at least three nested if statements.  Let me know if you want to explore that or if you're all set with CASE.  Note: CASE was added in 6.3 and higher - so if you're on 6.1 you'll need to upgrade before being able to use CASE.

  Alan

  0

  Comment actions Permalink
• 

  Aditya Koyyaamudi

  • June 19, 2018 15:58

  CASE statement is working fine, thanks for the support Alan!

   

  0

  Comment actions Permalink
• 

  Aditya Koyyaamudi

  • June 20, 2018 15:12

  Hi Alan,

  Could you please help me on how to write below case statement.

  case when LOAN_AMOUNT is null then null
  when trim(LOAN_AMOUNT) = ' ' then null
  when upper(LOAN_AMOUNT) like '%N%A%' then 99999
  else cast(LOAN_AMOUNT as numeric(12,0)) end)

  0

  Comment actions Permalink
• 

  Joel Stewart

  • June 20, 2018 15:23

  Aditya, you'll want to include the "else" case first as part of the default value per the documented syntax:

  CASE(<default value>; <any>; <case 1>; <return value 1>; <case 2>; <return value 2>; ...)

  After that, you can include the logic for each case to match with and then the result immediately following it. 

  0

  Comment actions Permalink
• 

  Aditya Koyyaamudi

  • June 20, 2018 15:32

  Thanks Joel, but how can I give column name as default value? the column is LOAN_AMOUNT and it has different values if it does not match any cases then it should display actual value present  in the column.

   

  case when LOAN_AMOUNT is null then null

  when trim(LOAN_AMOUNT) = ' ' then null
  when upper(LOAN_AMOUNT) like '%N%A%' then 99999
  else cast(LOAN_AMOUNT as numeric(12,0)) end

  if possible could you please provide me syntax for above query.

  0

  Comment actions Permalink
• 

  Joel Stewart

  • June 20, 2018 18:13

  Here's how I would start for the first couple of cases: 

  CASE(T(#ColumnName); ISNULL(#ColumnName); null; <boolean condition 2>; <return value 2>; ...)

   

  It's hard to extrapolate the other SQL formulas you're referencing without reference to the actual data. This structure should help you customize the remaining formulas to meet your needs though. 

  0

  Comment actions Permalink

Please sign in to leave a comment.