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,
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;
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?
==================================================
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);
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