0

I'm trying to select records from a table using SQL Developer based on an INT value (in one file) to a STR (in the other file) and I'm using a nested(?) query:

select *
from proddta.FQ584871 
where tdrscn in (select rmrscn from proddta.f48310) 
and tdan8 > 1 and tdeqhr > 0 
and tddoco not in (select glsbl from proddta.f0911)

In this example the TDDOCO is an INT and the GLSBL is a STR. The match would be TDDOCO = 456123 to the GLSBL = 00456123.

Thank you for any assistance

I've tried to CAST and the STR and VARCHAR but they don't appear to work when I'm trying to convert the TDDOCO.

I've tried:
tddoco not in (select to_number(ltrim(glsbl,'0')) from proddta.f0911

and I've tried:

to_char(tddoco) not in (select glsbl from proddta.f0911)

and I get an invalid number error.

Annmarie Annie Altenhofen
  • 1
  • why not `CAST ( glsbl as BIGINT)` your conversion would work as '456123' isn't equal to '0045612' – nbk Jan 29 '23 at 21:12
  • 3
    Which database do you use? Tags suggest Oracle. However, there's no STR datatype there. Furthermore, you've said "in one file". What do you call a *file*? In database, we have *tables*, not *files*. Finally, what problem do you actually have? No rows returned? Some error? If so, which one? You did mention *invalid number*, but that's result of your unsuccessful datatype conversion, not the original query. A few sample rows would help us help you. Because, Oracle *would* implicitly convert datatypes and return result, if possible. It seems that GLSBL doesn't contain digits only. – Littlefoot Jan 29 '23 at 21:30
  • 1
    It sounds like some of the VARCHAR2 values might not convert to numbers. Please take a look at [Finding non-numeric values in varchar column](https://stackoverflow.com/questions/47711667/finding-non-numeric-values-in-varchar-column). – Jason Seek Well Jan 30 '23 at 13:20

0 Answers0