Awesome Dennis! Thanks!
PETER VIDAL
PALL CORPORATION / SR SYSTEM ANALYST @ WH APPLICATION DEVELOPMENT GROUP
10540 RIDGE RD., SUITE 203, NEW PORT RICHEY, FL 34654-5111
' 727-815-3104 Ä 7 727-815-3120 Ä þ WWW.PALL.COM
?Imagination is more important than knowledge...?
Albert Einstein (1879 - 1955)
"Dennis Lovelady" <iseries@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/05/2010 03:53 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc
Subject
RE: SQL UPDATE: Update a field with a field from another file
This is "my complication" on this simple SQL:
This is the SQL I am doing:
UPDATE ITEMBL A SET A.ITCLS =
(SELECT ITCLS FROM ITMRVA B
WHERE A.ITNBR = B.ITNBR AND B.STID = 'J2P' AND A.ITCLS <> B.ITCLS)
WHERE A.ITNBR = B.ITNBR AND A.HOUSE = 'JTG'
?
The error I am having is: COLUMN QUALIFIER OR TABLE B UNDEFINED. If I
remove the "B" qualifier from the last B.ITNBR in the SQL, then I have
this error instead: NULL VALUES NOT ALLOWED IN COLUMN OR VARIABLE ITCLS.
Hi, Peter:
Alan has already sent a correction that looks good to me. I thought I
would
explain that the reason it's complaining about that B qualifier is that B
was defined (confined) within your parenthesized expression. Outside of
that parenthesis set, B has no meaning.
When you removed it, you began to do the UPDATE for all rows where
A.HOUSE='JTG' - even those for which there was no matching row from the
second file. For those rows, the result was NULL (of course), and your
column apparently won't accept NULL values. (Which is a saving grace in
this case.)
I hope that helps.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"This is the most extraordinary collection of talent, of human knowledge,
that has ever been gathered together at the White House -- with the
possible
exception of when Thomas Jefferson dined here alone."
-- John F. Kennedy (to Nobel Prize winners)
midrange.com
