HI,
I don't know if this is the right group, if not, please point me to
the right one.

I'm using SQL to query INformix, but I'm getting some weird errors.
Below is my query, why is Informix bitching about syntax errors?
Also, it doesn't tell me what's wrong, or even where, just that there
is something wrong! Coming from MS SQL, this is very frustrating.

QUERY:

DECLARE @SrcBusinessUnits int
SET @SrcBusinessUnits = 1
DECLARE @DestBusinessUnits int
SET @DestBusinessUnits = 1
DECLARE @ToDate datetime
SET @ToDate = '2007-07-05 00:00:00'
DECLARE @FromDate datetime
SET @FromDate = '2007-07-01 00:00:00'

select
(
select
name
from
bus_unit
where
bu_id = @SrcBusinessUnits
) as BusinessUnit,
'2007-07-01' as FromDate,
'2007-07-05' as ToDate,
count(*) as TotalWaybills,
SUM(cons_pkts) as TotalParcels,
SUM(cons_kg_used) as TotalKilograms,
(
select
count(*)
from
cont_consmnt
where
cc_cons_id in
(SELECT
cons_ID
from
consignment
where
cons_src_bu = @SrcBusinessUnits AND
cons_dte_created BETWEEN @FromDate AND @ToDate
)
) as ContainerLoad
from
consignment
where
cons_src_bu = @SrcBusinessUnits AND
cons_dte_created BETWEEN @FromDate AND @ToDate

Any idea what I'm doing wrong?

On Mon, 2007-10-08 at 11:07 +0000, jarppi.duplessis (AT) gmail (DOT) com wrote:
There are ways to find out where the syntax error is being detected, but
you'd have to tell us what you're using to submit the query. If you're
using dbaccess, after you Run the query, choose Modify, and the cursor
will be placed in the offending position. If you're using some client
programming language that offers access to sqlca, sqlca.sqlerrd will
contain (in some slot, I don't remember which one at the moment) the
byte offset of the offending position.

Anyway, your problem is that Informix SQL doesn't allow declaring
variables like this. If you're trying to write a stored procedure, look
up the correct SPL syntax for declaring variables. Otherwise, since your
values are just literal constants anyway, try substituting the constants
directly into your query.

Good luck,

--
Carsten Haese
http://informixdb.sourceforge.net

On Oct 8, 7:07 am, jarppi.duples... (AT) gmail (DOT) com wrote:
What version of IDS are you running? Platform may help also.

Note that IDS 7.xx versions do not support variables except within
stored procedures.

Art S. Kagel

jarppi.duplessis (AT) gmail (DOT) com wrote:

You will need to modify your SQL statements somewhat for Informix since
once you get into declaring variables and procedure you can't just copy
and paste between MSSQL and Informix. Here are some differences to take
account of:

- I don't think variable names need a '@' or any other character in
front of them.
- Each statement will need to end with a semi-colon. MSSQL doesn't
enforce this; Informix does.
- I don't think 'datetime' is an acceptable variable type. It looks like
you want 'datetime year to day'.
- 'DECLARE' should be 'DEFINE' I think.
- You probably want to encapsulate your SQL statements is a stored
procedure if you want to use variables. I don't think you can use them
in raw SQL.

I hope this helps but you'll probably have to do some reading yourself
as well.

Regards, Ben.

Go through these links given below. It would surely help you in getting the
basic difference B/W INFORMIX and SQL Stored procedures
http://www.4js.com/exclude/en/html/f.../odiagmsv.html
http://download.oracle.com/docs/html/B16022_01/ch3.htm

Thanks
Balaji Vinu


On 10/8/07, Ben Thompson <ben (AT) nomonitorsoftspam (DOT) com> wrote:


--
no me "NO" life know me "KNOW" life