Board index » delphi » select syntax

select syntax


2006-02-10 07:18:37 PM
delphi159
Hello all,
I made one Procedure in interbase,
which returns number(18,2)
and if I do
select * from MY_PROC(26.2, 16);
it returns 1 row correctly.
but in ORACLE I used to do like this.
select c.ID, c.NAME, c.NUM, MY_PROC(c.NUMBER, c.PERCENT) from CUSTOMER c;
and for my big dissapointment, I found out that it doesn't work in
interbase.
do we have anything similar????
Best regards.
 
 

Re:select syntax

George Goglidze writes:
Quote
Hello all,

I made one Procedure in interbase,
which returns number(18,2)

and if I do
select * from MY_PROC(26.2, 16);
it returns 1 row correctly.

but in ORACLE I used to do like this.

select c.ID, c.NAME, c.NUM, MY_PROC(c.NUMBER, c.PERCENT) from CUSTOMER c;

and for my big dissapointment, I found out that it doesn't work in
interbase.
do we have anything similar????

Best regards.


select c.ID, c.NAME, c.NUM, (select MY_PROC(c.NUMBER, c.PERCENT) from
rdb$database) from CUSTOMER c;
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts, build
a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a computer,
cook a tasty meal, fight efficiently, die gallantly. Specialization is for
insects. (RAH)
 

Re:select syntax

Thanks for an answer.
here's the real code now:
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
from ARTICLES
and it doesn't work.
error:
Dynamic SQL Error SQL error code = -804 Function unknown P_CALC_TAX
Best Regards.
"Jeff Overcash (TeamB)" <XXXX@XXXXX.COM>writes
Quote
George Goglidze writes:
>Hello all,
>
>I made one Procedure in interbase,
>which returns number(18,2)
>
>and if I do
>select * from MY_PROC(26.2, 16);
>it returns 1 row correctly.
>
>but in ORACLE I used to do like this.
>
>select c.ID, c.NAME, c.NUM, MY_PROC(c.NUMBER, c.PERCENT) from CUSTOMER
c;
>
>and for my big dissapointment, I found out that it doesn't work in
>interbase.
>do we have anything similar????
>
>Best regards.
>
>

select c.ID, c.NAME, c.NUM, (select MY_PROC(c.NUMBER, c.PERCENT) from
rdb$database) from CUSTOMER c;

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts,
build
a wall, set a bone, comfort the dying, take orders, give orders,
cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a
computer,
cook a tasty meal, fight efficiently, die gallantly. Specialization is
for
insects. (RAH)
 

Re:select syntax

Hello George,
Quote
here's the real code now:
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
from ARTICLES

and it doesn't work.
error:
Dynamic SQL Error SQL error code = -804 Function unknown P_CALC_TAX
Change it to:
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select myoutputparametername P_CALC_TAX(PRICE_NO_TAX, TAX) from
rdb$database)
from ARTICLES
Obviously, replace "myoutputparametername" with whatever your
output parameter is called.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:select syntax

select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL P_CALC_TAX(PRICE_NO_TAX, TAX) from
rdb$database)
from ARTICLES
no result anyway.
as well error code has changed:
Dynamic SQL Error SQL error code = -104 Token unknown - line 3, char 14
P_CALC_TAX
although as I said this works :
select total from P_CALC_TAX(19.16, 15);
"Martijn Tonies" <XXXX@XXXXX.COM>writes
Quote
Hello George,

>here's the real code now:
>select ID, CODE as Codigo, NAME as Nombre,
>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
>from ARTICLES
>
>and it doesn't work.
>error:
>Dynamic SQL Error SQL error code = -804 Function unknown P_CALC_TAX

Change it to:

select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select myoutputparametername P_CALC_TAX(PRICE_NO_TAX, TAX) from
rdb$database)
from ARTICLES

Obviously, replace "myoutputparametername" with whatever your
output parameter is called.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com


 

Re:select syntax

George Goglidze writes:
Quote

select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
from ARTICLES

this doesn't work.
and returns error:
Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX

Try qualifying the parameters:
(select TOTAL from P_CALC_TAX(ARTICLES.PRICE_NO_TAX, ARTICLES.TAX))
(I suppose TAX also is column in ARTICLES)
--
Ilkka Hyvärinen
 

