Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > putting value NULL in column need help

Reply
Page 1 of 3 1 23
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 08-16-11, 11:21
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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.
Reply With Quote
  #2 (permalink)  
Old 08-16-11, 11:34
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Use a CASE statement.

Andy
Reply With Quote
  #3 (permalink)  
Old 08-16-11, 11:59
sathyaram_s sathyaram_s is offline
Super Moderator
  
Join Date: Aug 2001
Location: UK
Posts: 4,534
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.
Reply With Quote
  #4 (permalink)  
Old 08-16-11, 12:14
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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
Reply With Quote
  #5 (permalink)  
Old 08-16-11, 12:19
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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
Reply With Quote
  #6 (permalink)  
Old 08-16-11, 12:32
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
Quote:
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.


Quote:
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

Quote:
Code:
Search conditions

search-condition

|--+-----+--+-predicate--+-------------------------------+-+---->
   '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |   
            '-(search-condition)---------------------------'   

   .------------------------------------------------------------------------.   
   V                                                                        |   
>----+--------------------------------------------------------------------+-+--|
     '-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-'     
       '-OR--'  '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |       
                         '-(search-condition)---------------------------'
Quote:
Code:
Basic predicate

>>-expression--+- = ------+----expression----------------------><
               |      (1) |                   
               +- <> -----+                   
               +- < ------+                   
               +- > ------+                   
               |      (1) |                   
               +- <= -----+                   
               |      (1) |                   
               '- >= -----'
Last edited by tonkuma; 08-16-11 at 12:52. Reason: Add 3).
Reply With Quote
  #7 (permalink)  
Old 08-16-11, 13:09
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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?
Reply With Quote
  #8 (permalink)  
Old 08-16-11, 13:17
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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

Quote:
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 01:08.
Reply With Quote
  #9 (permalink)  
Old 08-16-11, 13:20
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
Quote:
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.
Reply With Quote
  #10 (permalink)  
Old 08-16-11, 14:09
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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 22:47.
Reply With Quote
  #11 (permalink)  
Old 08-16-11, 19:20
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
Quote:
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.

Quote:
Errors were found in .ZIP file. attempt to fix
If I choose Y, repeated same error.
Reply With Quote
  #12 (permalink)  
Old 08-17-11, 00:37
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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 22:48.
Reply With Quote
  #13 (permalink)  
Old 08-17-11, 08:31
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
I couldn't see the newly attached file, too.
May be something wrong with my setting or usage of unzip program in my laptop.
Reply With Quote
  #14 (permalink)  
Old 08-17-11, 11:34
jemz jemz is offline
Registered User
  
Join Date: Aug 2011
Posts: 76
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 22:49.
Reply With Quote
  #15 (permalink)  
Old 08-17-11, 11:43
tonkuma tonkuma is offline
Registered User
  
Join Date: Feb 2008
Location: Japan
Posts: 2,193
I couldn't see the file, too
Reply With Quote
Reply
Page 1 of 3 1 23

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.