Re: Need an SQL update lesson

There isn't anything in there that appears to be wrong. It just looks like
the field ACREC isn't in JEFF.ORDTL or in JEFF.ORHDR. You're sure it's in
both of those files?

Michael Salsman
WinCo Foods, LLC
Programmer
Work: (208) 672-2038
Cell: (208) 949-8665

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Jeff Crosby
Sent: Tuesday, January 29, 2019 08:40
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange
.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOO
HQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0e
k&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=wqZyK0s7gVur8KwJB
Y0JtS0in0JSDUWHeReeSGlLAaA&e= or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=plNsMTxzWhWSFjZNkpToGxIFLyZhIzVJ50glDjI4ro4&e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD
&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGw
gqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jG
ASqfBa8JCX6Zw&s=RlAV-LTQttj49U5LlRytFcCeVjomuDXd2UVZo3uBrAA&e=
--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange
.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOO
HQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0e
k&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=wqZyK0s7gVur8KwJB
Y0JtS0in0JSDUWHeReeSGlLAaA&e= or email:
MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=plNsMTxzWhWSFjZNkpToGxIFLyZhIzVJ50glDjI4ro4&e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD
&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGw
gqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jG
ASqfBa8JCX6Zw&s=RlAV-LTQttj49U5LlRytFcCeVjomuDXd2UVZo3uBrAA&e=

--
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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.co
m_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwol
PvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod
9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=wqZyK0s7gVur8KwJBY0JtS0in0JSD
UWHeReeSGlLAaA&e= or email: MIDRANGE-L-request@xxxxxxxxxxxx Before
posting, please take a moment to review the archives at

https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=plNsMTxzWhWSFjZNkpToGxIFLyZhIzVJ50glDjI4ro4&e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=http-3A__amzn.to_2dEadiD&d=
DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyz
Zs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JC
X6Zw&s=RlAV-LTQttj49U5LlRytFcCeVjomuDXd2UVZo3uBrAA&e=

--


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://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=wqZyK0s7gVur8KwJBY0JtS0in0JSDUWHeReeSGlLAaA&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=plNsMTxzWhWSFjZNkpToGxIFLyZhIzVJ50glDjI4ro4&e=
.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=ls1vEGzGwgqZJyzZs7sGJ8CtK97ty2KqTEwuy7Bm0ek&m=eQSod9ZvgR6-jJaMjYzG4iujvdA-jGASqfBa8JCX6Zw&s=NDpAreQE33dV0jCuHrspdrw2dngs__6Kg-R0OTr4xKA&e=
--
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