Left function using openquery

• carol-217590

  SSC Veteran

  Points: 207

  More actions

  August 7, 2007 at 10:40 am

  #70588

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

• Joe Clifford

  SSCrazy Eights

  Points: 8739

  More actions

  August 7, 2007 at 11:57 pm

  #725352

  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

• Joel Ewald

  SSCertifiable

  Points: 6120

  More actions

  August 8, 2007 at 10:43 am

  #725492

  try to use substring instead of left

• steve smith-401573

  SSChampion

  Points: 10889

  More actions

  August 10, 2007 at 11:19 am

  #726121

  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.