0

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
Habib
  • 70
  • 7

1 Answers1

0

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.

mirtheil
  • 8,952
  • 1
  • 30
  • 29
  • Sorry, that was a mistake - i did have it as date and i tried the integer then changed it back (incompletely) It returns an error every time Error occurred during SQL query execution Reason: SQL Error [37000]: [LNA][PSQL][SQL Engine]Syntax Error: CREATE proc<< ??? >> test1 () – Habib Jun 12 '21 at 01:23
  • I figured it out. use CREATE PROCEDURE (no shortcuts) – Habib Jun 12 '21 at 01:29