Problem gettin data out of the Blob with SQL
I'm using Oracle 12 / Pega 7.2.2
When I do this SQL, I get back 9 rows - as the PYIDs are M-191006-000001 thru -000009
I'm using Oracle 12 / Pega 7.2.2
When I do this SQL, I get back 9 rows - as the PYIDs are M-191006-000001 thru -000009
select PYID, pzinskey, SENDERREF, RELATEDREF, MessageType ,PXCREATEDATETIME ,
gin.pr_read_from_stream('Message','pzInsKey',pzpvstream) message
from GIN.INV_WORK where PYID like 'M-191006-00000%'
The problem is the last field - message - shows the value of that tag from the 1st record on all nine rows returned. If I do this -
select gin.pr_read_from_stream('Message','pzInsKey',pzpvstream) message
from GIN.INV_WORK where pzInsKey = 'INV-GIN-WORK M-191006-000003'
I get the right value back for that record - but it only works right if I explicitly state the pzInsKey value in the where clause so I only get one row back - ie - pzInsKey = 'INV-GIN-WORK M-191006-000003' . How do I get the tag out of the blob for each case in one Sql?
Any help would be greatly appreciated.
Pegasystems Inc.
IN
Instead of using "where PYID like 'M-191006-00000%' ", what is the result if you use "where pzInsKey like 'INV-GIN-WORK M-191006-00000%' ".
Bank of America
US
I've tried that ... where pzinskey like .... basically whenever I return more rows than one regardless of the where clause, I get the same value from the function for every row - whatever the function returned for the first row in the result set, all the other rows get that same value.