Stored proc.

Thread View: borland.public.interbase.sql

6 messages

[T] Back to Threads [V] Tree View [G] Group Articles

6 total messages Started by Henry Olive Tue, 26 Nov 2002 18:45

#11291

Author: Henry Olive
Date: Tue, 26 Nov 2002 18:45

39 lines
1853 bytes

IB6, D6 , SqlQuery

I have 2 comboboxes on a form , for filtering a report.
but i have heard (here) that filter is slower than SQL.
so i decided to use a   s.proc.
Could you please add necessary codes in to below s.p. ?

Combobox1's items = A , B      ,  Combobox2's items = C , D
how can i control these.?

if combobox.text='A' then
C.PROCESS='AAA';
if combobox.text='B' then
C.PROCESS='BBB';

if combobox2.text='C' then
C.PSTATE:=Null
if combobox2.text='D' then
C.PSTATE:=Not Null

CREATE PROCEDURE CHECKRPT (
  RETURNS (
  CUSTNO INTEGER,
  TOTAL DOUBLE PRECISION,
  DUEDATE  TIMESTAMP,
  PSTATE VARCHAR(8),
  CUSTNAME VARCHAR(40)
) AS     BEGIN
FOR SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
C.DUEDATE,  C.PSTATE,  D.CUSTNAME
FROM CHECK C , CUSTOMERS D
WHERE (C.CUSTNO=D.CUSTNO)
ORDER BY C.DUEDATE,  C.CUSTNO
INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
DO
SUSPEND;
END

Thank You.

Re: Stored proc.

#11242

Author: Henry Olive
Date: Thu, 28 Nov 2002 19:41

32 lines
1734 bytes

I think i couldn't explain my problem.
Try to explain again.
How can i tell to a  s.proc that
if a variable=0 then A_table_field is null ? ( blank )
if a variable=1 then A_table_field is not null ? ( not blank )

Please look at  between  BEGIN  and FOR in below SP.

CREATE PROCEDURE CHECKRPT (  PAYM SMALLINT )
  RETURNS (
  CUSTNO INTEGER,
  TOTAL DOUBLE PRECISION,
  DUEDATE  TIMESTAMP,
  PSTATE VARCHAR(8),
  CUSTNAME VARCHAR(40)
) AS    BEGIN


    IF PAYM=0 THEN  C.PAYMENT=NULL
    IF PAYM=1 THEN  C.PAYMENT <> NULL


FOR
SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
C.DUEDATE,  C.PSTATE,  D.CUSTNAME
FROM CHECK C , CUSTOMERS D
WHERE (C.CUSTNO=D.CUSTNO)  AND  ( C.PAYMENT=:PAYM )
ORDER BY C.DUEDATE,  C.CUSTNO
INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
DO
SUSPEND;
END

Re: Stored proc.

#11243

Author: Bill Todd
Date: Thu, 28 Nov 2002 21:50

78 lines
2824 bytes

I think what you want is the following.

CREATE PROCEDURE CHECKRPT (  PAYM SMALLINT )
  RETURNS (
  CUSTNO INTEGER,
  TOTAL DOUBLE PRECISION,
  DUEDATE  TIMESTAMP,
  PSTATE VARCHAR(8),
  CUSTNAME VARCHAR(40)
) AS    BEGIN


IF (PAYM = 0 THEN)
  FOR
  SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
  C.DUEDATE,  C.PSTATE,  D.CUSTNAME
  FROM CHECK C , CUSTOMERS D  
  WHERE (C.CUSTNO=D.CUSTNO)  AND  ( C.PAYMENT IS NULL )
  ORDER BY C.DUEDATE,  C.CUSTNO
  INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
  DO
  SUSPEND;
ELSE
  FOR
  SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
  C.DUEDATE,  C.PSTATE,  D.CUSTNAME
  FROM CHECK C , CUSTOMERS D  
  WHERE (C.CUSTNO=D.CUSTNO)  AND  ( C.PAYMENT IS NOT NULL )
  ORDER BY C.DUEDATE,  C.CUSTNO
  INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
  DO
  SUSPEND;
END



On Thu, 28 Nov 2002 21:41:15 +0200, "Henry Olive" <zed@zedltd.com>
wrote:

>I think i couldn't explain my problem.
>Try to explain again.
>How can i tell to a  s.proc that
>if a variable=0 then A_table_field is null ? ( blank )
>if a variable=1 then A_table_field is not null ? ( not blank )
>
>Please look at  between  BEGIN  and FOR in below SP.
>
>CREATE PROCEDURE CHECKRPT (  PAYM SMALLINT )
>  RETURNS (
>  CUSTNO INTEGER,
>  TOTAL DOUBLE PRECISION,
>  DUEDATE  TIMESTAMP,
>  PSTATE VARCHAR(8),
>  CUSTNAME VARCHAR(40)
>) AS    BEGIN
>
>
>    IF PAYM=0 THEN  C.PAYMENT=NULL
>    IF PAYM=1 THEN  C.PAYMENT <> NULL
>
>
>FOR
>SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
>C.DUEDATE,  C.PSTATE,  D.CUSTNAME
>FROM CHECK C , CUSTOMERS D
>WHERE (C.CUSTNO=D.CUSTNO)  AND  ( C.PAYMENT=:PAYM )
>ORDER BY C.DUEDATE,  C.CUSTNO
>INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
>DO
>SUSPEND;
>END
>
>


--
Bill (TeamB)
(TeamB cannot respond to questions received via email)

Re: Stored proc.

#11244

Author: Henry Olive
Date: Thu, 28 Nov 2002 22:27

2 lines
1082 bytes

What a nice people you are
Thank you SO VERY MUCH.

Re: Stored proc.

#11251

Author: Rafael Vacas
Date: Fri, 29 Nov 2002 11:43

19 lines
1350 bytes

Hi Henry,

Try this and let me know ...

FOR
SELECT DISTINCT  C.CUSTNO,  C.TOTAL,
C.DUEDATE,  C.PSTATE,  D.CUSTNAME
FROM CHECK C , CUSTOMERS D
WHERE (C.CUSTNO=D.CUSTNO)
and ( (:paym=0 and c.payment is null) or (:paym=1 and c.payment is not
null) )
ORDER BY C.DUEDATE,  C.CUSTNO
INTO :CUSTNO, :TOTAL, :DUEDATE, :PSTATE, :CUSTNAME
DO
SUSPEND;
END

Rafael
A Coru�a

Re: Stored proc.

#11285

Author: Henry Olive
Date: Mon, 02 Dec 2002 19:35

2 lines
1034 bytes

Thank you Rafael,
Your code is OK. &  it works very well

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads