× 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

Sorry I missed the parenthesis. Rob is correct. Bring the checks into the join in the sub-select and you'll be fine.

Michael Salsman

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Salsman, Michael
Sent: Tuesday, January 29, 2019 08:48
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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=




As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.