I am running a Oracle 10gR2 DB. In a specfic table I have a clob which contains a XML string. My goal is to count how many time a specfic node appears with in the string; which would give me a count of orders being imported into the system. I have tried to run the following query, but with no success.
select gcssmc_imports_data_id as gcssmc_id,
count(case when REGEXP_INSTR(rec_data, '[=nIIN=]', 'cm') = 0
then null
else 1
end) as NIINs
from gcssmc_imports_data
where interface_name = 'MATS'
and status = 'COMPLETED'
and created_date like to_char(sysdate, 'DD-MON-YY')
group by gcssmc_imports_data_id;
The error:
Error starting at line 1 in command:
select gcssmc_imports_data_id as gcssmc_id,
count(case when REGEXP_INSTR(rec_data, '[=nIIN=]', 'cm') = 0
then null
else 1
end) as NIINs
from gcssmc_imports_data
where interface_name = 'MATS'
and status = 'COMPLETED'
and created_date like to_char(sysdate, 'DD-MON-YY')
group by gcssmc_imports_data_id
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
example string within the rec_data : ?xml version="1.0" encoding="UTF-8"?><p0:salesorderOutCollection xmlns:p0="http://www.usmc.mil/schemas/1/if/stratis"><p0:salesorderOutRecord><p0:dIC>A5A</p0:dIC><p0:rIC>MC1</p0:rIC><p0:nIIN>014925709</p0:nIIN><p0:uOI>KT</p0:uOI><p0:qTr>1</p0:qTr><p0:sDN>M2167020590001</p0:sDN><p0:sC>A</p0:sC><p0:supADD>YMT</p0:supADD><p0:proj/><p0:pri>13</p0:pri><p0:sRN>2498334</p0:sRN><p0:precMIC/><p0:rDD>089</p0:rDD><p0:adviceCode>2A</p0:adviceCode><p0:demilC/><p0:cC>A</p0:cC><p0:shipToName>M21670 9TH COMM BN I MEF (R/U)</p0:shipToName><p0:shipToLine1>TRAFFIC MANAGEMENT OFFICER</p0:shipToLine1><p0:shipToLine2>MF SUPPLY 9TH COMM BN I MEF M21670</p0:shipToLine2><p0:shipToLine3>BLDG 13051 MCB PHONE 760 725 6848</p0:shipToLine3><p0:shipToLine4>CAMP PENDLETON CA 92055-5351</p0:shipToLine4><p0:shipToCity>CAMP PENDLETON</p0:shipToCity><p0:shipToState>CA</p0:shipToState><p0:shipToZipCode/><p0:shipToCountry>US</p0:shipToCountry><p0:fund/><p0:distC/><p0:rICT>MC1</p0:rICT><p0:disposalAuthority/><p0:sfx/></p0:salesorderOutRecord><p0:salesorderOutRecord><p0:dIC>A5A</p0:dIC><p0:rIC>MC1</p0:rIC><p0:nIIN>014925709</p0:nIIN><p0:uOI>KT</p0:uOI><p0:qTr>1</p0:qTr><p0:sDN>M2830120610001</p0:sDN><p0:sC>A</p0:sC><p0:supADD/><p0:proj/><p0:pri>13</p0:pri><p0:sRN>2528478</p0:sRN><p0:precMIC/><p0:rDD/><p0:adviceCode>2A</p0:adviceCode><p0:demilC/><p0:cC>A</p0:cC><p0:shipToName>M28301 CLR 17 1ST MLG (R/U)</p0:shipToName><p0:shipToLine1>ATTN: SUPPLY OFFICER</p0:shipToLine1><p0:shipToLine2>MF CLR 17 1ST MLG M28301</p0:shipToLine2><p0:shipToLine3>BLDG 140199 MCB PHONE 760 725 6360</p0:shipToLine3><p0:shipToLine4>CAMP PENDLETON CA 92055-5000</p0:shipToLine4><p0:shipToCity>CAMP PENDLETON</p0:shipToCity><p0:shipToState>CA</p0:shipToState><p0:shipToZipCode/><p0:shipToCountry>US</p0:shipToCountry><p0:fund/><p0:distC/><p0:rICT>MC1</p0:rICT><p0:disposalAuthority/><p0:sfx/></p0:salesorderOutRecord><p0:salesorderOutRecord><p0:dIC>A5A</p0:dIC><p0:rIC>MC1</p0:rIC><p0:nIIN>014925709</p0:nIIN><p0:uOI>KT</p0:uOI><p0:qTr>1</p0:qTr><p0:sDN>M2167020670004</p0:sDN><p0:sC>A</p0:sC><p0:supADD>M21670</p0:supADD><p0:proj/><p0:pri>13</p0:pri><p0:sRN>2506948</p0:sRN><p0:precMIC/><p0:rDD>081</p0:rDD><p0:adviceCode>2A</p0:adviceCode><p0:demilC/><p0:cC>A</p0:cC><p0:shipToName>M21670 9TH COMM BN I MEF (R/U)</p0:shipToName><p0:shipToLine1>TRAFFIC MANAGEMENT OFFICER</p0:shipToLine1><p0:shipToLine2>MF SUPPLY 9TH COMM BN I MEF M21670</p0:shipToLine2><p0:shipToLine3>BLDG 13051 MCB PHONE 760 725 6848</p0:shipToLine3><p0:shipToLine4>CAMP PENDLETON CA 92055-5351</p0:shipToLine4><p0:shipToCity>CAMP PENDLETON</p0:shipToCity><p0:shipToState>CA</p0:shipToState><p0:shipToZipCode/><p0:shipToCountry>US</p0:shipToCountry><p0:fund/><p0:distC/><p0:rICT>MC1</p0:rICT><p0:disposalAuthority/><p0:sfx/></p0:salesorderOutRecord><p0:salesorderOutRecord><p0:dIC>A5A</p0:dIC><p0:rIC>MC1</p0:rIC><p0:nIIN>014925709</p0:nIIN><p0:uOI>KT</p0:uOI><p0:qTr>1</p0:qTr><p0:sDN>M2167020670002</p0:sDN><p0:sC>A</p0:sC><p0:supADD/><p0:proj/><p0:pri>13</p0:pri><p0:sRN>2505216</p0:sRN><p0:precMIC/><p0:rDD>081</p0:rDD><p0:adviceCode>2A</p0:adviceCode><p0:demilC/><p0:cC>A</p0:cC><p0:shipToName>M21670 9TH COMM BN I MEF (R/U)</p0:shipToName><p0:shipToLine1>TRAFFIC MANAGEMENT OFFICER</p0:shipToLine1><p0:shipToLine2>MF SUPPLY 9TH COMM BN I MEF M21670</p0:shipToLine2><p0:shipToLine3>BLDG 13051 MCB PHONE 760 725 6848</p0:shipToLine3><p0:shipToLine4>CAMP PENDLETON CA 92055-5351</p0:shipToLine4><p0:shipToCity>CAMP PENDLETON</p0:shipToCity><p0:shipToState>CA</p0:shipToState><p0:shipToZipCode/><p0:shipToCountry>US</p0:shipToCountry><p0:fund/><p0:distC/><p0:rICT>MC1</p0:rICT><p0:disposalAuthority/><p0:sfx/></p0:salesorderOutRecord>
If this is even possible, any assistance is awesome...