DBMS Naming Conventons Error

Reply
Contributor
Posts: 45

DBMS Naming Conventons Error

[ Edited ]

 

ERROR: This DBMS table or view cannot be accessed by the SAS System because it contains column names that are not unique when a SAS normalized (uppercased) compare is performed.  See "Naming Conventions" in the SAS/ACCESS documentation.

Does anyone know why I might be getting this error?
the code i'm using came from someone who used this query last fall in SQL. These tables run fine in Hyperion.

It has nothing to do with the fields we select (we actually didn't select any from the problem table)

I've looked in the table and there are no duplicate fields.

 

This is happening on at least 2 tables in this DBMS. any insight is appreciated. Portico.Pp_Prac and Portico.PP_Prac_Loc tables (so length is not the issue)

Base SAS 9.2 / SAS Enterprise Guide 6.1 - it happens in both

Super User
Super User
Posts: 7,736

Re: DBMS Naming Conventons Error

First lets check the database.  Do an sql describe table from within the database on those two tables giving you a problem:

describe Portico.Pp_Prac

describe Portico.PP_Prac_Loc

Then post what the describe text is returned.  It could be an incompatible character replacement, or mixed case etc.  Can't tell without seein the database structure,

Contributor
Posts: 45

Re: DBMS Naming Conventons Error

That was going to be my next step - just double check what fields are there.

Contributor
Posts: 45

Re: DBMS Naming Conventons Error

i'm unable to do a describe table.... so weird....

Super User
Posts: 7,475

Re: DBMS Naming Conventons Error

DBMS's allow the use of characters in names that are not valid in Base SAS.

IE DB/2 allows the use of the number sign.

Maybe someone added a new field in the database in the meantime that violates SAS rules.

Try to use pass-through SQL to eliminate or change such columns.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 45

Re: DBMS Naming Conventons Error

I will do a pass through and see if that works. Since it worked in sql - i'm hoping that does the trick.  I didn't see any characters at all in the field names.

Super User
Posts: 11,144

Re: DBMS Naming Conventons Error

[ Edited ]

Another potential issue is the length of SAS variable names, 32 characters, versus your DB. If you have very long variable names such as:

 

This_is_a_very_long_example_variable_name_for_a_variable_that_contains_birth_date

This_is_a_very_long_example_variable_name_for_a_variable_that_contains_service_date

 

The two variables are identical for the first 32 characters and the usual SAS rules for transforming to a SAS variable would yield the same 32 character variable.

 

 

Contributor
Posts: 45

Re: DBMS Naming Conventons Error

great point! I should be able to tell that when i do the describe table. Thanks for pointing this out!

Contributor
Posts: 45

Re: DBMS Naming Conventons Error

Passthrough is working! I don't see any weird characters - so who knows why.

Super User
Posts: 7,475

Re: DBMS Naming Conventons Error

[ Edited ]

Also no blanks? What about length of names? 33 characters may not be obvious, but will be a showstopper.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,391

Re: DBMS Naming Conventons Error

It doesn't have to be weird chars. It just might be a single char in like named column that differs:
Gender
gender
Data never sleeps
Ask a Question
Discussion stats
  • 10 replies
  • 541 views
  • 0 likes
  • 5 in conversation