🚀 go-pugleaf

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)