Re:select syntax

Quote
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
Sorry, my fault :-)
Quote
(select TOTAL P_CALC_TAX(PRICE_NO_TAX, TAX) from
rdb$database)
from ARTICLES
(select total from p_calc_tax(... )) -- and make sure it returns
only 1 row.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com
Quote
no result anyway.
as well error code has changed:
Dynamic SQL Error SQL error code = -104 Token unknown - line 3, char 14
P_CALC_TAX

although as I said this works :
select total from P_CALC_TAX(19.16, 15);



"Martijn Tonies" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>Hello George,
>
>>here's the real code now:
>>select ID, CODE as Codigo, NAME as Nombre,
>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>>(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
>>from ARTICLES
>>
>>and it doesn't work.
>>error:
>>Dynamic SQL Error SQL error code = -804 Function unknown P_CALC_TAX
>
>Change it to:
>
>select ID, CODE as Codigo, NAME as Nombre,
>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>(select myoutputparametername P_CALC_TAX(PRICE_NO_TAX, TAX) from
>rdb$database)
>from ARTICLES
>
>Obviously, replace "myoutputparametername" with whatever your
>output parameter is called.
>
>--
>Martijn Tonies
>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
>Server
>Upscene Productions
>www.upscene.com
>Database development questions? Check the forum!
>www.databasedevelopmentforum.com
>
>


 

Re:select syntax

Martijn Tonies writes:
Quote
>select ID, CODE as Codigo, NAME as Nombre,
>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,

Sorry, my fault :-)
Naw mine, you just followed my bad lead :).
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts, build
a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act
alone, solve equations, analyze a new problem, pitch manure, program a computer,
cook a tasty meal, fight efficiently, die gallantly. Specialization is for
insects. (RAH)
 

Re:select syntax

Thank you very much for help,
do appreciate it.
But I had tried that one, much before I posted this topic.
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
from ARTICLES
this doesn't work.
and returns error:
Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX
Regards
"Martijn Tonies" <XXXX@XXXXX.COM>writes
Quote
>select ID, CODE as Codigo, NAME as Nombre,
>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,

Sorry, my fault :-)

>(select TOTAL P_CALC_TAX(PRICE_NO_TAX, TAX) from
>rdb$database)
>from ARTICLES


(select total from p_calc_tax(... )) -- and make sure it returns
only 1 row.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
Database development questions? Check the forum!
www.databasedevelopmentforum.com


>no result anyway.
>as well error code has changed:
>Dynamic SQL Error SQL error code = -104 Token unknown - line 3, char 14
>P_CALC_TAX
>
>although as I said this works :
>select total from P_CALC_TAX(19.16, 15);
>
>
>
>"Martijn Tonies" <XXXX@XXXXX.COM>writes
>news:XXXX@XXXXX.COM...
>>Hello George,
>>
>>>here's the real code now:
>>>select ID, CODE as Codigo, NAME as Nombre,
>>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>>>(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
>>>from ARTICLES
>>>
>>>and it doesn't work.
>>>error:
>>>Dynamic SQL Error SQL error code = -804 Function unknown P_CALC_TAX
>>
>>Change it to:
>>
>>select ID, CODE as Codigo, NAME as Nombre,
>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>>(select myoutputparametername P_CALC_TAX(PRICE_NO_TAX, TAX) from
>>rdb$database)
>>from ARTICLES
>>
>>Obviously, replace "myoutputparametername" with whatever your
>>output parameter is called.
>>
>>--
>>Martijn Tonies
>>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
>>Server
>>Upscene Productions
>>www.upscene.com
>>Database development questions? Check the forum!
>>www.databasedevelopmentforum.com
>>
>>
>
>


 

Re:select syntax

well if put numbers myself it works, but I definately don't need this.
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(25.26, 16))
from ARTICLES
"George Goglidze" <XXXX@XXXXX.COM>writes
Quote
Thank you very much for help,
do appreciate it.

But I had tried that one, much before I posted this topic.

select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
from ARTICLES

this doesn't work.
and returns error:
Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX

Regards

