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,

Is this SQL code wrapped in SQL SQLDBC ... END ?

As far as I know you can only issue one command at a time

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

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.

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

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.

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