SQL Script errors with ORA-01722: invalid number in EA v 13.5

[ArchBeast]

This SQL query ran ok in EA v13 but errors with 'OraOLEDB [-2147467259] ORA-01722: invalid number'
in EA v 13.5:

Select

--Look for an association class and list the source and target class of the association

-- The 'AS' clause needs to be followed by a simple string or eaDocX searches don't work

ifO.ea_guid AS CLASSGUID,
ifO.Object_Type As CLASSTYPE,


ifO.Alias As IF_ID,

-- Replace any commas in the interface name result so we don't confuse CSV!

Replace(ifO.Name, ',',';') AS IF_NAME,
ifO.Status As IF_STATUS,
ifO.PDATA5 As CAPABILITY,
srcO.Name AS SRC_SYS_NAME,
tgtO.Name AS TGT_SYS_NAME

FROM t_object tgtO , t_object srcO, t_connector, t_object ifO, t_package pkg

WHERE
-- These macros dont work in eaDocX searches - no results will be output
pkg.Package_ID in (#Branch#) AND ifO.Package_ID = pkg.Package_ID

AND tgtO.Object_ID = t_connector.End_Object_ID
AND srcO.Object_ID = t_connector.Start_Object_ID
AND t_connector.PDATA1 IS NOT NULL
AND ifO.Object_ID = To_Number(t_connector.PDATA1)
AND ifO.Stereotype like '%interface%'

ORDER by 1

Whoops it doesn't work in EA v 13 either, but it used too! Other scripts also work so not an OLDEB problem.

[Geert Bellekens]

I guess this is an SQL search and not a script.
First thing to do is check the dbError.txt in the %appdata% folder for EA.

Then remove the comments. IIRC EA will strip (or it used to) everything after the first comments, so your search will be nothing more then "SELECT"

Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Geert

[Simon M]

Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

I'll agree that this is very likely the problem, it would also explain why it used to work but now doesn't.

The other option is to filter the t_connector table to the Connector_Type you're interested in. I'm guessing 'Association'.

[Paolo F Cantoni]

I guess this is an SQL search and not a script.
First thing to do is check the dbError.txt in the %appdata% folder for EA.

Then remove the comments. IIRC EA will strip (or it used to) everything after the first comments, so your search will be nothing more then "SELECT"

Next thing is the To_Number(t_connector.PDATA1) there's a good chance you have somewhere a text in a PDATA1
I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Geert

To reinforce what Geert has said, don't forget that a NULL value will also trigger type issues.  We find that a lot with function calls in queries.

Again, for the record, I HATE NULLS!

Paolo

[Simon M]

don't forget that a NULL value will also trigger type issues.

The query already has that check.

[Paolo F Cantoni]

don't forget that a NULL value will also trigger type issues.

The query already has that check.

But only on a specific column.  We get caught bu NULL values in the other columns!  We then have to exclude (or at least handle them).

Paolo

[MaXyM]

I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Casting to empty string is in general wrong idea (even if this time it might work)
It's better to use proper syntax for comparing values incl. NULLs. For example

Code: [Select]

val1 IS DISTINCT FROM val2
or
val1 IS NOT DISTINCT FROM val2It's worth to remember that in most cases/SQL dialects any operation with NULL (including boolean evaluation) gives NULL. And then NULL is never casted to boolean. So even NULL = NULL condition is not TRUE (but it's not FALSE too)

In fact this is great subject to rise during recruitment
I love that

[Geert Bellekens]

I guess you better cast both fields to string to compare them instead of trying to cast it to a number.

Casting to empty string is in general wrong idea (even if this time it might work)

I did not say cast to empty string. The null values where already checked, so this was a case of the field having a non numerical value.
When comparing a numerical field with a string field it is a better idea to cast the numerical field to a string, then to cast the string field to a numerical type.

Geert

[Geert Bellekens]

It's better to use proper syntax for comparing values incl. NULLs. For example

Code: [Select]

val1 IS DISTINCT FROM val2
or
val1 IS NOT DISTINCT FROM val2

Since I've never used IS DISTINCT FROM  I did a little research.
It is a standard SQL feature, but it it does not seem to be supported by Oracle, SQL server. see https://www.sql-workbench.net/dbms_comparison.html
I haven't tried ms-access, (.eap files) and the website doesn't mention access sql syntax.

Geert