Hi all,

Now before you tell me to go to the Sybase forum, my problem is I need to re-write SQL which is fairly universal (and some of you get a kick out of). The only reason I need to re-write it is because it is running on IQ 12.4.3. Now the reason I am posting in here is because this forum is used more often than the Sybase one.. So any help would be appreciated.. Thanks in advance.

Recently we have upgraded from Sybase IQ 12.4.2 to 12.4.3 EBF#12. Since then the following code fails with the error "A Maximum of one subquery predicate per conjunct is supported in this release".

Code:
select 	distinct month_name, fiscal_year
from 	ck_calendar
where 	(month_name = 'July'
and 	fiscal_year = 2004)
or 	(
	month_name = (
	select distinct month_name
	from ck_calendar
	where real_date = (
	select dateadd(month, - 1, max(real_date))
	from ck_calendar
	where month_name = 'July'
	and fiscal_year = 2004))
and	fiscal_year = (
	select distinct fiscal_year
	from ck_calendar
	where real_date = (
	select dateadd(month, - 1, max(real_date))
	from ck_calendar
	where month_name = 'July'
	and fiscal_year = 2004)))
or 	(month_name = 'July'
and	fiscal_year = (
	select distinct fiscal_year
	from ck_calendar
	where real_date = (
	select dateadd(year, - 1, max(real_date))
	from ck_calendar
	where month_name = 'July'
	and fiscal_year = 2004)))
A quick search of the Sybase website, didn't give me any answers except to re-write the code...

The table is built using the following:

Code:
create table object.ck_calendar (
date_key 		unsigned int not null,
real_date 		date,
day_no_week 	integer,
day_no_month 	integer,
day_no_cal_year 	integer,
day_no_fiscal_year 	integer,
day_no_sys	integer,
day_name 	char(9),
day_abbrev 	char(3),
date_last_year 	date,
day_last_year_key 	integer,
weekday_flag 	char(3),
week_no_cal 	integer,
week_no_fiscal 	integer,
week_no_sys 	integer,
week_end_date 	date,
week_end_key 	integer,
month_key 	integer,
month_no_cal 	integer,
month_no_fiscal 	integer,
month_no_sys 	integer,
month_name 	char(9),
month_abbrev 	char(3),
month_end_date 	date,
month_year 	varchar(7),
cal_qtr 		integer,
fiscal_qtr 		integer,
fiscal_qtr_year 	varchar(12),
cal_year 		integer,
fiscal_year 	integer,
primary key (date_key)
)
The SQL which is failing used to return the current month-year, previous month-year and the month-year from the previous financial year. An example of the output is as follows:

month_name fiscal_year
July 2003 (same month.. previous year)
June 2003 (previous month, happens to be in previous financial year)
July 2004 (selected month)

(Our financial year runs from July -> June)

Now my problem is I need to re-write the SQL that is failing so that it no longer violates the restriction in the error message. Now my major problem is that I can not use temporary tables/variables etc as this SQL is generated in a reporting application. So.... I can only changed the code contained with in the where clause... However I can change the table and add columns etc. The other thing is that the user is only prompted to enter a month and a year (hence the 'July' and 2004)..

Please help!! It's driving me crazy...

Many thanks