Important Announcement

This forum will be discontinued.

The creation of new topics is disabled.

Please open GitHub issues for the corresponding topics/products in the following Trivadis repositories:

A lot of users have already a GitHub account and the management of the issues is better than in this forum. For example, closing a forum topic is never related to a product change. Closing a GitHub issue for an accepted bug means that the bug is fixed. This should simplify the work for all involved parties.

See this blog post for more information.

Thank you for your understanding.

Notifications
Clear all

[Solved] Offset/Fetch giving errors

7 Posts
2 Users
0 Reactions
1,799 Views
0
Topic starter

Hello,

When using OFFSET/FETCH I am getting following error:

4-Blocker: no viable alternative at input '('

This is not supported by PL/SQL Cop ? If no, is there any workaround ?

5 Answers
0

Yes, you're right. That's another parser bug. I've opened a ticket (PLSQLCOP-269). Parse error when using expressions in offset/rowcount in row_limiting_clause.

The first issue is caused by dealing with keyword offset. It is allowed as table alias and that leads to a conflict. 

Right now the best what you can do is using simple variables or constants. This should work.

0

Can you please provide a full example? Thx

This post was modified 5 years ago by Philipp Salvisberg
0
Topic starter

A simple example where I get the error:

SELECT naam
FROM dl.patient
OFFSET 10 ROWS
FETCH NEXT 3 ROWS ONLY;

0

Hmm, this looks like a parser bug. Thanks for reporting this. I'll open a ticket.

However, It usually does not make sense to use the row_limiting_clause without ordering the result. And since you asked for a workaround...

SELECT naam
FROM dl.patient
ORDER BY naam
OFFSET 10 ROWS
FETCH NEXT 3 ROWS ONLY;

Using a subquery works as well: 

SELECT * FROM (SELECT naam FROM dl.patient) a
OFFSET 10 ROWS
FETCH NEXT 3 ROWS ONLY;

 

This post was modified 5 years ago 2 times by Philipp Salvisberg
Philipp Salvisberg 19/12/2019 11:47 am

opened ticket PLSQLCOP-268

Philipp Salvisberg 19/12/2019 11:53 am

using an alias works as well. In fact it is required also for the subquery variant. updated post.

0
Topic starter

When using expressions in the offset/fetch clause it also seems to go wrong (although it is ordered).

 e.g. the following query:

SELECT /*+ first_rows(25) */

                 api.patient_ot (patient_nr => p.patient_id

                                ,afdelings_code => p.afdeling_id

                                ,naam => p.naam

                                ,voornaam => CASE l_tonenvoornaam WHEN 1 THEN p.voornaam ELSE null END

                                ,geslacht => p.geslacht

                                ,geboortedatum => p.geboorte_dt

                                ,email => p.e_mail

                                ,rijksregister_nr => p.rijksregister_nr

                                ,gsm_nr => p.gsm_nr

                                ,adressen => NULL)

                ,COUNT (1) OVER ()

        BULK COLLECT INTO l_patientlist.items, l_rowcount

        FROM dl.patient p WHERE 1=1

        AND (p_naam is null OR p.naam LIKE p_naam)

        AND (p_voornaam is null OR p.voornaam LIKE p_voornaam)

        AND (p_patient_nr is null OR p.patient_id = p_patient_nr)

        AND (p_rijksregister_nr is null OR p.rijksregister_nr = p_rijksregister_nr)

        AND (p_geboortedatum is null OR p.geboorte_dt = TO_DATE(p_geboortedatum))

        AND (p_afdelings_code is null OR p.afdeling_id = p_afdelings_code)

        ORDER BY p.voornaam, p.naam

        OFFSET (l_patientlist.page - 1) * l_patientlist.limit ROWS

        FETCH NEXT l_patientlist.limit ROWS ONLY;

When putting the expressions in a variable it works fine.