Very Computer

SAS-L,

SAS version 8.2, Oracle version 8.x.  Desktop: Windoze 2000.  I do not know
what OS is on the remote Oracle server but probably Unix.

A section of code follows.  In the first submission I have commented out all
references to one table and the code runs correctly.  In the second section
I have commented out the references to the second table. This code also runs
correctly.  In the third section I have removed the comments so the two
tables can be joined.  The error message, which I have not seen before,
indicates that the SQL command was not properly ended.  What does this mean
and how can I repair it.

Thanks for you help,

Rodney

121  proc sql;
122     connect to oracle(user='rpresley'

124     create table  dispptb as
125     select * from connection to oracle
126     (SELECT coverage.cvrage_type as covtype,
127     coverage.cvrage_start_dt as startdt,
128     coverage.cvrage_termination_dt as termindt/*,
129     bene_migration.bene_unique_id as uniqid,
130     bene_migration.state_code as state,
131     bene_migration.bene_county_cd as county    */
132     FROM  /*bene_migration,*/ coverage
133     WHERE  /*bene_migration.state_code=  */
134                       coverage.state_code='GA'
135  /* and coverage.bene_unique_id = bene_migration.bene_unique_id
136      and bene_migration.resid_chg_dt <= '03/31/2002'  */
137  /* and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')
137!   */
138  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */

139  /* and    beneficiary.bene_race_cd = '2'   */
140     and coverage.cvrage_start_dt = '04/01/2001'
141      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
141! >='03/31/2002')
142     and coverage.cvrage_type IN ('PTB')  )
143     ;
NOTE: Table WORK.DISPPTB created, with 5080 rows and 3 columns.

144  quit;
NOTE: PROCEDURE SQL used:
      real time           52.29 seconds
      cpu time            0.10 seconds

*******************************************************

145  proc sql;
146     connect to oracle(user='rpresley'

148     create table  dispptb as
149     select * from connection to oracle
150     (SELECT /*coverage.cvrage_type as covtype,
151     coverage.cvrage_start_dt as startdt,
152     coverage.cvrage_termination_dt as termindt, */
153     bene_migration.bene_unique_id as uniqid,
154     bene_migration.state_code as state,
155     bene_migration.bene_county_cd as county
156     FROM  bene_migration /*, coverage   */
157     WHERE  bene_migration.state_code=
158                       /*coverage.state_code=*/ 'GA'
159  /* and coverage.bene_unique_id = bene_migration.bene_unique_id */
160      and bene_migration.resid_chg_dt <= '03/31/2002'
161     and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')

162  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */

163  /* and    beneficiary.bene_race_cd = '2'   */
164  /* and coverage.cvrage_start_dt = '04/01/2001'
165      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
165! >='03/31/2002')
166     and coverage.cvrage_type IN ('PTB') */ )
167     ;
NOTE: Table WORK.DISPPTB created, with 994158 rows and 3 columns.

168  quit;
NOTE: PROCEDURE SQL used:
      real time           12:03.34
      cpu time            4.70 seconds

***********************************************

169  proc sql;
170     connect to oracle(user='rpresley'

172     create table  dispptb as
173     select * from connection to oracle
174     (SELECT coverage.cvrage_type as covtype,
175     coverage.cvrage_start_dt as startdt,
176     coverage.cvrage_termination_dt as termindt/*,
177     bene_migration.bene_unique_id as uniqid,
178     bene_migration.state_code as state,
179     bene_migration.bene_county_cd as county    */
180     FROM  bene_migration, coverage
181     WHERE  bene_migration.state_code=
182                       coverage.state_code='GA'
183     and coverage.bene_unique_id = bene_migration.bene_unique_id
184      and bene_migration.resid_chg_dt <= '03/31/2002'
185     and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')

186  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null)

187     and    beneficiary.bene_race_cd = '2'   */
188     and coverage.cvrage_start_dt = '04/01/2001'
189      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
189! >='03/31/2002')
190     and coverage.cvrage_type IN ('PTB')  )
191     ;
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. SQL
statement: SELECT
       coverage.cvrage_type as covtype, coverage.cvrage_start_dt as startdt,
       coverage.cvrage_termination_dt as termindt FROM bene_migration,
coverage WHERE
       bene_migration.state_code= coverage.state_code='GA' and
