Subject | Re: [firebird-support] Subselect in SP |
---|---|
Author | Jorge Andrés Brugger |
Post date | 2007-04-24T11:07:31Z |
Thanks!
My query was taken from a view, which doesnt have any "where". I just
copy it without thinking too much :)
By the way, is there any plans to support subselects in SP's?
Regards!
Svein Erling Tysvaer escribió:
Jorge Andrés Brugger
Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446-4444 int. 103
Correo electrónico: jbrugger@...
Website: www.dasu.com.ar
[Non-text portions of this message have been removed]
My query was taken from a view, which doesnt have any "where". I just
copy it without thinking too much :)
By the way, is there any plans to support subselects in SP's?
Regards!
Svein Erling Tysvaer escribió:
> Simple Jorge,--
>
> select min(FACTURAS.FECHA)
> FROM ITEMS_FACTURAS
> JOIN FACTURAS ON ITEMS_FACTURAS.ID_FACTURA = FACTURAS.ID_FACTURA
> AND ITEMS_FACTURAS.SUCURSAL = FACTURAS.SUCURSAL
> WHERE FACTURAS.SUCURSAL=1 AND ITEMS_FACTURAS.CODIGO_PRODUCTO=31344
>
> No need to do simple things the complex way!
> Set
>
> Jorge Andrés Brugger wrote:
>
>> Hello all.
>>
>> I have this sentence:
>>
>> select first 1 fecha from (
>> SELECT FACTURAS.SUCURSAL, ITEMS_FACTURAS.CODIGO_PRODUCTO, FACTURAS.FECHA
>> FROM ITEMS_FACTURAS INNER JOIN FACTURAS ON
>> (ITEMS_FACTURAS.ID_FACTURA = FACTURAS.ID_FACTURA) AND
>> (ITEMS_FACTURAS.SUCURSAL = FACTURAS.SUCURSAL)
>> GROUP BY FACTURAS.SUCURSAL, ITEMS_FACTURAS.CODIGO_PRODUCTO,
>> FACTURAS.FECHA
>> HAVING FACTURAS.SUCURSAL=1 AND ITEMS_FACTURAS.CODIGO_PRODUCTO=31344)
>> order by fecha
>>
>> It works ok in isql. But, I cannot use it inside of an Stored Procedure
>> (select ... from (select) not supported).
>>
>> Firebird doesnt support neither something like:
>>
>> SELECT FACTURAS.SUCURSAL, ITEMS_FACTURAS.CODIGO_PRODUCTO, First 1
>> FACTURAS.FECHA
>> FROM ITEMS_FACTURAS INNER JOIN FACTURAS ON (ITEMS_FACTURAS.ID_FACTURA =
>> FACTURAS.ID_FACTURA) AND (ITEMS_FACTURAS.SUCURSAL = FACTURAS.SUCURSAL)
>> GROUP BY FACTURAS.SUCURSAL, ITEMS_FACTURAS.CODIGO_PRODUCTO
>> HAVING (((FACTURAS.SUCURSAL)=1) AND
>> ((ITEMS_FACTURAS.CODIGO_PRODUCTO)=31344))
>> ORDER BY FACTURAS.FECHA;
>>
>> How can I do that query from inside a Stored Procedure? (I need to know
>> the lowest "fecha" from a specific "sucursal" and "codigo_producto")
>>
>> Thanks!
>>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
Jorge Andrés Brugger
Informática
DASU - Obra Social del Personal de la Universidad Nacional de la Patagonia
Comodoro Rivadavia, Chubut, Argentina
Teléfono (0297) 446-4444 int. 103
Correo electrónico: jbrugger@...
Website: www.dasu.com.ar
[Non-text portions of this message have been removed]