Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Aug 2011
    Posts
    84

    Unanswered: putting value NULL in column need help

    Hi,

    can you help me on my stored procedure how can i make a stored procedure using data studio that will display value NULL in the column if there is no matched in other table,example customer_table and order_table,if the customer has no order in order_table the customer name will be change to NULL...Thank you in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use a CASE statement.

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Outer joins ?

    Quote Originally Posted by jemz View Post
    Hi,

    can you help me on my stored procedure how can i make a stored procedure using data studio that will display value NULL in the column if there is no matched in other table,example customer_table and order_table,if the customer has no order in order_table the customer name will be change to NULL...Thank you in advance.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Aug 2011
    Posts
    84
    sir in using the case,correct me if i am wrong

    case customer_name
    when customer_id=order_id THEN 'NULL'
    ELSE
    END AS CUSTOMER_NAME

    is this correct sir?i am confuse in the case...please help me

  5. #5
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by sathyaram_s View Post
    Outer joins ?
    Hi,thank you for the reply but how can i display null to the customer name or to change it null all the customer name if there is no order in the order_table?Thank you in advance

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    case customer_name
    when customer_id=order_id THEN 'NULL'
    ELSE
    END AS CUSTOMER_NAME
    You should see syntax diagram in this more carefully.
    CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

    1) Are you using searched-when-clause or simple-when-clause?
    They can't be intermixed.

    2) ELSE should be followed by NULL or result-expression.


    Code:
    CASE expression
    
    case-expression
    
    |--CASE--+-searched-when-clause-+------------------------------->
             '-simple-when-clause---'   
    
       .-ELSE NULL---------------.      (1)   
    >--+-------------------------+--END-----------------------------|
       '-ELSE--result-expression-'            
    
    searched-when-clause
    
       .-----------------------------------------------------.   
       V                                                     |   
    |----WHEN--search-condition--THEN--+-result-expression-+-+------|
                                       '-NULL--------------'     
    
    simple-when-clause
    
                   .-----------------------------------------------.   
                   V                                               |   
    |--expression----WHEN--expression--THEN--+-result-expression-+-+--|
                                             '-NULL--------------'
    3) "customer_id=order_id" is a search condition(simplest form of a condition which contains only a predicate) and not an expression.
    http://publib.boulder.ibm.com/infoce.../r0000754.html

    http://publib.boulder.ibm.com/infoce.../r0000746.html

    Code:
    Search conditions
    
    search-condition
    
    |--+-----+--+-predicate--+-------------------------------+-+---->
       '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |   
                '-(search-condition)---------------------------'   
    
       .------------------------------------------------------------------------.   
       V                                                                        |   
    >----+--------------------------------------------------------------------+-+--|
         '-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-'     
           '-OR--'  '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |       
                             '-(search-condition)---------------------------'
    Code:
    Basic predicate
    
    >>-expression--+- = ------+----expression----------------------><
                   |      (1) |                   
                   +- <> -----+                   
                   +- < ------+                   
                   +- > ------+                   
                   |      (1) |                   
                   +- <= -----+                   
                   |      (1) |                   
                   '- >= -----'
    Last edited by tonkuma; 08-16-11 at 13:52. Reason: Add 3).

  7. #7
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    You should see syntax diagram in this more carefully.
    CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

    1) Are you using searched-when-clause or simple-when-clause?
    They can't be intermixed.

    2) ELSE should be followed by NULL or result-expression.




    3) "customer_id=order_id" is a search condition(simplest form of a condition which contains only a predicate) and not an expression.
    Search conditions - IBM DB2 9.7 for Linux, UNIX, and Windows

    Basic predicate - IBM DB2 9.7 for Linux, UNIX, and Windows
    Hi,Thank you for this,but what if i have 4 tables how do i use the case?

  8. #8
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    You should see syntax diagram in this more carefully.
    CASE expression - IBM DB2 9.7 for Linux, UNIX, and Windows

    1) Are you using searched-when-clause or simple-when-clause?
    They can't be intermixed.

    2) ELSE should be followed by NULL or result-expression.




    3) "customer_id=order_id" is a search condition(simplest form of a condition which contains only a predicate) and not an expression.
    Search conditions - IBM DB2 9.7 for Linux, UNIX, and Windows

    Basic predicate - IBM DB2 9.7 for Linux, UNIX, and Windows
    Hello here is i want the output

    order_id order_unit customer_name
    0001 1pc John Mayer
    0002 2pcs Steve Jobs
    0003 3pcs Karl Debb
    Shane Karl
    Michael Shane
    since Shane Karl and Michael Shane have no order there names will be change to NULL.is this possible?Thank you in advance and I am hoping for your positive response.
    Last edited by jemz; 08-17-11 at 02:08.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    what if i have 4 tables how do i use the case?
    Too simple description to understand what you want to do.

    Please give me sample data and expected result.
    You should supply at least three or more rows for each of 4 tables.

  10. #10
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    Too simple description to understand what you want to do.

    Please give me sample data and expected result.
    You should supply at least three or more rows for each of 4 tables.


    Hi , please find the attachment. I also wrote the problem.please help me sir.Thank you in advance and more power to you always.
    Last edited by jemz; 08-18-11 at 23:47.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Hi sir, please find the attachment. I also wrote the problem.please help me sir.Thank you in advance and more power to you always.
    Attached Files
    MY.zip (2.1 KB, 2 views)
    I couldn't see the attached file.

    Errors were found in .ZIP file. attempt to fix
    If I choose Y, repeated same error.

  12. #12
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    I couldn't see the attached file.


    If I choose Y, repeated same error.

    Okay, I attached again the zip file.I hope you can get the attachment.Thank you in advance.
    Last edited by jemz; 08-18-11 at 23:48.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't see the newly attached file, too.
    May be something wrong with my setting or usage of unzip program in my laptop.

  14. #14
    Join Date
    Aug 2011
    Posts
    84
    Quote Originally Posted by tonkuma View Post
    I couldn't see the newly attached file, too.
    May be something wrong with my setting or usage of unzip program in my laptop.
    what about this,try this one...I hope you can get now.Thank you in advance.more power to you always

    Attachment 12056
    Last edited by jemz; 08-18-11 at 23:49.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't see the file, too

Posting Permissions

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