Re: This is not trivia pursuit....

[Robert Goshko <robert.goshko@AXIS-DEV.COM>]

I do not think you can use a correlated sub query as the value on a set
statement (I may be wrong, but I have never gotten it to work), try this
one:

SELECT 'UPDATE PS_EMPLOYMENT SET BUSINESS_TITLE = ''' ||
B.JobTitle ||
''' WHERE EMPLID = ''' ||
A.EmplID ||
''' AND Empl_Rcd# = ' ||
TO_CHAR( A.Empl_Rcd#, '999' ) ||
';'
FROM PS_EMPLOYMENT A
   , PS_EMPLOYEES B
WHERE A.EmplID          = B.EmplID
  AND A.Empl_Rcd#       = B.Empl_Rcd#
  AND A.Business_Title <> B.JobTitle

The last parameter will eliminate any conditions where the titles already
match, the result will be a series of update statements that can be run to
update the employment table.

.....Rob
_________________________________________________
Robert Goshko <mailto:robert.goshko@axis-dev.com>
Owner
Axis Developments
System Consulting Services
Sherwood Park, Alberta, Canada
http://www.axis-dev.com/


-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Sam Spritzer
Sent: Thursday, January 06, 2000 2:48 PM
To: SQR-USERS@list.iex.net
Subject: This is not trivia pursuit....


Don't you just love it when clueless users make a request that, in their
world, makes perfect sense to them within the realm of their logic and
common
sense.....hence, take a look at the following.

I am trying to update a varchar(30) column from one table with a varchar(30)
column from another table. I have already tested both columns for null
values....and there aren't any. Can any of you shed any light in this?

            *********************************************
SQL*Plus: Release 3.3.2.0.2 - Production on Thu Jan 06 16:26:11 2000

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.


Connected to:
Oracle7 Server Release 7.3.4.3.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.3.0 - Production

SQL> update ps_employment a
   2 set a.business_title = (select b.jobtitle
   3 from ps_employees b
   4 where a.emplid = b.emplid);
from ps_employees b
     *
ERROR at line 3:
ORA-01407: cannot update mandatory (NOT NULL) column to NULL
            ****************************************

SQRly yours,
Sam