Re: Need an SQL update lesson

The "IN" just brings back a list of values.

Put part of the where into the actual join. Change
Update JEFF.ORDTL d Set d.CWSTS = '3'
Where d.ONRCU
in (select h.ONRCU
from JEFF.ORHDR h
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1)
and d.ACREC = 'A'
and h.ACREC = 'A'
and d.CWSTS In('1', '2');
to this:
Update JEFF.ORDTL d Set d.CWSTS = '3'
Where d.ONRCU
in (select h.ONRCU
from JEFF.ORHDR h
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
And h.acrec = 'A')
and d.ACREC = 'A'
and d.CWSTS In('1', '2');

This works:

select hid, hord, lord, l.*
from erplxf.ech h inner join erplxf.ecl l
on h.hord = l.lord
and h.hid='CZ';


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jeff Crosby
Sent: Tuesday, January 29, 2019 10:40 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: Need an SQL update lesson

Old thread. It has been 7 months and we are finally at a point of moving
on this.

First off, Vern's suggestion of this:

Update JEFF.ORDTL d Set d.CWSTS = '3'
Where d.ONRCU
in (select h.ONRCU
from JEFF.ORHDR h
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1)
and d.CWSTS In('1', '2');

did indeed work. Now I want to do this:

Update JEFF.ORDTL d Set d.CWSTS = '3'
Where d.ONRCU
in (select h.ONRCU
from JEFF.ORHDR h
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1)
and d.ACREC = 'A'
and h.ACREC = 'A'
and d.CWSTS In('1', '2');

to make sure the line item and the order are both active. No joy. I get
this error:

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable ACREC not found. Cause . . . .
. : ACREC was not found as a column of table *N in *N and was not found
as a global variable in *N. If the table is *N, ACREC is not a column of
any table or view that can be referenced, or ACREC is a special register
that cannot be set in an atomic compound statement. Recovery . . . : Do
one of the following and try the request again: -- Ensure that the column
and table names are specified correctly in the statement. -- If this is a
SELECT statement, ensure that all the required tables were named in the
FROM clause. -- If the column was intended to be a correlated reference,
qualify the column with the correct table designator. -- If the column was
intended to be a global variable, qualify the name with the schema where
the global variable exists or ensure the schema is in the path. -- If this
is a SET statement for a special register within an atomic compound dynamic
statement, remove the statement or remove the ATOMIC keyword.

It's barfing on the h.ACREC reference. The field does exist in that file.
What am I doing that is not allowed?

Thanks.


On Tue, Jul 17, 2018 at 4:28 PM Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>
wrote:

Man, that is lucky if it worked!! And I'm very glad if it did!

Cheers
Vern

On 7/17/2018 3:25 PM, Jeff Crosby wrote:

I think Vern's worked. Further testing after vacation.

Thanks all. This list is so great.

On Tue, Jul 17, 2018 at 4:24 PM, Jim It <jim_it@xxxxxxxxxxx> wrote:

Jeff,

Select d.ONRCU, d.CWSTS, h.SHPDTISO, c.DATE1
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2');

Becomes

Merge Into JEFF.ORDTL as TGT
Using
(Select d.ONRCU, d.CWSTS, h.SHPDTISO, c.DATE1
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2')) As SRC On TGT.ONRCU =
SRC.ONRCU When Matched Then Set TGT.CWSTS = '3'

Jim
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of
Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
Sent: Tuesday, July 17, 2018 4:05 PM
To: Midrange Systems Technical Discussion
Subject: Need an SQL update lesson

Once again I need help with SQL, this time on an update.

This select:

Select d.ONRCU, d.CWSTS, h.SHPDTISO, c.DATE1
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2');

gives me the correct records. Now I want to update field (er,
column) d.CWSTS to a literal based on this select.

I found an example that implies this should work:

Update JEFF.ORDTL Set CWSTS = '3'
From JEFF.ORDTL d
Inner Join JEFF.ORHDR h On d.ONRCU = h.ONRCU
Inner Join JEFF.BILNGCNT c On h.SHPDTISO = c.DATE1
Where d.CWSTS In('1', '2');

but it barfs at the "From" so I assume some different syntax for
DB2 on

the

IBM i. I feel like I'm this close, but my googling has not
resulted in other examples.

After today I'm on vacation until Tuesday, but I just gotta know . . .

Thanks.


--


Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
direct.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion
of my company. Unless I say so.
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD
--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--


Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
direct.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com