OK Alam,

But the problem is the follow: I'm working in a module that export the
registers from the database to ascii files. This module works in Oracle
today. This solution is for optional parameters where the user can fill
or not it.
The SQL that will recover the registers from the database is in a file,
than I open this file on my module, get the sql instruction and recover
the parameters than I show a grid with preformated fields to the user
fill the parameters.
Then the stored procedures is not the ideal solution because I've the
leave free the tables that I'll use in the sql instruction.
Some customers mine have the necessity to export data to predefined
layouts that are used to do the integration with another softwares and
with this module I'll be able to create the ascii file layouts without
modify my software.

Because this I need to create a form to have optional parameters on the
query... but the parameter must have on the query, because my system
don't know how query it will have to run... I can have any query on the
text file.

Did you understand my question?

Regards

Fabrício F. Kammer

-----Mensagem original-----
De: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Em nome de Adam
Enviada em: sexta-feira, 6 de janeiro de 2006 08:52
Para: firebird-support@yahoogroups.com
Assunto: Re: RES: RES: [firebird-support] Null and parameters


--- In firebird-support@yahoogroups.com, Fabrício Fadel Kammer
<ffkammer@c...> wrote:

>
> Thanks Adam,
>
> But do you understand what I want?
>
> I need to recover all the customers of the "CUSTOMERS" table if the
> pCode parameter is NULL and I need to recorver just one customer that
> have the CUSTOMER.CODIGO (primary key of the customer table) equal to
> the pCode parameter if it was informed.
>
> Are there another form to do this?
>

Well normally you dont include the where clause in the query from the
client if you want all records. But you can do it using a stored
procedure.

set term ^ ;

create procedure getcustomers
(
pCode integer
)
returns
(
Codigo Integer,
NOME varchar(100)
)
as
declare variable stmt varchar(200)
begin
stmt = 'SELECT c.Codigo, c.Nome FROM CUSTOMERS C';

if (:pCode is not null) then
begin
stmt = stmt || ' WHERE C.CODIGO = ' || :pcode;
end

for execute statement stmt
into :Codigo, :Nome do
begin
suspend;
end

end
^

Then you can use

select * from getcustomers(null)

or

select * from getcustomers(1)

The stored procedure internally adjusts the query and runs it.

Do you understand how parameters differ from some block of text that
is replaced?

Adam

> Thanks again
>
> Fabrício F. Kammer
>
> -----Mensagem original-----
> De: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Em nome de Adam
> Enviada em: quinta-feira, 5 de janeiro de 2006 20:31
> Para: firebird-support@yahoogroups.com
> Assunto: Re: RES: [firebird-support] Null and parameters
>
>
> You can emulate what you want using the execute statement syntax in
> any PSQL (trigger or stored procedure) block, however it is mostly
> unnecessary.
>
> Parameters are pseudo-values, not variables. You can generate the
> ideal plan for a query using its parameters, but there is no way to
> determine the query plan for
>
> SELECT C.NOME FROM CUSTOMERS AS C WHERE :pCode IS NULL OR C.CODIGO =
> :pCode;
>
> In this case, you are trying to use pCode as a field name, sort of
> like WHERE ID IS NULL. If ID is indexed, then the index could be used
> to assist the query.
>
> A Null parameter is not the problem here.
>
> SELECT C.NOME FROM CUSTOMERS AS C WHERE C.CODIGO = :pCode;
>
> Will work fine if :pCode is sent through as null. There may be a case
> for an enhancement request to allow a form of find/replace variables,
> but it is not available now.
>
> Adam
>
>
>
>
>
>
>
> --- In firebird-support@yahoogroups.com, Fabrício Fadel Kammer
> <ffkammer@c...> wrote:
> >
> > Thanks Jason,
> >
> > It's I can do this in oracle and I couldn't doing on firebird...
> >
> > Regards
> >
> > Fabrício
> >
> > -----Mensagem original-----
> > De: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] Em nome de Jason Dodson
> > Enviada em: quinta-feira, 5 de janeiro de 2006 13:44
> > Para: firebird-support@yahoogroups.com
> > Assunto: Re: [firebird-support] Null and parameters
> >
> >
> > You cant use a parameter in place of a field name. Its not quite
> > find-and-replace.
> >
> > Jason
> >
> > Fabrício Fadel Kammer wrote:
> > > Hi all,
> > >
> > > I'm with another doubt here!
> > >
> > > Can't I use the NULL for parameters comparison like bellow?
> > >
> > > SELECT C.NOME FROM CUSTOMERS AS C WHERE :pCode IS NULL OR C.CODIGO

=

> > > :pCode;
> > >
> > > In this situation I want to select all customers if the pCode
> > parameter
> > > is NULL or just one customer if the :pCode parameter was informed.
> > >
> > > Thanks again,
> > >
> > > Fabrício F. Kammer
> > > Conchal/SP Brazil
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > >
> > >
> > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >
> > > Visit http://firebird.sourceforge.net 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> > --
> > The information transmitted herewith is sensitive information

intended

> > only for use to the individual or entity to which it is addressed.

If

> > the reader of this message is not the intended recipient, you are
> hereby
> > notified that any review, retransmission, dissemination,

distribution,

> > copying or other use of, or taking of any action in reliance upon,
> this
> > information is strictly prohibited. If you have received this
> > communication in error, please contact the sender and delete the
> > material from your computer.
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://firebird.sourceforge.net 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
> >
> >
> >
> > * Visit your group "firebird-support
> > <http://groups.yahoo.com/group/firebird-support> " on the web.
> >
> >
> > * To unsubscribe from this group, send an email to:
> > firebird-support-unsubscribe@yahoogroups.com
> >
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

> > >
> >
> >
> > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> > Service <http://docs.yahoo.com/info/terms/> .
> >
> >
> > _____
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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
>
>
>
> * Visit your group "firebird-support
> <http://groups.yahoo.com/group/firebird-support> " on the web.
>
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

> >
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/> .
>
>
> _____
>
>
>
>
> [Non-text portions of this message have been removed]
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++





SPONSORED LINKS
Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+supp
ort&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w
4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Micro
soft+technical+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support
Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Techn
ical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+
support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support
&w6=Microsoft+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA>
technical support Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w
1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+tec
hnical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+
support&w6=Microsoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjE
qA6g> computer technical support
Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technic
al+support&w2=Computer+technical+support&w3=Compaq+computer+technical+su
pport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w
6=Microsoft+technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w>
technical support Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w
1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+tec
hnical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+
support&w6=Microsoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfm
vLcA> packard technical support Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Tech
nical+support&w2=Computer+technical+support&w3=Compaq+computer+technical
+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+suppor
t&w6=Microsoft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>
technical support

_____

YAHOO! GROUPS LINKS



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

>

* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service <http://docs.yahoo.com/info/terms/> .


_____




[Non-text portions of this message have been removed]