× 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.



I'd sent a response on the 13th. Here it is again  <g>

You might try a correlated subquery. The subquery can
refer to the outside
fields without the join. You could use an alias for the
file, say "o" for
outer and "i" for inner, just to be short.

UPDATE NCTSF/"RNK.PCTG" o SET o.pcvbch = 0 WHERE
o.pcvbch <> 0
and (digits(o.PCYEAR) concat digits(o.PCSKEY)) in
(SELECT (digits(i.PPYEAR )
concat digits(i.PPSKEY)) from NCTSF/"RNK.PPAR" i
WHERE i.PPYEAR = o.PCYEAR AND i.PPSKEY = o.PCSKEY AND
i.PPROLL <> 'V')

In fact, since the field names are distinct, you could
leave the alias out,
I think. But they might make it easier for the optimizer

UPDATE NCTSF/"RNK.PCTG" SET pcvbch = 0 WHERE pcvbch <> 0
and (digits(PCYEAR) concat digits(PCSKEY)) in (SELECT
(digits(PPYEAR )
concat digits(PPSKEY)) from NCTSF/"RNK.PPAR"
WHERE PPYEAR = PCYEAR AND PPSKEY = PCSKEY AND PPROLL
<> 'V')

I THINK it is basically running the subselect for each
record that passes
the first test (pcvbch <> 0).

> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
> --
> [ Picked text/plain from multipart/alternative ]
> Hi All,
> I posted this question a couple of weeks ago on a Friday and didn't receive
> any responses so I'll try again with the hope someone else will see it.
>
> I need to update a field in file B based on a selection from file A and a
> condition in file B.  Following is the SQL statement I have;
>
> UPDATE NCTSFT16/"T16.PCTG" SET PC_MV_BATCH_NO = 0 WHERE pcvbch <> 0
> and (digits(PCYEAR) concat digits(PCSKEY)) in (SELECT (digits(PCYEAR
> ) concat digits(PCSKEY)) from NCTSFT16/"T16.PPAR",
>  NCTSFT16/"T16.PCTG"B
> WHERE PPYEAR = B.PCYEAR AND PPSKEY = B.PCSKEY AND PPROLL <> 'V')
>
> It seems to update the field okay but it runs for about 30 minutes on files
> that have 16,000 records apiece.  There is a one to many relationship
> between file A and file B.  We are trying to do this on V4R4.
>
> Any suggestions on how to refine this thing to speed it up?
>
> TIA
> Ron
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.