Its /*+ not /* +

Don

autretx@MY-DEJA.COM wrote:

> My SQL statements or my SAS Code is not inside a macro but run in batch > mode. > > Interactively I use the preserve keyword inside the connection to > Oracle. > > And SAS passing /* + hints */ to Oracle Optimizer. > > Now what I don t understand that s Oracle doesn t use the optimizer > through SAS, a bug in the SQL Net connection? > > When the select is passed under sql+ (whithout /* + hint*/) the explain > plan of Oracle show that the optimizer works. > > With SAS it doesn t... > > Seem to be an uncorrect Oracle Statement no? > > In article > <6944FBE0F687D411823D00508B6095FA5015CF@aaunderdog.research.aa.wl.com>, > "Brucken, Nancy" <Nancy.Brucken@PFIZER.COM> wrote: > > Hi Xavier, > > If your PROC SQL is inside of a macro, then you're right, SAS > thinks the > > hint is a comment. A workaround is this: > > > > select %str(/)%str(*)+ use_hash( PP,RPP,CG,ADR,PRN) %str(*)%str(/) > > > > PP.NOM_PAT as nom > > ,PP.PNOM_OFF as prenom > > ,PP.CT_PERSONNE_PHYSIQUE as ct_perso > > ,PP.CT_FOYER_MKT as ct_foyer > > ,PP.CD_PRIO_FOY as prio_foy > > ,CG.NUM_IDENT_CG as num_cli > > ,CG.CD_GUI_CLI as cod_gui > > ,CG.CD_ACT_ECO as cod_eco > > ,CG.CD_CAT_PROF as cod_csp > > ,CG.CD_SOGETYPE as cod_soge > > ,ADR.CD_POSTAL_ADR as cod_pos > > > > from TB_PERSONNE_PHYSIQUE PP > > ,TA_RELATION_CG_PP RPP > > ,TB_CLIENT_GUICHET CG > > ,TB_ADRESSE ADR > > ,TB_PRESTATION PRN > > > > where (PP.CT_PERSONNE_PHYSIQUE = RPP.CT_PERSONNE_PHYSIQUE and > > RPP.CT_CLIENT_GUICHET = CG.CT_CLIENT_GUICHET and > > CG.CT_CLIENT_GUICHET = ADR.CT_CLIENT_GUICHET and > > PRN.CT_CLIENT_GUICHET = CG.CT_CLIENT_GUICHET and > > > > PRN.CD_PRD in (50, 51) and > > PRN.TP_PRN_ACTIVE in (0, 1) and > > PRN.TP_PRN_INDS = 0 and > > PRN.TP_PRN_PURGE = 0 and > > > > PP.dat_nais between to_date('01011901', 'DDMMYYYY') and > > to_date('01011985', 'DDMMYYYY') and > > > > CG.CD_SEG_MAR in ('144000','02400','04700' ) and > > > > RPP.CD_ETA_REL_CG_PP =1 > > PP.CD_ETA_PP = 1 > > ) > > > > Note that for Oracle to use the hint, it must be included in the > > pass-through code (the stuff inside of the parentheses after the FROM > > CONNECTION TO ORACLE statement). Putting it on the SAS side of the > > pass-through code won't help. > > Nancy > > > > Nancy Brucken > > Clinical Informatics > > Pfizer Global Research & Development, Ann Arbor > > (734) 622-5767 > > E-mail address: Nancy.Brucken@pfizer.com > > Sent via Deja.com http://www.deja.com/ > Before you buy.

-- Don Stanley, B.SC, Dip O.R.S, MNZCS Director, Sysware Consulting Group Box 634, Wellington, NEW ZEALAND http://www.sysware.co.nz EMAIL:: don_stanley@xtra.co.nz

Author:: Beyond the obvious with SAS Screen Control Language. Author:: Solutions for your GUI Applications Development Using SAS/AF FRAME Technology

http://www.geocities.com/don_stanley_nz/don_home.htm Genealogy:: http://www.geocities.com/don_stanley_nz/family.htm SAS Tips:: http://www.geocities.com/don_stanley_nz/sastips.htm