0

I have two tables A and B, for example

A

The <number> 1 </number> is cat
The <number> 2 </number> is dog
The <number> 3 </number> is horse
The <number> 4 </number> is chicken
The <number> 5 </number> is hippo

'''

B

<id>2</id>
<id>4</id>
<id>1</id>

I want to cross check the values from B (numbers only) with all values from table A and have the result:

is dog
is chicken
is cat

I do this in "where" query's section like this (just an example):

where (REGEXP_INSTR ((DBMS_LOB.SUBSTR(regexp_substr(A, '<number>(.**?)(\s)'))) , (DBMS_LOB.SUBSTR((select regexp_substr(B, '<id>(.*?)</id>.*',1,1,null,1) from B 
FETCH NEXT 1 ROWS ONLY ))))>0;

My problem is with "FETCH NEXT 1 ROWS ONLY" returns only one row and without that I receive the error "ORA-01427: single-row subquery returns more than one row" (logic). How can I compare all numbers from B with all rows from A and receive the 3 results?

Thank you very much

UPDATE 19/10

Below more accurate info.

select col1
from
(select regexp_substr(B.E, '<InternalId>(.**?)(\s)') as col1, (select regexp_substr(table_D.data1, '<InternalId>(.*?)</InternalId>.*',1,1,null,1) from table_F D) as col2
from table_C B 
where table_B.E like 'xxxxx')
where REGEXP_INSTR ((DBMS_LOB.SUBSTR(col1)) , (DBMS_LOB.SUBSTR(col2)))>0;

table_D.data1 is like that:

    <?xml version="1.0"?>
<FVDeliveryAdvice>
  <Header>
    <InternalId>2</InternalId>
  <Datatype>200</Datatype>

  </Header>
     <Item>
    <Subitem>
    </Subitem>
  </Item>
</FVDeliveryAdvice>

and

B.E like that

    ----- PL/SQL Error Message -----
<InternalId>2</InternalId>ORA-20104: ALR not found: '0007000-00801000001'
<Status>
 <Header
  <InternalId>2</InternalId>
  <Datatype>200</Datatype>
</Header>
 <StatusInfo>
</StatusInfo>
</Status>

----- PL/SQL Error Stack -----
ORA-20104: ALR not found:'0007000-00801000001'

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FF9AF947390         1  anonymous block


result
ORA-01427: single-row subquery returns more than one row

Result I want:
<InternalId>2</InternalId>ORA-20104: ALR not found: '0007000-00801000001'
but for all rows

The rows from all tables as too many thousands

Gtman
  • 1
  • 3
  • 1
    Hard to tell without seeing the rest of the query. It's not clear if you've heard of "joins" before. Separately - is your data supposed to be XML, or not? The strings in the first table seem to have some XML tags in them, but they are not valid XML in their entirety. Is that because you didn't show us the actual strings? If everything is XML, you would do much better to use XML functions, not regexp_<>. (And, since XML is case sensitive, the closing tag of should be , not with capital I.) –  Oct 18 '21 at 21:33
  • Replace text `Number` with text `Id` and join (or check `exists`) on `like` predicate. Do not try to parse something when you actually need a replace. – astentx Oct 18 '21 at 21:43
  • Hello thank you for your answer. Yes data is xml. I correct 'i'. But the problem is not there, regexp works perfect. The problem is that second regexp_substr returns more than 1 row. Show regexp_instr cannot run with more than one row (value) per time. I know where is the problem but i can't fix it. Is there any similar function like regexp_instr which can check all values? – Gtman Oct 18 '21 at 21:48

1 Answers1

0

You state in the comments that the data is xml, but the sample data you provided is not valid xml ("The <number> 1 </number> is cat" is not valid xml). The solution below uses regexp to extract the id from the <number> tag in the invalid xml and the XMLTYPE().EXTRACT().GETSTRINGVAL() function to extract the id from the valid xml. If your data is xml, look at the xml native functions to extract the data, they're a lot more performant than REGEXP functions.

WITH table_a (c) AS
(
SELECT 'The <number> 1 </number> is cat' FROM DUAL UNION ALL
SELECT 'The <number> 2 </number> is dog' FROM DUAL UNION ALL
SELECT 'The <number> 3 </number> is horse' FROM DUAL UNION ALL
SELECT 'The <number> 4 </number> is chicken' FROM DUAL UNION ALL
SELECT 'The <number> 5 </number> is hippo ' FROM DUAL 
)
, table_b (c) AS 
(
SELECT '<id>2</id>' FROM DUAL UNION ALL
SELECT '<id>4</id>' FROM DUAL UNION ALL
SELECT '<id>1</id>' FROM DUAL 
)
SELECT 
    TRIM(REGEXP_REPLACE(a.c,'([^<>]+)(<number>)([^<>]+)(</number>)([^<>]+)','\5')) as result
    FROM table_a a 
      JOIN table_b b ON TRIM(XMLTYPE(b.c).EXTRACT('/id/text()').GETSTRINGVAL()) = TRIM(REGEXP_REPLACE(a.c,'([^<>]+)(<number>)([^<>]+)(</number>)([^<>]+)','\3'));

RESULT

is cat
is dog
is chicken
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
  • Thank you very much for your answer, i really appreciate. But tables are very very big to do something like that. I edit my first post, please check it again for cleaner information – Gtman Oct 19 '21 at 10:41
  • @Gtman, please provide working sample data, with fully qualified xml, not a snippet. You should be able to run your query against your own test data. – Koen Lostrie Oct 19 '21 at 11:36
  • Sorry but i can't post the original data. Are HR data. Snippet is very close. But again i say, the problem is not on tables data, i don't have problem with regexp_substr. The problem is on REGEXP_INSTR. Because REGEXP_INSTR support (many rows, one value) and i have REGEXP_INSTR (many rows, many rows). – Gtman Oct 19 '21 at 13:08
  • I never said to post the production data. Just create a dummy dataset with a similar structure so I give you a proper answer that is easy to port to your dataset. If the data is xml, you should use xml functions, not regexp. Without complete xml I can't give you a proper answer. – Koen Lostrie Oct 19 '21 at 13:23
  • OK i make the changes. I delete nvl because second table was useless. I think i don't have something else to show you. Thank you very much again – Gtman Oct 21 '21 at 06:48
  • please could you help me? – Gtman Oct 29 '21 at 12:07
  • Sure, if you provide some sample valid xml with a similar structure instead of some snippets. As I stated before I cannot give a proper answer without valid xml. – Koen Lostrie Oct 29 '21 at 12:52