I have an Multi-pass Informix SQL that does not run on WebFOCUS DevStudio. I'm getting the error message below. I'm able to run single pass Informix SQLs.

(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000
: A syntax error has occurred.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT BYPASSING TO END OF COMMAND

Here is the syntax. It will run to the end of the 1st pass, before the "into temp". It will run directly on the Informix server.

SQL SQLINF SET AUTODISCONNECT ON COMMAND SQL SQLINF END SESSION SQL SQLINF SET DBDATE_OVERRIDE OFF

ENGINE SQLINF SET DEFAULT_CONNECTION tagus SQL SQLINF PREPARE SQLOUT FOR

select a12.segment_id segment_id,
a12.catmgr_id catmgr_desc,

sum(a11.sales_d) WJXBFS1,
sum(a11.sales_sngl_u) WJXBFS2
from f_wlsr_data a11,
p_item a12
where a11.item_id = a12.item_id
and (a12.whse_cat_id = '6'
and a11.week_id in (200848))
group by a12.segment_id,
a12.catmgr_id
into temp ZZTCA0202NZMD000 with no log

GO
select a13.segment_id segment_id,
a13.catmgr_id catmgr_desc,
sum(a11.sales_d) WJXBFS1,
sum(a11.sales_sngl_u) WJXBFS2
from f_wlsr_data a11,
t_week a12,
p_item a13
where a11.week_id = a12.same_wk_yago_id and a11.item_id = a13.item_id and (a13.whse_cat_id = '6'
and a12.week_id in (200848))
group by a13.segment_id,
a13.catmgr_id
into temp ZZTCA0202NZMD001 with no log

GO
select pa11.segment_id segment_id,
pa11.catmgr_desc catmgr_desc
from ZZTCA0202NZMD000 pa11
into temp ZZTCA0202NZOJ002 with no log

GO
insert into ZZTCA0202NZOJ002
select pa11.segment_id segment_id,
pa11.catmgr_desc catmgr_desc
from ZZTCA0202NZMD001 pa11

GO
select distinct pa11.segment_id segment_id, pa11.catmgr_desc catmgr_desc from ZZTCA0202NZOJ002 pa11 into temp ZZTCA0202NZOD003 with no log

GO
select distinct pa11.catmgr_desc catmgr_desc, a15.catmgr_desc catmgr_desc0, a14.department_id department_id, a18.DEPARTMENT_DESC DEPARTMENT_DESC, a14.category_id category_id, a17.category_desc category_desc, a14.subcategory_id subcategory_id, a16.subcategory_desc subcategory_desc, pa11.segment_id segment_id, a14.segment_desc segment_desc,

pa12.WJXBFS1 AS sales,
pa13.WJXBFS1 AS salesyago,
pa12.WJXBFS2 AS units,
pa13.WJXBFS2 AS unitsyago
from ZZTCA0202NZOD003 pa11, outer
ZZTCA0202NZMD000 pa12, outer
ZZTCA0202NZMD001 pa13,
p_segment a14,
p_category_mgr a15,
p_subcategory a16,
p_category a17,
p_department a18
where pa11.catmgr_desc = pa12.catmgr_desc and pa11.segment_id = pa12.segment_id and pa11.catmgr_desc = pa13.catmgr_desc and pa11.segment_id = pa13.segment_id and pa11.segment_id = a14.segment_id and pa11.catmgr_desc = a15.catmgr_id and a14.subcategory_id = a16.subcategory_id and a14.category_id = a17.category_id and a14.department_id = a18.department_id

GO
drop table ZZTCA0202NZMD000

GO
drop table ZZTCA0202NZMD001

GO
drop table ZZTCA0202NZOJ002

GO
drop table ZZTCA0202NZOD003

END
TABLE FILE SQLOUT
.................

I'm sorry - I'm no help. We don't use Informix any more since we converted to Oracle. I've never done a multi pass, nor have I held the data in temp, nor have I coded anything similar to your coding. I don't really know SQL since I code strictly in focus code. I've doctored up some SQL that others have written, but often need help doing even that. Good luck!

Hi AFS-Skier and all,

FYI, it looks like a case is already opened regarding this issue, and I was suggested that a case may be of better help for this one. I will try to keep you posted if any solution is given on the case.

Cheers,

Kerry

Did you allready try a more simpel approach:
SET DEFAULT_CONNECTION tagus
SQL SQLINF
sql blablabla...
;
END
TABLE FILE SQLOUT
PRINT *
END

So a simple connection string and SQL ending with ;.

I have not used Informix, but my suspicion is that the problem is due to the GO statement. GO is also used by MS SQL*Server and it is a non SQL command used by the SQL*Server delivered tools; however WebFOCUS DPT will not recognize it.

I would recommend starting with Fran's suggestion. Then you could experiment with replacing the GOs with semi-colons; that might work. Alternatively, you could try breaking up your SQL into individual DPT queries. WebFOCUS should remember the temp files within a single session.

EricH

Eric/Fran,

I'm able to run single pass SQL statements. Where I'm having trouble, is running multi-pass SQLs (Informix & SQL server) with WebFocus. These are multi-pass SQLs that are currently running on these platforms with the "GO" syntax.

I relies our tool query terminator string is set as "GO". Is it possible set a query terminator string in WebFocus? Or what would tell WebFocus to go to the next SQL string?

My main objective is to run multi-pass SQL statements.

AFS-Skier,

We understand what you're asking for. Did you try using a semi-colon in place of the GO like Fran was implying?

If that does not work, then you may need to split your query up into multiple SQL SQLINF sections like I suggested. Here is some crude "pseudo code" that shows how you might proceed:

 
SQL SQLINF
select a12.segment_id segment_id,
a12.catmgr_id catmgr_desc,

sum(a11.sales_d) WJXBFS1,
sum(a11.sales_sngl_u) WJXBFS2
from f_wlsr_data a11,
p_item a12
where a11.item_id = a12.item_id
and (a12.whse_cat_id = '6'
and a11.week_id in (200848))
group by a12.segment_id,
a12.catmgr_id
into temp ZZTCA0202NZMD000 with no log;
END
-RUN
-IF (&FOCERRNUM NE 0) THEN GOTO ERROR ;

SQL SQLINF
select a13.segment_id segment_id,
a13.catmgr_id catmgr_desc,
sum(a11.sales_d) WJXBFS1,
sum(a11.sales_sngl_u) WJXBFS2
from f_wlsr_data a11,
t_week a12,
p_item a13
where a11.week_id = a12.same_wk_yago_id and a11.item_id = a13.item_id and (a13.whse_cat_id = '6'
and a12.week_id in (200848))
group by a13.segment_id,
a13.catmgr_id
into temp ZZTCA0202NZMD001 with no log
END
-RUN
-IF (&FOCERRNUM NE 0) THEN GOTO ERROR ;

-* etc etc etc
-*  Then if all steps complete,
SQL SQLINF
   COMMIT ;
END
-EXIT

-*  Otherwise rollabck
-ERROR
SQL SQLINF
   ROLLBACK ;
END

 

Is this a little more work than a direct cut and paste? Yes. However, it does give you a lot more control. For example, if there is an Informix/SQL error somewhere along the line - maybe Informix ran out of temp space - you can trap the exact location where the problem occurs.

EricH

How about making a stored procedure on Informix and then calling the stored procedure from WebFOCUS?

Don't know if this is possible with Informix.
Only a suggestion.