PDA

Click to See Complete Forum and Search --> : T/SQL PL/SQL conversion

ben dhaou
03-20-2000, 01:46 PM
Can someone help me to convert this Sybase (T/SQL)
sequence :
UPDATE VALIDFQPAGE v1, FQPAGE f1
SET v1.freq=(v1.freqAbs/sumvalidpages), v1.code=f1.code
WHERE
v1.page=f1.page AND v1.returncode=f1.returncode;

to Oracle PL/SQL.

Thanks
Jason Rein
03-20-2000, 06:26 PM
Try this:

UPDATE
VALIDFQPAGE
SET
freq=(v1.freqAbs/v1.sumvalidpages), code=f1.code
FROM
VALIDFQPAGE v1
INNER JOIN FQPAGE f1 ON v1.page=f1.page AND
v1.returncode=f1.returncode


"ben dhaou" <cbendhaou@logmetrix.com> wrote:
>
> Can someone help me to convert this Sybase (T/SQL)
>sequence :
> UPDATE VALIDFQPAGE v1, FQPAGE f1
> SET v1.freq=(v1.freqAbs/sumvalidpages), v1.code=f1.code
> WHERE
> v1.page=f1.page AND v1.returncode=f1.returncode;
>
>to Oracle PL/SQL.
>
> Thanks
Alexandr Rayev
03-22-2000, 07:02 AM
UPDATE VALIDFQPAGE v1
SET v1.freq=(v1.freqAbs/v1.sumvalidpages),
v1.code=( Select f1.code
From FQPAGE f1
WHERE f1.page=v1.page AND
f1.returncode=v1.returncode);

This works if select return one row for each row from v1

"ben dhaou" <cbendhaou@logmetrix.com> wrote:
>
> Can someone help me to convert this Sybase (T/SQL)
>sequence :
> UPDATE VALIDFQPAGE v1, FQPAGE f1
> SET v1.freq=(v1.freqAbs/sumvalidpages), v1.code=f1.code
> WHERE
> v1.page=f1.page AND v1.returncode=f1.returncode;
>
>to Oracle PL/SQL.
>
> Thanks
jacek
03-22-2000, 09:27 AM
try that

UPDATE VALIDFQPAGE v1
SET v1.freq=(v1.freqAbs/v1.sumvalidpages),
v1.code=( Select f1.code
From FQPAGE f1
WHERE f1.page=v1.page AND
f1.returncode=v1.returncode
group by f1.code)
where exists
(select 1
From FQPAGE f1
WHERE f1.page=v1.page AND
f1.returncode=v1.returncode) ;



"ben dhaou" <cbendhaou@logmetrix.com> wrote:
>
> Can someone help me to convert this Sybase (T/SQL)
>sequence :
> UPDATE VALIDFQPAGE v1, FQPAGE f1
> SET v1.freq=(v1.freqAbs/sumvalidpages), v1.code=f1.code
> WHERE
> v1.page=f1.page AND v1.returncode=f1.returncode;
>
>to Oracle PL/SQL.
>
> Thanks
Rajesh Kumar
03-22-2000, 01:54 PM
"ben dhaou" <cbendhaou@logmetrix.com> wrote:
>
> Can someone help me to convert this Sybase (T/SQL)
>sequence :
> UPDATE VALIDFQPAGE v1, FQPAGE f1
> SET v1.freq=(v1.freqAbs/sumvalidpages), v1.code=f1.code
> WHERE
> v1.page=f1.page AND v1.returncode=f1.returncode;
>
>to Oracle PL/SQL.
>
> Thanks

You cannot use two tables in an update statement in Oracle,
so you need to process this update by opening a cursor for table f1 and then
loop through and issue an update.

CURSOR c1 is select v1.page, f1.page, v1.returncode, f1.returncode from VALIDFQPAGE
v1, FQPAGE f1
where v1.page=f1.page AND v1.returncode=f1.returncode;
open c1;
Loop until c1%notfound
fetch c1 into :var_page, var_f1page, var_v1retcode, var_f1retcode;
update VALIDFQPAGE set v1.freq=(v1.freqAbs/sumvalidpages),
v1.code= var_f1code
where v1.page = var_page, and
v1.returncode = var_v1retcode
End Loop
Close c1;



Hope this helps