Question

2
Replies
47
Views
HAYESJ01 Member since 2013 1 post
Bank of America
Posted: 11 months ago
Last activity: 11 months 1 week ago

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

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.

Data Integration
Share this page LinkedIn