Re: SQL HELP

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

Ron

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

At 09:58 AM 9/13/02 -0400, you wrote:
> Hi All,
>
> I am trying to update a filed in file A based on a condition in file A and a
> condition in file B using STRSQL.  Following is the statement that I am
> using;
>
> UPDATE NCTSF/"RNK.PCTG" SET pcvbch = 0 WHERE pcvbch <> 0
> and (digits(PCYEAR) concat digits(PCSKEY)) in (SELECT (digits(PCYEAR
> ) concat digits(PCSKEY)) from NCTSF/"RNK.PPAR",  NCTSF/"RNK.PCTG"
> WHERE PPYEAR = PCYEAR AND PPSKEY = PCSKEY AND PPROLL <> 'V')
>
> It seems like it is trying to do the update, but the problem is that it just
> keeps going and going like it has a cartesian join or something.  I have
> about 13,000 records in each file and although file A has more that one
> record that matchs file B,  file B is unique on these fields.  I am getting
> a count 500,000 and it was still going.
>
> We are using this on a box at V4R4.
>
> Any suggestions what may be going on.
>
> TIA
> Ron