Here is the Log for the error it produced... 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code); 4 %LET _EGTASKLABEL=%NRBQUOTE(Code); 5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(C:\Users\Work\Documents\SAS Projects\DENY_REPORT_CODE.egp); 6 %LET _SASPROGRAMFILE=; 7 %LET Start_Date = '27Feb2012'd; 8 %LET End_Date = '27Feb2012'd; 9 10 ODS _ALL_ CLOSE; NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices. Graph defaults for these drivers may be different from other SAS/GRAPH device drivers. For further information, please contact Technical Support. 11 OPTIONS DEV=ACTIVEX; 12 FILENAME EGHTML TEMP; NOTE: Writing HTML(EGHTML) Body file: EGHTML 13 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault 13 ! STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS/Shared%20Files/BIClientStyles/EGDefault.css") 13 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation 13 ! ; 14 15 %gaccessible; 16 data _null_; 17 call symput("Start_Date",put(&START_DATE,date9.)); 18 call symput("End_Date",put(&END_DATE,date9.)); 19 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 20 21 %put Start Date Equal To: &START_DATE; Start Date Equal To: 27FEB2012 22 %put End Date Equal To: &END_DATE; End Date Equal To: 27FEB2012 23 24 Proc SQL noprint; 25 connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments); 26 create table work.TEMP_DZ01 as select * from connection to oracle 27 ( SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO) */ 28 distinct AO.ODR_NUMBER, 29 AMQ.COMPLETED_DATE, 30 AQ.ID, 31 AM.bmt_shop, 32 (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as 32 ! MERCH_AMOUNT, 33 AO.service_charge_amount, 34 AO.tax_amount, 35 AO.total_amount, 36 MS.PRICE_CHANGE_CODE, 37 MS.CHANGED_PRICE, 38 AMQ.usr_mdfd 39 case 40 when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + 2 The SAS System 14:51 Tuesday, February 28, 2012 40 ! AO.manual_gift_cert_amount) then changed_price AS Full_Payment 41 when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + 41 ! AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + 41 ! AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment 42 else ' ' end 43 44 45 FROM ATS_QUEUES AQ, 46 ATS_MESSAGE_QUEUES AMQ, 47 ATS_MESSAGES AM, 48 ATS_ORDERS AO , 49 ATS_CSIS CS, 50 ATS_CSI_QUEUES CQ , 51 ATS_MESSAGES MS 52 WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy')) 53 AND AMQ.COMPLETED_DATE < trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1) 54 AND AO.ODR_NUMBER = AM.ODR_ODR_NUMBER 55 AND AO.VERSION = AM.ODR_VERSION 56 AND AM.ID = AMQ.MSG_ID 57 AND AMQ.QQQ_ID = AQ.ID 58 AND AQ.QUEUE_TYPE = 'DEN' 59 AND AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+) 60 AND AO.VERSION = CS.ODR_VERSION(+) 61 AND CS.ID = CQ.CSI_ID :smileyplus: 62 AND AO.ODR_NUMBER = MS.ODR_ODR_NUMBER 63 AND MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER 64 AND AO.VERSION = MS.ODR_VERSION 65 AND MS.MSG_TYPE IN ('ASK','CAN','ANS') /* For the price change infotmation - the price change code is 'P' 66 and the message_type in ('ASK','ANS') and also the direction = 'O'. 67 Not sure why you need 'CAN' in the filter*/ 68 AND MS.DIRECTION = 'O' 69 AND MS.ID > AM.ID 70 AND MS.FIT_ID = AM.FIT_Id 71 ); ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO) */ distinct AO.ODR_NUMBER, AMQ.COMPLETED_DATE, AQ.ID, AM.bmt_shop, (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as MERCH_AMOUNT, AO.service_charge_amount, AO.tax_amount, AO.total_amount, MS.PRICE_CHANGE_CODE, MS.CHANGED_PRICE, AMQ.usr_mdfd case when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then changed_price AS Full_Payment when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment else ' ' end FROM ATS_QUEUES AQ, ATS_MESSAGE_QUEUES AMQ, ATS_MESSAGES AM, ATS_ORDERS AO , ATS_CSIS CS, ATS_CSI_QUEUES CQ , ATS_MESSAGES MS WHERE AMQ.COMPLETED_DATE >= trunc(to_date('27FEB2012','ddmonyyyy')) AND AMQ.COMPLETED_DATE < trunc(to_date('27FEB2012','ddmonyyyy')+1) AND AO.ODR_NUMBER = AM.ODR_ODR_NUMBER AND AO.VERSION = AM.ODR_VERSION AND AM.ID = AMQ.MSG_ID AND AMQ.QQQ_ID = AQ.ID AND AQ.QUEUE_TYPE = 'DEN' AND AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+) AND AO.VERSION = CS.ODR_VERSION(+) AND CS.ID = CQ.CSI_ID :smileyplus: AND AO.ODR_NUMBER = MS.ODR_ODR_NUMBER AND MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER AND AO.VERSION = MS.ODR_VERSION AND MS.MSG_TYPE IN ('ASK','CAN','ANS') /* For the price change infotmation - the price change code is 'P' and the message_type in ('ASK','ANS') and also the direction = 'O'. Not sure why you need 'CAN' in the filter*/ AND MS.DIRECTION = 'O' AND MS.ID > AM.ID AND MS.FIT_ID = AM.FIT_Id. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 72 73 Disconnect from Oracle; NOTE: Statement not executed due to NOEXEC option. 74 Quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds 3 The SAS System 14:51 Tuesday, February 28, 2012 cpu time 0.01 seconds 75 76 77 78 %LET _CLIENTTASKLABEL=; 79 %LET _EGTASKLABEL=; 80 %LET _CLIENTPROJECTNAME=; 81 %LET _SASPROGRAMFILE=; 82 83 ;*';*";*/;quit;run; 84 ODS _ALL_ CLOSE; 85 86 87 QUIT; RUN;
... View more
