| Subject | Re: [firebird-support] Problem using 2 parameters in SP in order by |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-12-07T09:19:11Z |
At 09:09 AM 7/12/2004 +0100, you wrote:
engine has encountered an illegal use of the word "ASC" somewhere in the
query statement. Now, since this is an IBO error dialog, the actual error
could be coming from an illegal OrderingItem somewhere in the statement's
setup....
anything.
1. The name of a column in the underlying set, which may or may not be in
the output set
2. An expression involving one or more columns in the underlying set,
which may or may not be in the output set
3. A number representing the output position of an expression or column in
the output set specified in the SELECT list.
Even if it were valid to use a stored procedure call like a function, you
can't specify a constant, such as a timestamp value, as an ordering
criterion. A timestamp column, yes. A timestamp expression, yes.
It would really be simpler if you would describe what you want to
achieve. Describe how the records in RESERVATION are related to the
records in the main table of the query (which, so far, you have not
shown). At a guess, I think that what you want is a join between the main
table and RESERVATION, and to order the output set by RESERVATION.TIMESTART.
./heLen
>Hello,I would take a lot of convincing! :-)
>
>I'm trying to use a stored procedure in a order by clause.
>I thought I was doing OK, I am not very fluent in SP's, until I needed a
>second parameter.
>
>When I do something like this (I created some simple example to reproduce):
>
>order by (select OUT_TIMESTART from
>RESERVATION_START(reservation.RESERVATIONID))
>
>it works OK.
>When I change the stored procedure so that it needs 2 parameter (I don'tI don't believe this, either. This error is being thrown because the
>even use the second parameter yet) I get an error
>
>order by (select OUT_TIMESTART from
>RESERVATION_START(reservation.RESERVATIONID,1))
>
>--------
>ISC ERROR CODE:335544569
>
>ISC ERROR MESSAGE:
>Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 2, char 81
>ASC
engine has encountered an illegal use of the word "ASC" somewhere in the
query statement. Now, since this is an IBO error dialog, the actual error
could be coming from an illegal OrderingItem somewhere in the statement's
setup....
>Statement: select * from reservationSince you don't show to *what* you ascribe "works OK", this doesn't tell us
>order by (select OUT_TIMESTART from
>RESERVATION_START(reservation.RESERVATIONID,1))
>--------
>
>I tried the using the same SP in the select or where clause. There it works
>OK.
anything.
>To reproduce the problem I've added some sql.The criteria for an order by clause can be any of the following:
>
>Could somebody please tell me what is going on?
1. The name of a column in the underlying set, which may or may not be in
the output set
2. An expression involving one or more columns in the underlying set,
which may or may not be in the output set
3. A number representing the output position of an expression or column in
the output set specified in the SELECT list.
Even if it were valid to use a stored procedure call like a function, you
can't specify a constant, such as a timestamp value, as an ordering
criterion. A timestamp column, yes. A timestamp expression, yes.
It would really be simpler if you would describe what you want to
achieve. Describe how the records in RESERVATION are related to the
records in the main table of the query (which, so far, you have not
shown). At a guess, I think that what you want is a join between the main
table and RESERVATION, and to order the output set by RESERVATION.TIMESTART.
./heLen