Home » Other » Client Tools » perplexing oracle problem
perplexing oracle problem [message #25899] Fri, 23 May 2003 18:54 Go to next message
wassup
Messages: 3
Registered: May 2003
Junior Member
I have an Update SQL using a correlated subquery that just doesn't seem to work when it should...
Here are the 'key' players...

field type

bu varchar2(5)
book varchar2(10)
dt_time date
seq integer
posted_flag varchar2(1)

These are the keys (uniq) in table1 and table2

I am moving data from table1 to table2 and after moving I need to set the posted_flag to 'Y' in table1 so here goes my Update SQL.

This *has* to BE ANSI nothing specific to Oracle here

Line 1 : Update table1
Line 2 : set posted_flag = 'Y'
Line 3 : where exists (select 'x' from table2
Line 4 : where table1.bu = table2.bu
Line 5 : and table1.book = table2.book
Line 6 : and table1.dt_time = table2.dt_time
Line 7 : and table1.seq = table2.seq)
Line 8 : and bu = 'CHILD'
Line 9 : and book = 'MAIN'
Line 10: and posted_flag = 'N'

For some stupid reason this SQL does not work . However it works if I comment out Line 8 or replace it with

Line 8 : and bu like 'CHILD%'

So this would point to additional characters in field bu in table1, but I did a dump of table1 like so

SQL > Select bu, dump(bu) from table1

and I get the query coming back with

bu dump
___ _____

CHILD type 1 and the ASCII codes for C H I L D

and if I do a

SQL > Select bu||'$' from table1

I see the result as

CHILD$

don't seem like there are any additional characters at the end so why does this update not work??????

Any help much appreciated!!
Re: perplexing oracle problem [message #25908 is a reply to message #25899] Sun, 25 May 2003 23:36 Go to previous message
manish mendiratta
Messages: 110
Registered: May 2002
Senior Member
Try using rtrim or ltrim for your this column.
Previous Topic: order by
Next Topic: dynamic insert stat
Goto Forum:
  


Current Time: Thu Mar 28 04:50:32 CDT 2024