sql in informix

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@gmail.com wrote: > 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' > > [...] 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...@gmail.com wrote: > 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? 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@gmail.com wrote: > 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. [snip] > Any idea what I'm doing wrong? 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.