RE: SQL question: -- MIDRANGE-L

Maybe I'm not seeing something here but is the OP updating all records in drvrmstrpf
Or only records that match the LOCN field in locnmstrpf when the PRDEND field
Has a value of 20100211.

In this case would this not work?

Update drvrmstrpf Set status = 'C' where locn in (select locn from locnmstrpf
Where prdend=20100211)

Or something along that line.

******************************************
Don Wereschuk
ISD - Programmer/Analyst
Simcoe Parts Service Inc.
Phone: 705-435-7814 Ex: 302
Fax: 705-435-5029
mailto:dwereschuk@xxxxxxxxxxxxxxx
******************************************
"Save the Cheerleader - Save the world" - Hiro Nakamura
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom
Sent: Thursday, February 18, 2010 11:31 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL question:

Thanks Charles! I'll go with option2 for now, and will look into/study
option 1 later on today.

TomH

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, February 18, 2010 10:21 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL question:

Look at your statement, ask yourself what value does a.status get if
there isn't a corresponding value in locnmstrpf.

The asnwer is NULL.

If a.status doesn't allow NULL, then you get the error you are seeing.

Two options,
1) Continue to update all records in drvrmstrpf but update records
without match in locnmstrpf with a default value or with the existing
value.
2) Change the statement to only update records in drvrmstrpf that have
a match in locnmstrpf

For option 2
Update drvrmstrpf a
Set a.status=
(select 'C' from locnmstrpf b
Where a.locn = b.locn and b.prdend=20100211)
where exists (select * from locnmstrpf c
where a.locn = c.locn and c.prdend=20100211)

I'll leave option 1 as an exercise for you. :) Here's a hint, you'll
probably want to use coalesce().

HTH,
Charles

On Thu, Feb 18, 2010 at 10:45 AM, Tom <tomh@xxxxxxxxxxx> wrote:

I have 2 tables: DRVRMSTRPF, LOCNMSTRPF.

DRVRMSTRPF fields: LOCN, DRVR, STATUS

LOCNMSTRPF fields: LOCN, PRDEND, STATUS

All drvrmstrpf.locn values are found in locnmstrpf, but there may be

some

locnmstrpf entries for which there are no corresponding entries in
drvrmstrpf.

I have a request from a user: update the drvrmstrpf status code to 'C'
for all locnmstrpf records with PRDEND value of 20100211.

I've tried the following SQL, but getting an error ("Null values not
allowed in column or variable STATUS"):

Update drvrmstrpf a

Set a.status=

(select 'C' from locnmstrpf b

Where a.locn = b.locn and b.prdend=20100211)

What am I missing?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.