Re: dense_rank error, help needed

KM wrote:

> I've tried searching and found nothing, so I end up here. I'll post
> the code and the error message.
>
> First my situation is that I am running Embarcadero db Artisan 5.1.
> The code when executed from the workspace works fine, results come
> back great.
>
> When I put this on the server and try to run the sql on the unix box,
> I get an error.
>
> I learned about the "check syntax" icon on the artisian and I get the
> error below. Why does this work when I run it from the workspace and
> return a complete result set, but throughs a syntax error and again
> fails on the unix side?
>
> Thanks for all help in advance.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> here is the code: (sorry if the column names are cryptic, had to mask
> the true data)
>
> SELECT PRD,
> N_I,
> NME_X,
> A_I,
> '1' AS TG_N,
> DENSE_RANK() OVER (PARTITION BY '1' ORDER BY SUM(NEW_Y + TOT_Y)
> DESC ) "RANK_N",
> E_X,
> D_D,
> ME_X,
> NME_X,
> AC_X,
> SUM(NEW_Y + TOT_Y) AS AST_Y,
> SUM(ACC_Z) AS W_ACC_Z
> FROM table__FA
> WHERE RT_X IN ('RS','RAP','AS','SEL','MULTI','LAA')
> GROUP BY PRD,
> PRD,
> N_I,
> NME_X,
> A_I,
> E_X,
> D_D,
> ME_X,
> NME_X,
> AC_X,
> HAVING SUM(ACC_Z) >= '5';
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> error when doing 'syntax check':
>
> UNRECOGNIZED CONSTRUCT (or PL/SQL syntax error)
> (...code extract...)
> 605 expecting INTO BULK FROM
>
>
> -- and yes the error is on the DENSE_RANK line --
>
> thanks again for all help!

Too bad your Oracle installation doesn't have a version number. Unnumbered Oracle versions can't run SQL statements. My guess is that your version of Embarcadero doesn't recognize the function.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)