CODEWITHSUNDEEP
oracle-databaselinked-server
Ashar Syed
Ashar Syed

Reputation: 1256

"ORA-01747: invalid user.table.column, table.column, or column specification"

I am trying to execute this query against Oracle linked server, and getting the following error, which I know that my query is malformed, but I couldn't figure out where and how.

DECLARE @CREDIT_CUST_SQL NVARCHAR(1000) = NULL;
SET @CREDIT_CUST_SQL = 'SELECT 
                           cu.[ST_CD]
                          ,cu.[SRT_CD]
                          ,cu.[TITLE]
                          ,cu.[FNAME]
                          ,cu.[INIT]
                          ,cu.[LNAME]
                          ,cu.[ADDR1]
                          ,cu.[ADDR2]
                          ,cu.[CITY]
                          ,cu.[COUNTRY]
                          ,cu.[ZIP_CD]
                          ,cu.[HOME_PHONE]
                          ,cu.[BUS_PHONE]
                          ,cu.[EXT]
                        FROM [AR].[CUST] cu, 
                             [CUSTOM].[CUST_OTHER] co, 
                             [AR].[CUST_CR] cc
                        WHERE cu.CUST_CD = co.CUST_CD 
                            AND cu.CUST_CD = cc.CUST_CD
                            AND cu.DOB IS NOT NULL'



EXECUTE (@CREDIT_CUST_SQL) AT LIVE_BD;

And I get this error,

"ORA-01747: invalid user.table.column, table.column, or column specification"

Any idea why is happening. Thanks.

Upvotes: 0

Views: 13162

Answers (1)

jachguate
jachguate

Reputation: 17203

Your code looks like SQL Server

Do not use the brackets ([ ]) to quote the column names, just leave it as is for the capitalized valid identifiers or use the (sql standard) double quotes to the non valid identifiers or non capitalized ones.

Like this:

SELECT 
                       cu.ST_CD
                      ,cu.SRT_CD
                      ,cu.TITLE
                      ,cu.FNAME
                      ,cu.INIT
                      ,cu.LNAME
                      ,cu.ADDR1
                      ,cu.ADDR2
                      ,cu.CITY
                      ,cu.COUNTRY
                      ,cu.ZIP_CD
                      ,cu.HOME_PHONE
                      ,cu.BUS_PHONE
                      ,cu.EXT
                    FROM AR.CUST cu, 
                         CUSTOM.CUST_OTHER co, 
                         AR.CUST_CR cc
                    WHERE cu.CUST_CD = co.CUST_CD 
                        AND cu.CUST_CD = cc.CUST_CD
                        AND cu.DOB IS NOT NULL

Upvotes: 4

Related Questions