Using conditional If statement in InfoMaker

[Scott Huerta]

InfoMaker 9.0.2.7509
ASA 8.0.2.4454

I have the following SQL command working correctly in Sybase Central
(ISQL) but when I try to run it in InfoMaker I get an error message
that states "Host variable may not be used in a batch.".

The SQL command is:

if :sLevel='a' then
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and a.AcctId=:sId
elseif :sLevel='d' then
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
Join Debtor d on d.SysId=a.SysDtrId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and d.DebtorId=:sId
end if

Is this not supported in InfoMaker?

Bottom-line, what I'm trying to do is have one report versus several
reports that executes a different SQL statement based on one of the
retrieval argument's value.

What am I doing wrong or is this not possible?

Thanks in advance,

Scott Huerta
(3i Infotech)
Scott Huerta
3i Infotech (formerly Distinctive Solutions Corp.)

[Paul Horan[Sybase]]

The easiest method is to write that as a stored procedure, and then paint
the datawindow to invoke that SP. As long as the result sets from each
report "section" are congruent, you'll be able to add more and more
"elseif"s to the SP without having to redeploy the datawindow.
--
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

Post by Scott Huerta
InfoMaker 9.0.2.7509
ASA 8.0.2.4454
I have the following SQL command working correctly in Sybase Central
(ISQL) but when I try to run it in InfoMaker I get an error message
that states "Host variable may not be used in a batch.".
if :sLevel='a' then
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and a.AcctId=:sId
elseif :sLevel='d' then
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
Join Debtor d on d.SysId=a.SysDtrId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and d.DebtorId=:sId
end if
Is this not supported in InfoMaker?
Bottom-line, what I'm trying to do is have one report versus several
reports that executes a different SQL statement based on one of the
retrieval argument's value.
What am I doing wrong or is this not possible?
Thanks in advance,
Scott Huerta
(3i Infotech)
Scott Huerta
3i Infotech (formerly Distinctive Solutions Corp.)

[Scott Huerta]

This post might be inappropriate. Click to display it.

[Terry Dykstra [TeamSybase]]

You can avoid a SP by using a union statement. The :sLavel retrieval
argument ensures only one of the selects ever returns rows.

select
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and a.AcctId=:sId
and :sLevel='a'
union all
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
Join Debtor d on d.SysId=a.SysDtrId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and d.DebtorId=:sId
and elseif :sLevel='d'
--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

Post by Scott Huerta
Paul, Thanks for the reply.
I thought about this but was trying to avoid an SP due to the
"red-tape" in installing new database objects (our QA process). But
if this is the best way then I'll have to "bite the bullet".
Thanks
Scott
On 22 Jun 2009 06:31:33 -0700, "Paul Horan[Sybase]"

Post by Paul Horan[Sybase]
The easiest method is to write that as a stored procedure, and then paint
the datawindow to invoke that SP. As long as the result sets from each
report "section" are congruent, you'll be able to add more and more
"elseif"s to the SP without having to redeploy the datawindow.

Scott Huerta
3i Infotech (formerly Distinctive Solutions Corp.)

[Scott Huerta]

Terry,

Thank you very much! Now that you point this out, it is obvious.

I've been struggling with how to do this for quite some time.

I learned something today.

Thanks again,

Scott


On 22 Jun 2009 09:00:11 -0700, "Terry Dykstra [TeamSybase]"

Post by Terry Dykstra [TeamSybase]
You can avoid a SP by using a union statement. The :sLavel retrieval
argument ensures only one of the selects ever returns rows.
select
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and a.AcctId=:sId
and :sLevel='a'
union all
select count(i.SysId)
from Invoice i
Join Schedules s on s.SysId=i.SysSchedId
Join Account a on a.SysId=i.SysAcctId
Join Debtor d on d.SysId=a.SysDtrId
where s.PurchDate between MakeDay01(:dMonth) and MakeEOM(:dMonth)
and d.DebtorId=:sId
and elseif :sLevel='d'

Scott Huerta
3i Infotech (formerly Distinctive Solutions Corp.)