ora-00936 in Oracle PT/SQL

[CevinMoses]

I am using Access 97 as a front end to an Oracle server in order to customize
the information I can get out of it to create better reports than what the
availalbe frontend offers.

My pass-through queries have worked well, but due to my lack of experience
with complex queries, I have been writing queries that reference queries that
reference queries, rather than one complex query that gets it all done in one
shot. I have been trying to learn to write one, but with little success,
though I did pull off a pass-through union query seen below as subquery2. I
have dumbed them down more and more and still get the same error:
[Oracle][ODBC][Ora]ORA-00936: missing expression (#936)

Rather than give you the dumbed down version, here is the full code. I
thought it best to build two subqueries in the WHERE clause. The first
selects data relavent to when the products were made. The second subquery
combines two tables of grading data, one table stores data for pieces that
are acceptable, and the other table stores data for those that are not.
Together, they make up data with the grading results each piece receives on
the inspection end prior to being put in a box and shipped out. Both
subquery1 and subquery2 have successfully worked on their own, and I have
used a local query in MS-Access to join the two. However, I would much
rather have the Oracle server do the processing for me.

SELECT
subquery1.NP_SERIAL,
subquery1.NC_CLOCK,
subquery1.NC_FOREMAN_CLOCK,
subquery1.DP_CAST,
subquery1.DATEPART('h',[DP_CAST]),
subquery1.NC_SHIFT,
subquery1.CP_JOB_LOCATION,
subquery1.NP_PART,
subquery2.[INSPECTION_DATE],
subquery2.[GRADE],
subquery2.[DEFECT]

FROM
(SELECT
NP_SERIAL,
NC_CLOCK,
NC_FOREMAN_CLOCK,
DP_CAST,
NC_SHIFT,
CP_JOB_LOCATION,
NP_PART

FROM
TBVN66

WHERE
(CP_JOB_LOCATION LIKE 'MC7') and
(np_part Like '%2214%') and
(dp_cast between to_date('120106', 'mmddyy') and to_date('010107',
'mmddyy'))) subquery1,

(SELECT
NP_SERIAL,
DP_STOCK_CHANGE AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN62

WHERE
(NP_PART LIKE '%2214%') AND
(DP_STOCK_CHANGE > TO_DATE('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

UNION

SELECT
NP_SERIAL,
DP_CULL AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN58

WHERE
(np_part Like '%2214%') AND
(DP_CULL > to_date('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')) subquery2

WHERE
subquery1.NP_SERIAL = subquery2.NP_SERIAL

Please let me know what more I can provide that will help.

Thanks in advance!
Cevin

 

[Smartin]

I am using Access 97 as a front end to an Oracle server in order to customize
the information I can get out of it to create better reports than what the
availalbe frontend offers.

My pass-through queries have worked well, but due to my lack of experience
with complex queries, I have been writing queries that reference queries that
reference queries, rather than one complex query that gets it all done in one
shot. I have been trying to learn to write one, but with little success,
though I did pull off a pass-through union query seen below as subquery2. I
have dumbed them down more and more and still get the same error:
[Oracle][ODBC][Ora]ORA-00936: missing expression (#936)

Rather than give you the dumbed down version, here is the full code. I
thought it best to build two subqueries in the WHERE clause. The first
selects data relavent to when the products were made. The second subquery
combines two tables of grading data, one table stores data for pieces that
are acceptable, and the other table stores data for those that are not.
Together, they make up data with the grading results each piece receives on
the inspection end prior to being put in a box and shipped out. Both
subquery1 and subquery2 have successfully worked on their own, and I have
used a local query in MS-Access to join the two. However, I would much
rather have the Oracle server do the processing for me.

I don't immediately see the cause of the error, but I'm no Oracle wiz
(or any other wiz for that matter).

[Could you explain what this does?
LIKE '!_%' ESCAPE '!'
]

Nevertheless, there are a couple things I would change about the query
structure.

First, remove subquery1 -- it only references one table and has nothing
special going on. Move the constraints to the main WHERE clause.

Second, put the NP_SERIAL join in each part of subquery2. I think this
will substantially reduce the work done in subquery2.

Note: I left 'subquery1' in place as an alias to reduce the amount of
code that needs to be reworded, but it's not a subquery any more, just
an alias:

SELECT
subquery1.NP_SERIAL,
subquery1.NC_CLOCK,
subquery1.NC_FOREMAN_CLOCK,
subquery1.DP_CAST,
subquery1.DATEPART('h',[DP_CAST]),
subquery1.NC_SHIFT,
subquery1.CP_JOB_LOCATION,
subquery1.NP_PART,
subquery2.[INSPECTION_DATE],
subquery2.[GRADE],
subquery2.[DEFECT]

FROM
TBVN66 subquery1,

/* stuff moved to main WHERE clause */

(SELECT
NP_SERIAL,
DP_STOCK_CHANGE AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN62

WHERE
(NP_PART LIKE '%2214%') AND
(DP_STOCK_CHANGE > TO_DATE('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

UNION

SELECT
NP_SERIAL,
DP_CULL AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN58

WHERE
(np_part Like '%2214%') AND
(DP_CULL > to_date('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

) subquery2

WHERE
/* no longer necessary:
subquery1.NP_SERIAL = subquery2.NP_SERIAL
AND
*/
(CP_JOB_LOCATION LIKE 'MC7') and
(np_part Like '%2214%') and
(dp_cast between to_date('120106', 'mmddyy') and to_date('010107',
'mmddyy'));


Hoping this helps...

 

[CevinMoses]

As someone new to writing SQL, I greatly appreciate the advice as far as
structure goes. I'll try it tonight and see if it helps at all. (I work 3rd
shift.)

The Escape clause deals with the underscore found as the first character in
the [CP_POSTING_EVENT], which is a 3 character code, if the piece has not
been inspected before. Those are the only records I want. Oracle uses "_"
as a wildcard character, so I used the Escape function to tell Oracle to
ignore it as a wildcard and treat it as a character.

Thanks again, I'll repost the code to this same discussion string once I
clean it up, along with the results in case someone can help figure out where
the Oracle error is.

-Cevin

I am using Access 97 as a front end to an Oracle server in order to customize
the information I can get out of it to create better reports than what the
availalbe frontend offers.

My pass-through queries have worked well, but due to my lack of experience
with complex queries, I have been writing queries that reference queries that
reference queries, rather than one complex query that gets it all done in one
shot. I have been trying to learn to write one, but with little success,
though I did pull off a pass-through union query seen below as subquery2. I
have dumbed them down more and more and still get the same error:
[Oracle][ODBC][Ora]ORA-00936: missing expression (#936)

Rather than give you the dumbed down version, here is the full code. I
thought it best to build two subqueries in the WHERE clause. The first
selects data relavent to when the products were made. The second subquery
combines two tables of grading data, one table stores data for pieces that
are acceptable, and the other table stores data for those that are not.
Together, they make up data with the grading results each piece receives on
the inspection end prior to being put in a box and shipped out. Both
subquery1 and subquery2 have successfully worked on their own, and I have
used a local query in MS-Access to join the two. However, I would much
rather have the Oracle server do the processing for me.

I don't immediately see the cause of the error, but I'm no Oracle wiz
(or any other wiz for that matter).

[Could you explain what this does?
LIKE '!_%' ESCAPE '!'
]

Nevertheless, there are a couple things I would change about the query
structure.

First, remove subquery1 -- it only references one table and has nothing
special going on. Move the constraints to the main WHERE clause.

Second, put the NP_SERIAL join in each part of subquery2. I think this
will substantially reduce the work done in subquery2.

Note: I left 'subquery1' in place as an alias to reduce the amount of
code that needs to be reworded, but it's not a subquery any more, just
an alias:

SELECT
subquery1.NP_SERIAL,
subquery1.NC_CLOCK,
subquery1.NC_FOREMAN_CLOCK,
subquery1.DP_CAST,
subquery1.DATEPART('h',[DP_CAST]),
subquery1.NC_SHIFT,
subquery1.CP_JOB_LOCATION,
subquery1.NP_PART,
subquery2.[INSPECTION_DATE],
subquery2.[GRADE],
subquery2.[DEFECT]

FROM
TBVN66 subquery1,

/* stuff moved to main WHERE clause */

(SELECT
NP_SERIAL,
DP_STOCK_CHANGE AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN62

WHERE
(NP_PART LIKE '%2214%') AND
(DP_STOCK_CHANGE > TO_DATE('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

UNION

SELECT
NP_SERIAL,
DP_CULL AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN58

WHERE
(np_part Like '%2214%') AND
(DP_CULL > to_date('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

) subquery2

WHERE
/* no longer necessary:
subquery1.NP_SERIAL = subquery2.NP_SERIAL
AND
*/
(CP_JOB_LOCATION LIKE 'MC7') and
(np_part Like '%2214%') and
(dp_cast between to_date('120106', 'mmddyy') and to_date('010107',
'mmddyy'));


Hoping this helps...

 

[CevinMoses]

Nope, still had the ORA-00936 error, though I see what your changes did to
the code. Thanks for the design help to clean it up a bit.

-Cevin

I am using Access 97 as a front end to an Oracle server in order to customize
the information I can get out of it to create better reports than what the
availalbe frontend offers.

My pass-through queries have worked well, but due to my lack of experience
with complex queries, I have been writing queries that reference queries that
reference queries, rather than one complex query that gets it all done in one
shot. I have been trying to learn to write one, but with little success,
though I did pull off a pass-through union query seen below as subquery2. I
have dumbed them down more and more and still get the same error:
[Oracle][ODBC][Ora]ORA-00936: missing expression (#936)

Rather than give you the dumbed down version, here is the full code. I
thought it best to build two subqueries in the WHERE clause. The first
selects data relavent to when the products were made. The second subquery
combines two tables of grading data, one table stores data for pieces that
are acceptable, and the other table stores data for those that are not.
Together, they make up data with the grading results each piece receives on
the inspection end prior to being put in a box and shipped out. Both
subquery1 and subquery2 have successfully worked on their own, and I have
used a local query in MS-Access to join the two. However, I would much
rather have the Oracle server do the processing for me.

I don't immediately see the cause of the error, but I'm no Oracle wiz
(or any other wiz for that matter).

[Could you explain what this does?
LIKE '!_%' ESCAPE '!'
]

Nevertheless, there are a couple things I would change about the query
structure.

First, remove subquery1 -- it only references one table and has nothing
special going on. Move the constraints to the main WHERE clause.

Second, put the NP_SERIAL join in each part of subquery2. I think this
will substantially reduce the work done in subquery2.

Note: I left 'subquery1' in place as an alias to reduce the amount of
code that needs to be reworded, but it's not a subquery any more, just
an alias:

SELECT
subquery1.NP_SERIAL,
subquery1.NC_CLOCK,
subquery1.NC_FOREMAN_CLOCK,
subquery1.DP_CAST,
subquery1.DATEPART('h',[DP_CAST]),
subquery1.NC_SHIFT,
subquery1.CP_JOB_LOCATION,
subquery1.NP_PART,
subquery2.[INSPECTION_DATE],
subquery2.[GRADE],
subquery2.[DEFECT]

FROM
TBVN66 subquery1,

/* stuff moved to main WHERE clause */

(SELECT
NP_SERIAL,
DP_STOCK_CHANGE AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN62

WHERE
(NP_PART LIKE '%2214%') AND
(DP_STOCK_CHANGE > TO_DATE('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

UNION

SELECT
NP_SERIAL,
DP_CULL AS "INSPECTION_DATE",
CP_GRADE AS "GRADE",
CP_DEFECT AS "DEFECT",
NP_PART,
CP_POSTING_EVENT,
NP_REWORK_CYCLE,
QP_QUANTITY

FROM
TBVN58

WHERE
(np_part Like '%2214%') AND
(DP_CULL > to_date('120106', 'mmddyy')) AND
(NP_REWORK_CYCLE = '0') AND
(QP_QUANTITY = '1') AND
(CP_POSTING_EVENT LIKE '!_%' ESCAPE '!')

/* NEW LINE: */
AND subquery1.NP_SERIAL = NP_SERIAL

) subquery2

WHERE
/* no longer necessary:
subquery1.NP_SERIAL = subquery2.NP_SERIAL
AND
*/
(CP_JOB_LOCATION LIKE 'MC7') and
(np_part Like '%2214%') and
(dp_cast between to_date('120106', 'mmddyy') and to_date('010107',
'mmddyy'));


Hoping this helps...