Hi,
We ran the below code and got error .
libname s odbc dsn=edw schema=retail_husky_public user=[USER NAME] password=[PASSWORD REMOVED]
;
/* simple queries work */
proc sql;
select d.date_id
from s.date_dim d
;
quit;
/* awkward / large queries fail */
proc sql;
create table work.all_transactions as
select
distinct a.date_id,
a.store_no,
g.store_name,
b.card_no,
d.cust_type,
b.customer_id,
d.card_type,
c.first_name,
c.last_name,
c.email,
b.card_status,
f.account_status,
a.card_id,
a.pos_trans_no,
a.pos_till_no,
a.trans_time,
a.sales_trans_id,
case when x.tendername ='' then 'Other'
when x.tendername is null then 'Lotto Redemption'
else x.tendername end as tendername
,case when x.accountnumber ='' then 'N/A'
when x.accountnumber is null then 'Lotto Redemption'
else x.accountnumber end as accountnumber,
a.base_points_earned,
a.points_redeemed,
case when a.product_item_id in (56537,30896) then quantity_sold
else 0 end as total_fuel_volume,
sales_value
from
s.sales_item_fact a,
s.card_dim b,
s.customer_dim c,
s.ma_cards d,
s.account_dim f,
s.store_location_dim g
,work.payments x
where a.card_id <> 1 and
b.customer_id <> 1 and
a.date_id between &startdate and &enddate
and a.card_id = b.card_id
and c.customer_id = b.customer_id
and d.card_no = b.card_no
and f.account_id = b.account_id
and a.store_no = g.store_no
and d.account_status <> 'Suspended'
and x.sales_trans_id = a.sales_trans_id
and a.product_item_id not in (10012,
100212
)
order by b.card_no;
quit;
Please look into the attachment for full details of error. Could you please your suggestions for to resolve this issue.
Error :
CLI prepare error: {vertica}verticaDSII} (10) An error occurred during query preparation:
Multiple commands cannot be active on the same connection.Consider increasing ResultBuffersize or fetching
all results before initiating another command
