SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Syntax error on when case, declare and set
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

1love
Starting Member

3 Posts

Posted - 01/03/2013 :  01:24:23  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/03/2013 :  01:25:36  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 01/03/2013 01:26:01
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/03/2013 :  04:04:39  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

1love
Starting Member

3 Posts

Posted - 01/03/2013 :  09:34:24  Show Profile  Reply with Quote
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 ?

Edited by - 1love on 01/03/2013 09:49:13
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/09/2013 :  18:05:33  Show Profile  Reply with Quote
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

Edited by - Jeff Moden on 01/09/2013 19:28:06
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000