Datatype

3 followers
0
Avatar

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

Aditya Koyyaamudi

9 comments

  • Avatar
    Aditya Koyyaamudi

    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
  • Avatar
    Alan Mark

    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
  • Avatar
    Aditya Koyyaamudi

    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
  • Avatar
    Alan Mark

    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
  • Avatar
    Aditya Koyyaamudi

    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
  • Avatar
    Joel Stewart

    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
  • Avatar
    Aditya Koyyaamudi

    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
  • Avatar
    Joel Stewart

    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
Please sign in to leave a comment.