"Martijn Tonies" <XXXX@XXXXX.COM>writes
news:43eca60e$XXXX@XXXXX.COM...
>>select ID, CODE as Codigo, NAME as Nombre,
>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>
>Sorry, my fault :-)
>
>>(select TOTAL P_CALC_TAX(PRICE_NO_TAX, TAX) from
>>rdb$database)
>>from ARTICLES
>
>
>(select total from p_calc_tax(... )) -- and make sure it returns
>only 1 row.
>
>
>--
>Martijn Tonies
>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS
SQL
>Server
>Upscene Productions
>www.upscene.com
>Database development questions? Check the forum!
>www.databasedevelopmentforum.com
>
>
>>no result anyway.
>>as well error code has changed:
>>Dynamic SQL Error SQL error code = -104 Token unknown - line 3, char
14
>>P_CALC_TAX
>>
>>although as I said this works :
>>select total from P_CALC_TAX(19.16, 15);
>>
>>
>>
>>"Martijn Tonies" <XXXX@XXXXX.COM>writes
>>news:XXXX@XXXXX.COM...
>>>Hello George,
>>>
>>>>here's the real code now:
>>>>select ID, CODE as Codigo, NAME as Nombre,
>>>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>>>>(select P_CALC_TAX(PRICE_NO_TAX, TAX) from rdb$database)
>>>>from ARTICLES
>>>>
>>>>and it doesn't work.
>>>>error:
>>>>Dynamic SQL Error SQL error code = -804 Function unknown
P_CALC_TAX
>>>
>>>Change it to:
>>>
>>>select ID, CODE as Codigo, NAME as Nombre,
>>>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>>>(select myoutputparametername P_CALC_TAX(PRICE_NO_TAX, TAX) from
>>>rdb$database)
>>>from ARTICLES
>>>
>>>Obviously, replace "myoutputparametername" with whatever your
>>>output parameter is called.
>>>
>>>--
>>>Martijn Tonies
>>>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle &
MS
>SQL
>>>Server
>>>Upscene Productions
>>>www.upscene.com
>>>Database development questions? Check the forum!
>>>www.databasedevelopmentforum.com
>>>
>>>
>>
>>
>
>


 

Re:select syntax

Quote
But I had tried that one, much before I posted this topic.

select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
from ARTICLES

this doesn't work.
and returns error:
Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX
PRICE_NO_TAX is a column in ARTICLES, correct?
Can you do this select without the stored procedure thingy?
--
Martijn Tonies
Database Workbench - development tool for InterBase, Oracle and more!
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Re:select syntax

1)
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio
from ARTICLES
WORKS FINE.
2)
select TOTAL from P_CALC_TAX(15, 2);
WORKS FINE TOO.
3)
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
from ARTICLES
DOESN'T WORK.
error:
Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX
4)
select a.ID, a.CODE as Codigo, a.NAME as Nombre,
a.EXISTANCE as Existancia, a.PRICE_NO_TAX as Precio,
p.TOTAL
from ARTICLES a, P_CALC_TAX(a.PRICE_NO_TAX, a.TAX) p
DOESN'T WORK
error:
no current record for fetch operation
but woow!!! :(((
i'm just being stupid!!!
I was just writing this post, and testing again and again.
and now I am working fine with this!!!
select ID, CODE as Codigo, NAME as Nombre,
EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
(select TOTAL from P_CALC_TAX(a.PRICE_NO_TAX, a.TAX))
from ARTICLES a
MANY MANY MANY thanks to everyone who tried to help me!!!
appreciate your help a lot.
"Martijn Tonies" <XXXX@XXXXX.COM>writes
Quote
>But I had tried that one, much before I posted this topic.
>
>select ID, CODE as Codigo, NAME as Nombre,
>EXISTANCE as Existancia, PRICE_NO_TAX as Precio,
>(select TOTAL from P_CALC_TAX(PRICE_NO_TAX, TAX))
>from ARTICLES
>
>this doesn't work.
>and returns error:
>Dynamic SQL Error SQL error code = -206 Column unknown PRICE_NO_TAX

PRICE_NO_TAX is a column in ARTICLES, correct?

Can you do this select without the stored procedure thingy?



--
Martijn Tonies
Database Workbench - development tool for InterBase, Oracle and more!
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com