Error when Update/Insert into SQL table from SAS

[WorkingMan]

Good day everyone, I am using SAS Data Integration Studio (DI Studio 4.902).

I am doing Update/Insert to SQL table from SAS but i got the error below:

ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 
       statement. Please check to make sure that these references are not part of a recursive situation.
ERROR: The current reference to SQL View WORK.W1Y75PT0 has exceeded PROC SQL's limit of 50 references to a single view in a SQL 

Here are the code:

 proc sql ;
MPRINT(ETLS_LOADER):   update ODMSCDS.PRODUCTION_GAS as m set ID = (select ID from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_AG_ACTUAL = (select PRODUCED_AG_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT 
= t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_AG_TARGET = (select PRODUCED_AG_TARGET from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_AG_ACTUAL_UOM = (select PRODUCED_AG_ACTUAL_UOM from WORK.W1Y75PT0 
as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_AG_TARGET_UOM = (select PRODUCED_AG_TARGET_UOM from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_NAG_ACTUAL = (select PRODUCED_NAG_ACTUAL 
from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_NAG_TARGET = (select 
PRODUCED_NAG_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_NAG_ACTUAL_UOM = 
(select PRODUCED_NAG_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
PRODUCED_NAG_TARGET_UOM = (select PRODUCED_NAG_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = 
t.FIELD_RK), PRODUCED_GAS_LP_ACTUAL = (select PRODUCED_GAS_LP_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_LP_TARGET = (select PRODUCED_GAS_LP_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_LP_ACTUAL_UOM = (select PRODUCED_GAS_LP_ACTUAL_UOM from WORK.W1Y75PT0 as t 
where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_LP_TARGET_UOM = (select PRODUCED_GAS_LP_TARGET_UOM from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HP_ACTUAL = (select 
PRODUCED_GAS_HP_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HP_TARGET 
= (select PRODUCED_GAS_HP_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
PRODUCED_GAS_HP_ACTUAL_UOM = (select PRODUCED_GAS_HP_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HP_TARGET_UOM = (select PRODUCED_GAS_HP_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT 
= t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HHP_ACTUAL = (select PRODUCED_GAS_HHP_ACTUAL from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HHP_TARGET = (select PRODUCED_GAS_HHP_TARGET from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), PRODUCED_GAS_HHP_ACTUAL_UOM = (select 
PRODUCED_GAS_HHP_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
PRODUCED_GAS_HHP_TARGET_UOM = (select PRODUCED_GAS_HHP_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), GAS_REINJECT_ACTUAL = (select GAS_REINJECT_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT 
and m.FIELD_RK = t.FIELD_RK), GAS_REINJECT_TARGET = (select GAS_REINJECT_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_REINJECT_ACTUAL_UOM = (select GAS_REINJECT_ACTUAL_UOM from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_REINJECT_TARGET_UOM = (select GAS_REINJECT_TARGET_UOM from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_LIFT_ACTUAL = (select GAS_LIFT_ACTUAL from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_LIFT_TARGET = (select GAS_LIFT_TARGET from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_LIFT_ACTUAL_UOM = (select GAS_LIFT_ACTUAL_UOM 
from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_LIFT_TARGET_UOM = (select 
GAS_LIFT_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_FUEL_ACTUAL = (select 
GAS_FUEL_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_FUEL_TARGET = (select 
GAS_FUEL_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_FUEL_ACTUAL_UOM = (select 
GAS_FUEL_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_FUEL_TARGET_UOM = 
(select GAS_FUEL_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_AG_LOC 
= (select GAS_SALES_AG_LOC from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
GAS_SALES_AG_ACTUAL = (select GAS_SALES_AG_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = 
t.FIELD_RK), GAS_SALES_AG_TARGET = (select GAS_SALES_AG_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), GAS_SALES_AG_ACTUAL_UOM = (select GAS_SALES_AG_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_AG_TARGET_UOM = (select GAS_SALES_AG_TARGET_UOM from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_NAG_LOC = (select GAS_SALES_NAG_LOC from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_NAG_ACTUAL = (select GAS_SALES_NAG_ACTUAL from WORK.W1Y75PT0 as t 
where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_NAG_TARGET = (select GAS_SALES_NAG_TARGET from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_NAG_ACTUAL_UOM = (select 
GAS_SALES_NAG_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
GAS_SALES_NAG_TARGET_UOM = (select GAS_SALES_NAG_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK 
= t.FIELD_RK), GAS_SALES_TOTAL = (select GAS_SALES_TOTAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = 
t.FIELD_RK), GAS_SALES_TOTAL_UOM = (select GAS_SALES_TOTAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
39                                                         The SAS System                           12:06 Thursday, January 25, 2018

m.FIELD_RK = t.FIELD_RK), GAS_PROD_JUSTIFICATION = (select GAS_PROD_JUSTIFICATION from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), FLARING_VENTING_JUSTIFICATION = (select FLARING_VENTING_JUSTIFICATION from WORK.W1Y75PT0 
as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), LOADING_DTTM = (select LOADING_DTTM from WORK.W1Y75PT0 as t 
where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), LP_FLARING_VENTING_ACTUAL = (select LP_FLARING_VENTING_ACTUAL from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), LP_FLARING_VENTING_ACTUAL_UOM = (select 
LP_FLARING_VENTING_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
LP_FLARING_VENTING_TARGET = (select LP_FLARING_VENTING_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), LP_FLARING_VENTING_TARGET_UOM = (select LP_FLARING_VENTING_TARGET_UOM from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), HP_FLARING_VENTING_ACTUAL = (select HP_FLARING_VENTING_ACTUAL from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), HP_FLARING_VENTING_ACTUAL_UOM = (select 
HP_FLARING_VENTING_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), 
HP_FLARING_VENTING_TARGET = (select HP_FLARING_VENTING_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and 
m.FIELD_RK = t.FIELD_RK), HP_FLARING_VENTING_TARGET_UOM = (select HP_FLARING_VENTING_TARGET_UOM from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_TOTAL_TARGET = (select GAS_SALES_TOTAL_TARGET from WORK.W1Y75PT0 
as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), GAS_SALES_TOTAL_TARGET_UOM = (select GAS_SALES_TOTAL_TARGET_UOM 
from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), MOISTURE_CONTENT = (select MOISTURE_CONTENT 
from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), MOISTURE_CONTENT_UOM = (select 
MOISTURE_CONTENT_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), VENT_ACTUAL = (select 
VENT_ACTUAL from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), VENT_ACTUAL_UOM = (select 
VENT_ACTUAL_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), VENT_TARGET = (select 
VENT_TARGET from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), VENT_TARGET_UOM = (select 
VENT_TARGET_UOM from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), WF_PHASE = (select WF_PHASE 
from WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), WF_STATUS = (select WF_STATUS from 
WORK.W1Y75PT0 as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), WF_USERID = (select WF_USERID from WORK.W1Y75PT0 
as t where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), OFFLINE_DATE = (select OFFLINE_DATE from WORK.W1Y75PT0 as t 
where m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK), WF_USERMODDATE = (select WF_USERMODDATE from WORK.W1Y75PT0 as t where 
m.REPORT_DT = t.REPORT_DT and m.FIELD_RK = t.FIELD_RK) where exists (select * from WORK.W1Y75PT0 as t where m.REPORT_DT = 
t.REPORT_DT and m.FIELD_RK = t.FIELD_RK);

Is there a solution to this?

Seeking for your assistance.

Regards,

Work

[LinusH]

Some code you get there.
Lots of subqueries. There is probably better ways of doing this, without that logic.
How does your job look like?

[WorkingMan]

Hi Linus,

I use Update/Insert in Table Loader, with Insert(SQL) when there is new rows. You may refer to screenshot below:

And here is how my job looks like:

This error only happen when there are more than 50 columns in the table. If table is less than 50 columns, then there won't be any error at all. As per the error, is there a way to set the 50 columns SQL limit to more than that?

For the code that you asked, it is actually generated from Table Loader. Do you have any workaround for it?

[Patrick]

@WorkingMan

Mocking up your flow and then playing around with the table loader it appears that:

If you're using SQL SET but don't map all source columns to target (which is a valid case) then the table loader generates for some reason I don't understand an intermediary view and then uses this view to update the target. That looks like very inefficient code to me and it's the root cause of the issue. As @LinusH suggests: Contact SAS TechSupport.

You could try and use MODIFY using INDEX instead or you could use the SQL UPDATE and SQL INSERT transformation instead. If using the SQL UPDATE and INSERT transformations then I would also first load your source table to SQL server (using proc append) and then have everything in-database.

If the scenario you've posted is something you're doing often then I'd also consider to create my own custom transformation. Creating something dynamic which also uses mapping information (the generated mapping macros) will be a bit involved so only worth it if used regularly. If going for a custom transformation the you could as part of the transformation always first load the data into a SQL staging table and then have all the rest done using pass-through SQL and though take advantage of the SQL Server code flavor (i.e. MERGE). 

[LinusH]

Oh must admit that I didn't realise that it was generated code...
It's quite limitation which should be prevented by the UI if it's known by SAS. Please report it to SAS Tech support.
Besides from that, I would try other update techniques and see if they work for you.