Syntax error on when case, declare and set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Syntax error on when case, declare and set

Author

Topic

1love
Starting Member

3 Posts

Posted - 2013-01-03 : 01:24:23

Hi, here is a code
CREATE FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
RETURNS numeric(10,2)
BEGIN
DECLARE suma numeric(10,2);
DECLARE IDZamowienieTemp integer;
DECLARE okresTMR date;

CASE okres
WHEN 'D' THEN
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
WHEN 'T' THEN

SET okresTMR = DATEADD(week,-1,GETDATE())
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
WHEN 'M' THEN

SET okresTMR = DATEADD(month,-1,GETDATE())
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
WHEN 'R' THEN

SET okresTMR = DATEADD(year,-1,GETDATE())
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())))
END CASE;

SET suma = 0;
OPEN kursor;
petla : LOOP
FETCH NEXT kursor INTO IDZamowienieTemp;
IF SQLCODE <> 0 THEN LEAVE petla END IF;
SET suma = suma + WartoscZamowienia(IDZamowienieTemp);
END LOOP;
CLOSE kursor;

RETURN suma;
END

Sybase points error on first DECLARE (after WHEN 'D' THEN) but i dont have idea why. I was checking almost everything. What is more,i was trying to do this in other way, to start like this:
CREATE FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
RETURNS numeric(10,2)
BEGIN
DECLARE suma numeric(10,2);
DECLARE IDZamowienieTemp integer;
DECLARE okresTMR date;
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
SET okresTMR = DATEADD(week,-1,GETDATE());
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));
SET okresTMR = DATEADD(month,-1,GETDATE());
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));
SET okresTMR = DATEADD(year,-1,GETDATE());
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN okresTMR AND Convert(datetime, Convert(int, GetDate())));

but again i got error or on first DECLARE or on first SET....

Can anyone help me?

Greetings, 1love

PS: Sorry for my english, it isnt my native language

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-03 : 01:25:36

this is a MS SQL Server forum. so you may be better off posting at sybase forums like www.dbforums.com to get sybase related syntax help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-03 : 04:04:39

A case statement gives a conditional expression for the value returned. It is not a control of flow statement.
You probably want an if there instead of a case.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1love
Starting Member

3 Posts

Posted - 2013-01-03 : 09:34:24

Thx for advices, i corrected a code however there are still syntax error there:

ALTER FUNCTION "DBA"."PrzychodyDTMR"(IN okres character(1))
RETURNS numeric(10,2)
BEGIN
DECLARE @suma numeric(10,2);
DECLARE @IDZamowienieTemp integer;

IF okres='D' THEN
BEGIN
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA=Convert(datetime, Convert(int, GetDate())));
END
ELSE IF okres='T' THEN
BEGIN
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(week,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
END
ELSE IF okres='M' THEN
BEGIN
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(month,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
END
ELSE IF okres='R' THEN
BEGIN
DECLARE kursor CURSOR FOR (SELECT Z.IDzamowienie
FROM ZAMOWIENIE Z
WHERE Z.DATA_ZAMOWIENIA BETWEEN Convert(datetime, Convert(int, DATEADD(year,-1,GETDATE()))) AND Convert(datetime, Convert(int, GetDate())));
END

SELECT @suma = 0;
OPEN kursor;
petla : LOOP
FETCH NEXT kursor INTO @IDZamowienieTemp;
IF SQLCODE <> 0 THEN LEAVE petla END IF;
SELECT @suma = @suma + WartoscZamowienia(IDZamowienieTemp);
END LOOP;
CLOSE kursor;

RETURN @suma;
END

Now Sybase shows error: item kursor already exists. Any ideas?

BTW
Some one told me to use SELECT @variable = .....
instead of SET @variable = .....
is it correct ?

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-09 : 18:05:33

Yes, the problem is that you cannot conditionally declare variables... not even cursor variables. You will get a similar error if you run the following simple example.

IF 1=1
BEGIN 
DECLARE @Variable VARCHAR(10)
END
ELSE
BEGIN
DECLARE @Variable VARCHAR(20)
END

You're also letting yourself in for a world of hurt performance wise. First, you're using a scalar function which, on a good day, is about 7 times slower than an equivaqlent inline function.Second, you're using a Cursor and that normally means RBAR operations that are going to be dreadfully slow and, perhaps, quite resource intensive.

I'm not sure because I certainly can't see your data from here but it seems like this could all be written as a single multi-case query without any loops, cursors, or other forms of RBAR. We should probably concentrate on that instead of trying to write conditional cursor instantiations in a scalar function.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources