| Author |
 Topic  |
|
|
1love
Starting Member
3 Posts |
 Posted - 01/03/2013 : 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
India
47069 Posts |
Posted - 01/03/2013 : 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/
|
Edited by - visakh16 on 01/03/2013 01:26:01 |
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/03/2013 : 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 - 01/03/2013 : 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 ? |
Edited by - 1love on 01/03/2013 09:49:13 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/09/2013 : 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 |
Edited by - Jeff Moden on 01/09/2013 19:28:06 |
|
|
| |
Topic |
|
|
|
Syntax error on when case, declare and set
