asked on

Trying to return dynamic strings from a CLOB column in DB2

I use IBM Data Studio for most DB2 query work. The following query, attempting to pick 'dynamic' string values from a CLOB, shows no errors in the
Studo query window but when run says: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF SUBSTR IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.67.28.
Is it even possible to explore a CLOB column for strings contained in other table columns dynamically ?

SELECT DISTINCT A.HP_EXR_ID
FROM db2prod.PRV_BPDE_PRV_MHG A
,db2prod.T401ALTC B
,db2prod.T401APFN C
,db2prod.t401ahpa D
WHERE A.HP_ID = B.HP_ID
and A.HP_ID = C.HP_ID
and A.HP_ID = D.HP_ID
and substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%c.pfn_prv_lst_name%'
and substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(c.pfn_prv_fst_name,1,4)%'
and substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(d.HP_ADDR_STR_ADDR,1,8)%'
and substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%substr(d.mn_zip_addr,1,3)%'
and substr(a.MHG_BPDE_DTA_XML_TX,1,3000) like '%d.pol_rgn_code)%'
AND B.HP_ALRT_TYP_CD IN ('G1')
AND B.HP_ALRT_TYP_STA_CD = 'A'
AND B.ANW_TSP_GRP =
(SELECT MAX(BB.ANW_TSP_GRP)
FROM db2prod.T401ALTC BB
WHERE BB.HP_ID = B.HP_ID
AND BB.HP_ALRT_TYP_CD = B.HP_ALRT_TYP_CD);

Tomas Helgi Johannsson

Hi!

If you are running your query against DB2 on z/OS then try this.
The issue is that space needs to be between comma and the numbers in the substr(column, 1, 3).

SELECT DISTINCT A.HP_EXR_ID
FROM db2prod.PRV_BPDE_PRV_MHG A
    ,db2prod.T401ALTC B
    ,db2prod.T401APFN C
    ,db2prod.t401ahpa D
WHERE A.HP_ID = B.HP_ID
and   A.HP_ID = C.HP_ID
and   A.HP_ID = D.HP_ID
and   substr(a.MHG_BPDE_DTA_XML_TX, 1, 3000) like '%c.pfn_prv_lst_name%'
and   substr(a.MHG_BPDE_DTA_XML_TX, 1, 3000) like '%substr(c.pfn_prv_fst_name, 1, 4)%'
and   substr(a.MHG_BPDE_DTA_XML_TX, 1 ,3000) like '%substr(d.HP_ADDR_STR_ADDR, 1, 8)%'
and   substr(a.MHG_BPDE_DTA_XML_TX, 1 ,3000) like '%substr(d.mn_zip_addr, 1, 3)%'
and   substr(a.MHG_BPDE_DTA_XML_TX, 1 ,3000) like '%d.pol_rgn_code)%'
AND B.HP_ALRT_TYP_CD IN ('G1')
AND B.HP_ALRT_TYP_STA_CD = 'A'
AND B.ANW_TSP_GRP =
(SELECT MAX(BB.ANW_TSP_GRP)
FROM db2prod.T401ALTC BB
WHERE BB.HP_ID = B.HP_ID
AND BB.HP_ALRT_TYP_CD = B.HP_ALRT_TYP_CD);

Open in new window

Long varchars and CLOB columns can be seen in the result-tab of the query by hovering over the row in the column and you will see a button that you click on which gives you the whole value.

Regards,
Tomas Helgi

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.