Actian/Pervasive SQL and Date Variables

Question

How are date variables declared and used in Actian Zen/Pervasive ?

How do i fix this

    CREATE proc test1 ()
returns(TranDate integer, GLAcntNumber integer , Net decimal(39,19))
as BEGIN 
    
DECLARE :StartDate DATE ;
SELECT CAST('2021-01-01' AS DATE) INTO :StartDate ;
SELECT :StartDate , GLAcntNumber, SUM(Net) AS Amt 
FROM vGLBalances 
WHERE period < 1
GROUP BY :StartDate, GLAcntNumber;

END

Answer 1

I take that back. I do see something in your query. You are creating a DATE but are trying to pass it back as an INTEGER. Change your query to:

CREATE procedure test1 ()
returns(TranDate date, GLAcntNumber integer , Net decimal(39,19))
as BEGIN 
    
DECLARE :StartDate DATE ;
SELECT CAST('2021-01-01' AS DATE) INTO :StartDate ;
SELECT :StartDate, GLAcntNumber, SUM(Net) AS Amt 
FROM vGLBalances 
WHERE period < 1
GROUP BY :StartDate, GLAcntNumber;

END

It should work. If you really want an Integer instead of a date, you'll need to change the query from using a date.