Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Answered: mysql isnull with case when for db2

    Hello,

    In mysql, we are able to make use of the isnull with case when such as below here

    select case when isnull((max(case columnA when 'test' then columnB end) or (max(case columnA when 'test' then columnB)=' '))
    then max((case columnA when 'item' then columnB end))
    else max((case columnA when 'price' then columnB end)) end) as product
    from tableA;

    Is there an alternative of using the above for isnull with case when and or operand in db2?

    Thanks in advance.

  2. Best Answer
    Posted by przytula_guy


  3. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You had another post for isnull() function and you got an answer. You may use that function and adapt the MySQL syntax to it.

    On the other hand, you are too bind to MySQL and you are trying to use DB2 as MySQL. It will not work - try to read some docs, understand how DB2 works, what are the differences and then try to write code in DB2 style.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #4
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thanks Guy Przytula.

    Yes Florin, I try if I could just replace it with isnull() user defined function, but it gave me an error.
    You are right, I need to rewrite it in sqlpl style.

    Thank you so much !

  6. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    That isnull() UDF returns an integer (0 or 1), it's not a boolean. Maybe that's your error about
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #6
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thanks Florin.
    Yes, that gave me an error when I tried to change the varchar to boolean as below here :

    create function isnull(b boolean)
    returns integer
    contains sql
    deterministic
    no external action
    return case when b is null then 1 else 0 end;

    ERROR when try to create the user defined function:
    A "BOOLEAN" data type is not supported in the context where it is being used.. SQLCODE=-20441, SQLSTATE=428H2, DRIVER=4.18.60

    I tried to to change the user defined function to boolean, but it gave me an error.
    In mysql, when i run the following select with isnull.

    table1 only has one row of record with symbol='abc' that has column name contain the value of 'test' only.
    1)
    select isnull((max(case name when 'test1' then symbol end)) or (max(case name when 'test1' then symbol end)='')) from table1 where symbol='abc';
    mysql will return a value of 1.

    2)
    select isnull((max(case name when 'test' then symbol end)) or (max(case name when 'test' then symbol end)='')) from table1 where symbol='abc';
    mysql will return a value of 0.

    Thanks in advance.

  8. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    --#SET TERMINATOR @
    
    create function isnull(b boolean)
    returns integer
    contains sql
    deterministic
    no external action
    begin
      return case when b is null then 1 else 0 end;
    end@
    Regards,
    Mark.

  9. #8
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Hi Mark,Thanks again for help!

    I am able to create the function that will take in a boolean and I has made a mistake, the statement should be taking in a varchar and return when it is null, it will return 1 else 0.
    With the create function that take in boolean and return integer.
    create function isnull(b boolean)
    returns integer
    contains sql
    deterministic
    no external action
    begin
    return case when b is null then 1 else 0 end;
    end@

    If I run the following, it will return an error :

    select isnull((max(case name when 'test' then symbol end))) from table1 where symbol='abc';

    Error:
    No authorized routine named "ISNULL" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.18.60

    Then I drop the function isnull(boolean) and recreate a udf with varchar since the column name is of varchar data type.

    create function isnull(b varchar(100))
    returns integer
    contains sql
    deterministic
    no external action
    begin
    return case when b is null then 1 else 0 end;
    end;

    I rerun the same select statement again,

    select isnull((max(case name when 'test' then symbol end))) from table1 where symbol='abc';

    Error: The invocation of routine "MAX" is ambiguous. The argument in position "1" does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=4.18.60

    Is this allow in sql pl?

    Thank you and appreciated your help!

    Best Regards,
    eshl

  10. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    I'm not able to reproduce your error.
    This works on my 9.7.6 database:
    Code:
    select isnull((max(case name when 'test' then symbol end))) 
    from table (values ('test', 'abc'), (null, 'abc')) t(name, symbol) 
    where symbol='abc'
    Does this statement work as is on your database?
    What's your db2 version and fixpack?
    What are the data types for name and symbol of your table1 table?

    BTW:
    You shouldn't yous begin ... end with a varchar parameter of the isnull function. You have to do this with a boolean parameters only.
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •