× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. January 2019

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.