Doubt SQL consultation in Oracle

[Galako]

Good morning,

I am sure where it is occurring when I apply below in the Oracle database.

Select all

  
SELECT H.MAQUINA, 
       PR.COD, 
       H.TURNO 
FROM HISTORICO, 
     (SELECT DISTINCT(P.ID) AS PID, P.CODIGO AS COD 
      FROM PRODUTO P, TEXTO_LOCALD TL 
      WHERE P.TEXTOID = TL.ID AND LINGUAGEM = 1) PR 
WHERE PR.PID = H.PRODUTOID 
AND   H.SEQUENCIA LIKE '0001%' 
AND   H.CLASSE = 1 
AND   H.DATA_TRAB = TO_DATE('2016-08-28','YYYY-MM-DD') 
AND   H.TURNO IN (1,2,3) 
AND   PR.COD IN (6383,4407,4485,4496,4501) 
GROUP BY H.MAQUINA,PR.COD,H.TURNO 
/ 

What happens, when he first consulted it usually performs, however, if it is again the same consultation. The error occurs ORA-01722: invalid number .

After analyzing I checked that product codes with letters were also registered in the product table and put apostrophes in this clause

Select all

PR.COD IN ('6383','4407','4485','4496','4501')

Oracle makes the query n times.

My question is the following, why Oracle performs the query the first time and the second time it occurs error? I made the query on other dates and some occurs the error and in others no error occurs.

The bank used is Oracle 11g.

[spernega]

Good afternoon,

If the Poduto COD field is not numeric, you need to inform the quotation marks.

I did not understand "Oracle makes the query n times."

Is this causing a Cartesian product?

[Galako]

Good afternoon,

If the Poduto COD field is not numeric, you need to inform the quotation marks.

I did not understand "Oracle makes the query n times."

Is this causing a Cartesian product? It is that I can run several times without any error occurring.

My doubt is still because the error or-01722 occurs only the second times that I will make the appointment? And there are some dates that I will make the query and the error does not occur.

Sorry if I expressed me evil before.

[spernega]

In fact it is weird to run at first and give mistake on Monday.

Are you using SQLPLUS, SQLDEVELOPER, ...? (Maybe this can mean something)

The only parameter you are changing is the date?

One more, productid of historic is the same data type of the product?

[Galako]

I am using SQLPLUS and PL \ SQL Developer, is Query runs in Crystal Reports that also occurs error.

The fields that are changed is h.data_trab and PR.COD, where in PR.COD step the codes available for the day, where I can choose only 1 or several.

Yes, the productID of the historic is from the same date type of the product.

[spernega]

Yeah, this is difficult.

The only thing I see that could influence is in relation to codes to have to be simple quotes.

Let's see if someone else's forum has any other solution.

[adrianoturbo]

Brother, it seems to me that you are storing numbers like strings in the conversion process.
I did not understand why to use a function to return the same value as in the column that is already defined with the date type, as follows:

Select all

 
AND   H.DATA_TRAB = TO_DATE('2016-08-28','YYYY-MM-DD') 

When it would be more practical to use the simple is safe:

Select all

 
AND   H.DATA_TRAB ='2016-08-28'  

OLAP Ferraments As is the case of Crystal Reports generally have difficulty understanding the NLS parameters.
In your specified case it is assigned precisely to NLS_Numeric_Characters, in the conversion process.
I believe that using the simple is safe, according to the code up there already would solve ... but there are other alternatives such as:
- Review the installation of the OLAP tool and set the NLS_NUMERIC_CHARACTERS option
In your own SELECT Insert the NLS_DATE_LANGUAGE parameter [

Select all

 
AND   H.DATA_TRAB = TO_DATE('2016-08-28','YYYY-MM-DD', 'nls_date_language = PORTUGUESE') 

and direct from the Glufke Time Tunnel We have other interesting solutions: http://en.glufke.net/oracle/viewtopic.php?t=6541

Good luck !!!

[Galako]

Oops, Adriano.

Thanks for the tips, however the dates were like this because I removed the SELECT directly from Crystal Reports and format them like that by default.

But this is not yet the point because the error does not only occur in Crystal Reports, is occurring in other tools and even changing the date formatting.

[adrianoturbo]

Brother, next ... so we can understand ... this query that is running is extracted directly from Crystal Reports, right? Type you go there in database, show sql query and generates a query of your report and runs it in sqlplus, sqldeveloper, among others and presents this error?
When you are trying to convert a string type in number, it may be that this string should not be converted correctly, apparently the Crystal is making a conversion different than the bank understands.
Try to do querys inside the bank without being the generated by Crystal and see if the error persists.

[geovani]

Oops!

My question is as follows, why Oracle performs the query the first time and the second time it occurs error? I made the query on other dates and some occurs the error and other the error occurs.

Suggestion:
The fact that in some dates the error occurs and In others, I understand that there is something to do with existing records. I suggest an analysis on a certain day that is giving error and checking the data it returns, will take the filters until you find the field that is giving the error "ORA-01722: invalid number."