turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Error when Update/Insert into SQL table from SAS

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-24-2018 11:42 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WorkingMan

01-25-2018 02:34 AM

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?

Lots of subqueries. There is probably better ways of doing this, without that logic.

How does your job look like?

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

01-25-2018 02:40 AM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WorkingMan

01-25-2018 11:57 PM

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).

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WorkingMan

01-25-2018 11:33 AM

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.

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.

Data never sleeps