coverage.bene_unique_id =
       bene_migration.bene_unique_id and bene_migration.resid_chg_dt <=
'03/31/2002' and
       (bene_migration.resid_end_dt IS NULL or bene_migration.resid_end_dt

Quote:>= '03/31/2002') and

       coverage.cvrage_start_dt = '04/01/2001' and
(coverage.cvrage_termination_dt is null or
       coverage.cvrage_termination_dt >='03/31/2002') and
coverage.cvrage_type IN ('PTB'). 192  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
      real time           1.31 seconds
      cpu time            0.01 seconds

Rodney J. Presley, Ph.D.
Georgia Medical Care Foundation
1455 Lincoln Parkway
suite 800
Atlanta, GA  30346
678 - 527 - 3474 voice
678-527-3574  fax

Rodney:

  One problem with the last run is the ','  which you have commented out.

  Try putting ',' after  termindt and run it again.

Prasad Ravi.

proc sql;
170     connect to oracle(user='rpresley'
171           orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
172     create table  dispptb as
173     select * from connection to oracle
174     (SELECT coverage.cvrage_type as covtype,
175     coverage.cvrage_start_dt as startdt,
176     coverage.cvrage_termination_dt as termindt/*,
                                                 ^^^^^^^^

177     bene_migration.bene_unique_id as uniqid,
178     bene_migration.state_code as state,
179     bene_migration.bene_county_cd as county    */

Prasad Ravi

                         rpresley
                         <rpres...@GMCF.ORG>    To:    SA...@LISTSERV.UGA.EDU
                         Sent by: "SAS(r)       cc:
                         Discussion"            Subject: PROC SQL pass through to Oracle
                         <SA...@LISTSERV.UGA.E
                         DU>

                         07/08/2003 10:02 AM
                         Please respond to
                         rpresley

SAS-L,

SAS version 8.2, Oracle version 8.x.  Desktop: Windoze 2000.  I do not know
what OS is on the remote Oracle server but probably Unix.

A section of code follows.  In the first submission I have commented out
all
references to one table and the code runs correctly.  In the second section
I have commented out the references to the second table. This code also
runs
correctly.  In the third section I have removed the comments so the two
tables can be joined.  The error message, which I have not seen before,
indicates that the SQL command was not properly ended.  What does this mean
and how can I repair it.

Thanks for you help,

Rodney

121  proc sql;
122     connect to oracle(user='rpresley'
123           orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
124     create table  dispptb as
125     select * from connection to oracle
126     (SELECT coverage.cvrage_type as covtype,
127     coverage.cvrage_start_dt as startdt,
128     coverage.cvrage_termination_dt as termindt/*,
129     bene_migration.bene_unique_id as uniqid,
130     bene_migration.state_code as state,
131     bene_migration.bene_county_cd as county    */
132     FROM  /*bene_migration,*/ coverage
133     WHERE  /*bene_migration.state_code=  */
134                       coverage.state_code='GA'
135  /* and coverage.bene_unique_id = bene_migration.bene_unique_id
136      and bene_migration.resid_chg_dt <= '03/31/2002'  */
137  /* and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')
137!   */
138  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */

139  /* and    beneficiary.bene_race_cd = '2'   */
140     and coverage.cvrage_start_dt = '04/01/2001'
141      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
141! >='03/31/2002')
142     and coverage.cvrage_type IN ('PTB')  )
143     ;
NOTE: Table WORK.DISPPTB created, with 5080 rows and 3 columns.

144  quit;
NOTE: PROCEDURE SQL used:
      real time           52.29 seconds
      cpu time            0.10 seconds

*******************************************************

145  proc sql;
146     connect to oracle(user='rpresley'
147           orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
148     create table  dispptb as
149     select * from connection to oracle
150     (SELECT /*coverage.cvrage_type as covtype,
151     coverage.cvrage_start_dt as startdt,
152     coverage.cvrage_termination_dt as termindt, */
153     bene_migration.bene_unique_id as uniqid,
154     bene_migration.state_code as state,
155     bene_migration.bene_county_cd as county
156     FROM  bene_migration /*, coverage   */
157     WHERE  bene_migration.state_code=
158                       /*coverage.state_code=*/ 'GA'
159  /* and coverage.bene_unique_id = bene_migration.bene_unique_id */
160      and bene_migration.resid_chg_dt <= '03/31/2002'
161     and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')

162  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null) */

163  /* and    beneficiary.bene_race_cd = '2'   */
164  /* and coverage.cvrage_start_dt = '04/01/2001'
165      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
165! >='03/31/2002')
166     and coverage.cvrage_type IN ('PTB') */ )
167     ;
NOTE: Table WORK.DISPPTB created, with 994158 rows and 3 columns.

168  quit;
NOTE: PROCEDURE SQL used:
      real time           12:03.34
      cpu time            4.70 seconds

***********************************************

169  proc sql;
170     connect to oracle(user='rpresley'
171           orapw=XXXXXXXXXX path='@tns:c1s80_SDPS');
172     create table  dispptb as
173     select * from connection to oracle
174     (SELECT coverage.cvrage_type as covtype,
175     coverage.cvrage_start_dt as startdt,
176     coverage.cvrage_termination_dt as termindt/*,
177     bene_migration.bene_unique_id as uniqid,
178     bene_migration.state_code as state,
179     bene_migration.bene_county_cd as county    */
180     FROM  bene_migration, coverage
181     WHERE  bene_migration.state_code=
182                       coverage.state_code='GA'
183     and coverage.bene_unique_id = bene_migration.bene_unique_id
184      and bene_migration.resid_chg_dt <= '03/31/2002'
185     and (bene_migration.resid_end_dt IS NULL or
bene_migration.resid_end_dt >= '03/31/2002')

186  /* and    (beneficiary.bene_death_dt >'03/31/2002' or
beneficiary.bene_death_dt is null)

187     and    beneficiary.bene_race_cd = '2'   */
188     and coverage.cvrage_start_dt = '04/01/2001'
189      and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt
189! >='03/31/2002')
190     and coverage.cvrage_type IN ('PTB')  )
191     ;
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended. SQL
statement: SELECT
       coverage.cvrage_type as covtype, coverage.cvrage_start_dt as
startdt,
       coverage.cvrage_termination_dt as termindt FROM bene_migration,
coverage WHERE
       bene_migration.state_code= coverage.state_code='GA' and
coverage.bene_unique_id =
       bene_migration.bene_unique_id and bene_migration.resid_chg_dt <=
'03/31/2002' and
       (bene_migration.resid_end_dt IS NULL or bene_migration.resid_end_dt

       coverage.cvrage_start_dt = '04/01/2001' and
(coverage.cvrage_termination_dt is null or
       coverage.cvrage_termination_dt >='03/31/2002') and
coverage.cvrage_type IN ('PTB'). 192  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used:
      real time           1.31 seconds
      cpu time            0.01 seconds

Rodney J. Presley, Ph.D.
Georgia Medical Care Foundation
1455 Lincoln Parkway
suite 800
Atlanta, GA  30346
678 - 527 - 3474 voice
678-527-3574  fax

Rodney:

   You are right if it is the last variable you don't need one,     oh! man
so many comments it is confusing,

hey I noticed another thing in your code,  I don't think this is allowed.

WHERE  bene_migration.state_code=
           coverage.state_code='GA'
^^^^^^^^^^

Prasad Ravi

                         rpresley
                         <rpres...@gmcf.org>    To:    'Prasad S Ravi' <prasad.s.r...@HOUSEHOLD.COM>
                                                cc:
                         07/08/2003 10:35 AM    Subject: RE: PROC SQL pass through to Oracle

Prasad,

Thanks for responding.

I have commented out the comma because TERMINDT is the last variable that
is
being used in the last submission of PROC SQL.  Is a comma needed after the
last variable in a select statement?

Rodney

The WHERE conditions look OK to me. Improved formatting makes them easier to
read:

    bene_migration.state_code= coverage.state_code='GA'
and coverage.bene_unique_id = bene_migration.bene_unique_id
and bene_migration.resid_chg_dt <='03/31/2002'
and (bene_migration.resid_end_dt IS NULL or bene_migration.resid_end_dt>=
'03/31/2002')
and coverage.cvrage_start_dt = '04/01/2001'
and (coverage.cvrage_termination_dt is null or
coverage.cvrage_termination_dt >='03/31/2002')
and coverage.cvrage_type IN ('PTB')

The log has an error code at the point in the program where you have an
extra closing parenthesis. That would be consistent with the the diagnostic
message from Oracle:
'SQL command not properly ended'.

Sig