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;
