declare variables -- please help

Toad Data Point
1

Hello advanced users!

I am having a problem with simple declaring variables. Here is my struggle. Am I using correct syntax? What does the ‘cursor’ error mean?

DECLARE
DT01 DATE :=‘2010-07-01’;
DT01 DATE :=‘2010-07-10’
SELECT
ORDERS.CUSTOMER_NO,
ORDERS.ORDER_DT
FROM RECORDS.ADMIN.ORDERS
WHERE ORDERS.ORDER_DT BETWEEN DT01 AND DT02

error ^ found “DATE” (at char 15) expecting `CURSOR’

Thank you,

2

I assume this is in Oracle. If so you could do it this way.

DECLARE
date1 VARCHAR(19) := ‘2010-01-02 00:00:00’;
date2 VARCHAR(19) := ‘2010-01-08 00:00:00’;
howmany NUMBER := 0;

BEGIN

SELECT count(ORDERS.ORDER_DATE)
into howmany
FROM QUEST_STAGE.ORDERS ORDERS
WHERE (ORDERS.ORDER_DATE BETWEEN TO_DATE (date1,
‘yyyy/mm/dd hh24:mi:ss’)
AND TO_DATE (date2,
‘yyyy/mm/dd hh24:mi:ss’));
dbms_output.put_line('rows = ’ || howmany);

END;

3

Error:8/24/2010 1:43:03 PM 0:00:00.640: ERROR: 'DECLARE

date1 VARCHAR(19) := ‘2010-01-02 00:00:00’’

error ^ found “VARCHAR” (at char 16) expecting `CURSOR’

1: DECLARE

date1 VARCHAR(19) := '2010-01-02 00:00:00’DECLARE

date1 VARCHAR(19) := '2010-01-02 00:00:00

Debbie,

Thank you very much for sharing your knowledge. Can you, please, take a look at the error message I am getting after applying your code? I appreciate your help. What is Cursor?

==================================================

4

DECLARE

date1 VARCHAR(19) := ‘2010-01-02 00:00:00’;

date2 VARCHAR(19) := ‘2010-01-08 00:00:00’;

howmany NUMBER := 0;

CURSOR c1 IS SELECT count(ORDERS.ORDER_DATE)

FROM QUEST_STAGE.ORDERS ORDERS

WHERE (ORDERS.ORDER_DATE BETWEEN TO_DATE (date1,

‘yyyy/mm/dd hh24:mi:ss’)

AND TO_DATE (date2,

‘yyyy/mm/dd hh24:mi:ss’));

BEGIN

OPEN c1;

FETCH c1 INTO howmany;

CLOSE c1;

END;

Cursors are used when you have multiple rows returned and need to loop through the set. You can convert the block of code I sent earlier to use a cursor. See below. But you should not need to. Can you send a full screenshot of your code and connection. Perhaps I can see from the screenshot what is occuring.

dbms_output.put_line('rows = ’ || howmany);

5

Debbie,

I think I just found my answer. I am uzing Netezza. Answer was at Netezza users forum. Thank you very much.

Message was edited by: toad_novice