August 7, 2007 at 10:40 am
Hello,
I am hoping someone knows why I get this error when I use the left function in openquery to connect to an Oracle database.
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "LEFT": invalid identifier
]
Here is the select statement that gives me the error. If I remove the entire case portion, it works. Please help. Thanks.
SET QUOTED_IDENTIFIER OFF
SELECT * from openquery(prod185, "select AEI_SITE_RULE_SYMB.SITE_SEQ, AEI_SITE_RULE_SYMB.SITE_NBR, AEI_SITE_RULE_SYMB.RULE_NBR,
TRN_SYMB_PTRN_TEXT, EXCL_IND,AEI_SITE_RULE_SYMB.RPTG_TYPE,
TRN_DIR, UPD_100, TRN_MOVE_CD, EXCL_DIR_CD,
EXCL_TRN_CATG_CD, EXCL_SVC_TYPE_CD,ALTN_RPT_CRC7,
ADJM_MINS,
case
when left(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT, 1) = '%' and
right(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,1) = '%'
then left(right(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,
len(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT)-1),
len(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT)-2)
when left(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,1) = '%'
then right(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,
len(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT)-1)
when right(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,1) = '%'
then left(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,
len(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT)-1)
else AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT
end as trn_symbl,
case
when left(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,1) = '%'
then left(right(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,
len(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT)-1),1)
ELSE LEFT(AEI_SITE_RULE_SYMB.TRN_SYMB_PTRN_TEXT,1)
END,
AEI_SITE_MSTR.SITE_NAME, AEI_SITE_MSTR.ACTL_CRC7
FROM AEI_SITE_RULE INNER JOIN AEI_SITE_RULE_SYMB ON AEI_SITE_RULE.RULE_NBR = AEI_SITE_RULE_SYMB.RULE_NBR AND AEI_SITE_RULE.SITE_NBR = AEI_SITE_RULE_SYMB.SITE_NBR AND AEI_SITE_RULE.RPTG_TYPE = AEI_SITE_RULE_SYMB.RPTG_TYPE
INNER JOIN AEI_SITE_MSTR ON AEI_SITE_RULE.SITE_NBR = AEI_SITE_MSTR.SITE_NBR")
August 7, 2007 at 11:57 pm
Carol -
Openquery executes the specified query against the remote dataset - all of your SQL must be acceptable to the remote server. Best test - if you can execute the query using the oracle client (e.g. SQLPlus), if it works as a query in SQLPlus it can usually be run as a openquery statement once special characters, etc. are taken into account (e.g. apostrophes).
Joe
August 8, 2007 at 10:43 am
try to use substring instead of left
August 10, 2007 at 11:19 am
IF I remember correctly, to capture the 'RIGHT(value,1), you will need to use a 'SUBSTR(field,LEN(field)-1,1) to get the equivalent in SQL PLUS.
Are you attempting to use the '%' as a literal character or as a wild card character? I'm wondering if SQL Server is doing any form of preprocessing and impacting your use of '%'. It shouldn't, but that's a very dangerous verb to rely on.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply