anyone know what is wrong with this SQL statement?

[patty1]

Am creating stored procedure and am getting error "incorrect syntex near "then"

if (datepart(month,(CurrentDate))between 1.00 and 3.00 then datepart(month,(Transaction_History.Post_Date)) between 10.00 and 12.00 else...

thanks.

 

[rosemaryl]

Dear Patty1,

As far as I know, SQL doesn't have a currentdate function, you should use GetDate() and the syntax is as follows:

This would be correctly written as

If datepart(Month,getdate()) between 1 and 3
Begin
Select "Post_Date"
From Transaction_History
Where datepart(month, "Post_Date&quot between 10 and 12
Else
Next If and condition
Begin
Select ...
From ...
Where ...
END

You could also use case when ...

For SQL Syntax refer to Microsoft SQL Server Books On Line (BOL) for more information.

Let me know if you need more help. If you are looking for a book on writing Stored Procedures, The Gurus Guide is a decent one.

Ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[patty1]

Thanks ro - Will give it a try tomorrow.

 

[Naith]

I don't know if you've mentioned what database you use in another post, but GetDate() is a Sybase and SQLServer function. If you're writing against Oracle, use SysDate.

Naith

 

[patty1]

unfortunately am getting error messages with formula (by the way database is Sybase). Below is formula in its entireity. Error messages are Incorrect syntax on lines 31, 36, 42, 48, 54, 61, and 67. Any help appreciated.

31 If datepart(Month,getdate()) between 1 and 3
32 Begin
33 Select "Post_Date"
34 From Transaction_History
35 Where datepart(month, "Post_Date&quot between 10 and 12
36 Else if
37 datepart(Month,getdate()) between 4 and 6
38 Begin
39 Select "Post_Date"
40 From Transaction_History
41 Where datepart(month, "Post_Date&quot between 1 and 2
42 Else if
43 datepart(Month,getdate()) between 7 and 9
44 Begin
45 Select "Post_Date"
46 From Transaction_History
47 Where datepart(month, "Post_Date&quot between 4 and 6
48 else if
49 datepart(Month,getdate()) between 10 and 12
50 Begin
51 Select "Post_Date"
52 From Transaction_History
53 Where datepart(month, "Post_Date&quot between 7 and 9
54 and if
55 datepart(Month,getdate()) between 1 and 3
56 Begin
57 Select "Post_Date"
58 From Transaction_History
59 Where datepart(year, "Post_Date&quot = datepart
60 (year,getdate(-1))
61 Else
62 Begin
63 Select "Post_Date"
64 From Transaction_History
65 Where datepart(year, "Post_Date&quot = datepart
66 (year,getdate())
67 END

 

[Naith]

That's how you start your procedure?

You should open the procedure with:

CREATE PROCEDURE YourProcName

Queries you have regarding your procedure should probably be aimed at the Sybase forum. But, for the record, if you have an appropriate version of Crystal, using the Calendar1stQtr to Calendar4thQtr functions saves a lot of range typing.

Naith

 

[patty1]

Am testing it as a sql query before I make it a procedure.

can't use Calendar1stqtr etc because users want an automated last full quarter report and so asking for a specific quarter won't do.

Thanks.

 

[patty1]

Am testing it as a sql query before I make it a procedure.

can't use Calendar1stqtr etc because users want an automated last full quarter report and so asking for a specific quarter won't do.

Thanks.

 

[rosemaryl]

Dear Patty,

The last version of Sybase I worked with was SQL Anyshere 5.5 so I cannot recall all the syntatical requirements. I think that you need to get assistance from a Sybase DBA. Do you have access to one?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Naith]

Patty,

If you're just keeping this in SQL external to a procedure, you want to use CASE arguments, not IF/THEN/ELSE.

It's falling over on the IFs because it's expecting a procedure declaration at the top.

If you like, I'll see you in the Sybase forum, and we can go over it there.

Naith