MVSFORUMS.com

psridhar · Beginner Joined: 16 May 2004 Posts: 68 Topics: 26

Hi,
I have a small problrm with the updation. I have two columns in a table T1 and two columns in table T2. For me master table is T1.

Columns are like this

Table 1
--------

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

Sri,
Can you be clear about what is needed, you are trying to update a different column 'ordertype' than what you have refered to. So there is a third column in play - is ordertype nullable ?

psridhar · Beginner Joined: 16 May 2004 Posts: 68 Topics: 26

I am sorry...... It is OrderName

kolusu · Posted: Fri Jun 25, 2004 12:43 pm Post subject:

Sridhar,

What is your DB2 Version?

I don't think you can update the table with 1 sql statement.Try this

psridhar · Beginner Joined: 16 May 2004 Posts: 68 Topics: 26

Hi Kolusu,

I am using DB2 V7.

Thanks alot.....

Can you help me in understanding what is IFNULL and when it is used?

vjpilp · Beginner Joined: 01 May 2004 Posts: 8 Topics: 2

I think you should try

kolusu · Posted: Fri Jun 25, 2004 12:54 pm Post subject:

vjpilp,

The value or coalesce function will be of NO use in this case. Because , the order_no is found in the table 1. You will never get a null value on the sub-select

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

psridhar · Beginner Joined: 16 May 2004 Posts: 68 Topics: 26

Hi Kolusu,

You are right. Those functions did not help me. Can you please throw some light on that are those functions and when they are useful?

vjpilp · Beginner Joined: 01 May 2004 Posts: 8 Topics: 2

Kolusu,
My understanding was that order_no will be found in table 1 but the order_name for the order_no could be NULL. I think psriddhar wants to insert 'PPP' if ordername for the corresponding order_no in T1 is NULL. rite?

So if the subselect could produce NULL... or am I wrong?

kolusu · Posted: Fri Jun 25, 2004 1:22 pm Post subject:

Sridhar and vjpilp,

The inner select has the T1 table as the driver. So your join will produce the results for all the order_no of t1 , but it does not look at order_no of t2.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

kolusu · Posted: Fri Jun 25, 2004 1:25 pm Post subject:

Sridhar,

Check this link for an explanation of NULLIF and IFNULL scalar functions

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/3.2?DT=20010718164132

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

psridhar · Beginner Joined: 16 May 2004 Posts: 68 Topics: 26

Hi vjpilp,

Table T1 will not have any row with Order_Name as spaces. The possibility is T2.Order_No itself may not be there in T1.

Hi Kolusu,

I tried the following SQL exactly. But still I am getting the same error.

UPDATE T2
SET T2.Order_Name = (SELECT T1.Order_Name
FROM T1
WHERE CHAR(T1.Order_No) = T2.Order_No) ;

Order_No in T2 is char(4) and in T1 is a small int.

Still getting same error.........

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

try this, I have not tested it

vjpilp · Beginner Joined: 01 May 2004 Posts: 8 Topics: 2

I think suresh kumar's code will work. Just one point here - It is generally not advisable to apply scalar functions on the left hand side like
WHERE CHAR(col_no) = col2_no because all rows for col_no will be fetched and CHAR will be applied. So its an overhead on the database.

Also should it not be WHERE CAST(col_no as CHAR(4)) = whatever? Idea

kolusu · Posted: Fri Jun 25, 2004 2:02 pm Post subject:

Suresh kumar,

Your query will not work because the inner select has the T1 table as the driver. So your join will produce the results for all the order_no of t1 , but it does not look at order_no of t2.

Sridhar

How does the the order_no in T1 & T2 look like when you select in spufi?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu