Subquery with the records of a column

Asked

Viewed 64 times

1

I have the following problem... I need to extract the information from a table where it has the following structure:

tabela: PROCESSO_VALOR_TIPO  
Coluna : field_model_ids  
Dados : 135;137;138;139;140;878;879;880;881;882

OBS: NEED TO USE THIS DATA AS A SUB-CONSUMPTION

My query :

SELECT *
  FROM FIELD_MODEL
 where FIELD_MODEL_ID IN
       (SELECT REPLACE(FIELD_MODEL_IDS, ';', ',')
          FROM PROCESSO_VALOR_TIPO
         where processo_valor_tipo_id = 1176);

ERROR:

ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a Valid number.

What would be the best way to return this data? I have tried several ways and none successfully

sql oracle sqldeveloper dql

  • Let me get this straight... you have a table called PROCESSO_VALOR_TIPO with a field called field_model_ids and if you select it, it will bring 135, 137, 138, etc, is that it? Or the field is a whole 'linguistics' joining all these Ids?

  • What kind of field processo_valor_tipo_id?

1 answer

1


With the query below you will be able to transform your string list into lines, with it you can use inside your subselect

SELECT trim(regexp_substr(FIELD_MODEL_IDS, '[^;]+', 1, LEVEL)) str
FROM PROCESSO_VALOR_TIPO
CONNECT BY instr(FIELD_MODEL_IDS, ';', 1, LEVEL - 1) > 0

Your final appointment would look like this:

SELECT *
FROM FIELD_MODEL
WHERE FIELD_MODEL_ID IN (    
    SELECT trim(regexp_substr(FIELD_MODEL_IDS, '[^;]+', 1, LEVEL)) str
    FROM PROCESSO_VALOR_TIPO
    CONNECT BY instr(FIELD_MODEL_IDS, ';', 1, LEVEL - 1) > 0)

Browser other questions tagged sql oracle sqldeveloper dql

You are not signed in. Login or sign up in order to post.