December 03, 2008, 07:44 PM
AFS-SkierInformix SQL - MultiPass Query
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
.................
December 08, 2008, 02:30 PM
sys1165aI'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!
December 08, 2008, 03:25 PM
KerryHi 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
December 08, 2008, 03:26 PM
FransDid 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 ;.
December 08, 2008, 04:07 PM
EricHI 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
December 15, 2008, 06:27 PM
AFS-SkierEric/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.
December 16, 2008, 10:26 AM
EricHAFS-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
December 17, 2008, 04:00 PM
Mighty MaxHow 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.