SAS to vertica :CLI prepare error: {vertica}verticaDSII} (10)

[chandu1]

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

[SASKiwi]

It looks like you are trying to join a temporary SAS dataset (work.payments) in a query to Vertica. This could be the cause of your problem. What happens if you remove that join and just have a pure Vertica query? 

[chandu1]

This code was run earlier without error.This is automated code .After changing configurations like odbc.ini file , this error occured . Is there any possibilty to set up buffersize in odbc connection ?

[SASKiwi]

When you join to SAS data it is likely all of the database data is extracted out to SAS and then joined to the SAS data. This isn't efficient. A better approach is to load the SAS data into the database first, and do all of the joining in the database. When the query was working how long did it take? 

[chandu1]

We added vertica.ini file in sasenv_local file earlier .After removing this file it works fine.

[SASKiwi]

OK, good. Please mark post as answered then.