Hello, When I run the following SQL in Teradata SQL assistant, I brings back results. When I try to run it in Web Focus, I get the following error
"(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: H13 BYPASSING TO END OF COMMAND"
1. Can someone please edit my code so it will run in Web Focus? I know I must be missing something that will allow pass-through. thank you.
create volatile table mkt_basket_v2, NO LOG (rgtn_i bigint ,co_loc_i smallint ,basket_q integer ,trans_q integer) on commit preserve rows; insert into mkt_basket_v2 select a.rgtn_i ,a.co_loc_i ,sum (case when ( b.mdse_dept_ref_i = 058 and b.mdse_clas_ref_i = 10 and b.mdse_item_ref_i = 3817) then 1 else 0 end) as basket_q ,count (*) as trans_q from prod_v.mdse_slstr_item_line A join prodrpt_v.mdse_item_dim_v B on a.mdse_item_i = b.mdse_item_i where a.sls_d = '2012-10-25' and a.rgtn_i = 1201210253446800 group by a.rgtn_i ,a.co_loc_i having basket_q > 0;
select b.co_loc_i ,b.rgtn_i ,b.sls_d ,c.dpci_lbl_t ,c.mdse_item_desc_t ,v.basket_q ,v.trans_q from mkt_basket_v2 V inner join prod_v.mdse_slstr_item_line B on v.rgtn_i = b.rgtn_i and v.co_loc_i = b.co_loc_i inner join prodrpt_v.mdse_item_dim_v C on b.mdse_item_i = c.mdse_item_i order by b.co_loc_i ,b.rgtn_i;This message has been edited. Last edited by: Kerry,
Web Focus 7.6
November 13, 2012, 03:49 PM
Waz
Is this SQL code wrapped in SQL SQLDBC ... END ?
As far as I know you can only issue one command at a time
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 14, 2012, 02:06 PM
Tom from Target
Yes, here is the code in WF....
SQL SQLDBC
create volatile table mbasket, NO LOG (rgtn_i bigint ,co_loc_i smallint ,basket_q integer ,trans_q integer) on commit preserve rows; insert into mbasket
(select a.rgtn_i ,a.co_loc_i ,sum (case when ( b.mdse_dept_ref_i = 058 and b.mdse_clas_ref_i = 10 and b.mdse_item_ref_i = 3817) then 1 else 0 end) as basket_q ,count (*) as trans_q from prod_v.mdse_slstr_item_line A join prodrpt_v.mdse_item_dim_v B on a.mdse_item_i = b.mdse_item_i where a.sls_d = '2012-10-25' and a.rgtn_i = 1201210253446800 group by a.rgtn_i ,a.co_loc_i having basket_q > 0); select b.co_loc_i ,b.rgtn_i ,b.sls_d ,c.dpci_lbl_t ,c.mdse_item_desc_t ,v.basket_q ,v.trans_q from MBASKET V inner join prod_v.mdse_slstr_item_line B on v.rgtn_i = b.rgtn_i and v.co_loc_i = b.co_loc_i inner join prodrpt_v.mdse_item_dim_v C on b.mdse_item_i = c.mdse_item_i order by b.co_loc_i ,b.rgtn_i;
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS H13 END TABLE FILE H13 PRINT * END -RUN
Web Focus 7.6
November 14, 2012, 03:45 PM
Waz
You have multiple commands in this, WebFOCUS only supports one at a time.
Have you tried:
SQL SQLDBC
create volatile table mbasket, NO LOG
(rgtn_i bigint
,co_loc_i smallint
,basket_q integer
,trans_q integer)
on commit preserve rows;
END
SQL SQLDBC
insert into mbasket
(select
a.rgtn_i
,a.co_loc_i
,sum (case when ( b.mdse_dept_ref_i = 058 and
b.mdse_clas_ref_i = 10 and
b.mdse_item_ref_i = 3817)
then 1 else 0 end) as basket_q
,count (*) as trans_q
from prod_v.mdse_slstr_item_line A
join prodrpt_v.mdse_item_dim_v B
on a.mdse_item_i = b.mdse_item_i
where a.sls_d = '2012-10-25'
and a.rgtn_i = 1201210253446800
group by a.rgtn_i
,a.co_loc_i having basket_q > 0);
END
SQL SQLDBC
select
b.co_loc_i
,b.rgtn_i
,b.sls_d
,c.dpci_lbl_t
,c.mdse_item_desc_t
,v.basket_q
,v.trans_q
from MBASKET V
inner join prod_v.mdse_slstr_item_line B
on v.rgtn_i = b.rgtn_i
and v.co_loc_i = b.co_loc_i
inner join prodrpt_v.mdse_item_dim_v C
on b.mdse_item_i = c.mdse_item_i
order by b.co_loc_i
,b.rgtn_i;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS H13
END
TABLE FILE H13
PRINT *
END
-RUN
that is if my understanding of you code is correct.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 14, 2012, 04:38 PM
Tom from Target
When I run the code changes you suggested...
FOC1400) SQLCODE IS -3706 (HEX: FFFFF186) XOPEN: 42000 : [37000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error : : expected something between '(' and the 'select' keyword. (FOC1414) EXECUTE IMMEDIATE ERROR. (FOC1400) SQLCODE IS -3807 (HEX: FFFFF121) XOPEN: 42S02 : [S0002] [Teradata][ODBC Teradata Driver][Teradata Database] Object 'MBAS : KET' does not exist. L (FOC1405) SQL PREPARE ERROR. 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
Web Focus 7.6
November 14, 2012, 04:49 PM
Waz
I don't have much experience with Terradata, so I may have split the code in the wrong place, or may not be able to be split.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
November 26, 2012, 11:41 AM
Tom from Target
Case closed. The following code works correctly:
SQL SQLDBC CREATE VOLATILE TABLE mkt_basket_v2, NO LOG (rgtn_i bigint ,co_loc_i smallint ,basket_q integer ,trans_q integer) on commit preserve rows; END SQL SQLDBC COMMIT WORK END SQL SQLDBC insert into mkt_basket_v2
select a.rgtn_i ,a.co_loc_i ,sum (case when ( b.mdse_dept_ref_i = 058 and b.mdse_clas_ref_i = 10 and b.mdse_item_ref_i = 3817) then 1 else 0 end) as basket_q ,count (*) as trans_q from prod_v.mdse_slstr_item_line A join prodrpt_v.mdse_item_dim_v B on a.mdse_item_i = b.mdse_item_i where a.sls_d = '2012-10-25' and a.rgtn_i = 1201210253446800 group by a.rgtn_i ,a.co_loc_i having basket_q > 0; END SQL SQLDBC
select b.co_loc_i ,b.rgtn_i ,b.sls_d ,c.dpci_lbl_t ,c.mdse_item_desc_t ,v.basket_q ,v.trans_q from mkt_basket_v2 V inner join prod_v.mdse_slstr_item_line B on v.rgtn_i = b.rgtn_i and v.co_loc_i = b.co_loc_i inner join prodrpt_v.mdse_item_dim_v C on b.mdse_item_i = c.mdse_item_i order by b.co_loc_i ,b.rgtn_i; END TABLE FILE SQLOUT PRINT * END