SQL query to convert CLOB to standard string

[Michael_Coxson_5769]

Hi all,

I am trying to write a subquery for a larger VSQLI that returns the value of a specific JOBS Variable, but I'm not sure how to cast a CLOB into a readable format within my current statement.  Could someone please provide me the method of returning the OV_VALUE value as a column result?

Here's what I have currently:

select distinct OH_NAME,OV_VNAME from OH,OV where OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3' and OH_IDNR = OV_OH_IDNR and OH_CLIENT = &$CLIENT#

https://us.v-cdn.net/5019921/uploads/editor/7l/f5tgj6h58vv1.png" width="432">
(the static OH_NAME will eventually be variablized to &$NAME#)

I appreciate any help with this!

[Pete Wirfs]

Maybe you need the cast() function?  Here is an example from one of my SQLServer queries for searching the contents of the "Documentation" tab;

and cast(b.ODOC_Content as varchar(4096)) like @SearchString

[Michael_Coxson_5769]

That did it!  Thanks for the quick response.

[Michael_Coxson_5769]

Alright, I have narrowed the return to a single line--any idea of how to set the resulting data as a variable in a larger query?  I have been looking around for examples and this is the furthest I have so far:

Here is my current query.  If I use @MASTERJOB as a static value or pass it from a parent object as a variable it works exactly as expected.

DECLARE @MASTERJOB VARCHAR(50); BEGIN select cast(OV.OV_Value as varchar(50)) INTO MASTERJOB from OH,OV    where OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3'    and OV_VNAME like '&MASTERJOB#'    and OH_IDNR = OV_OH_IDNR    and OH_CLIENT = &$CLIENT#; END; select distinct OH_NAME,OHGF_NAME,OH_CLIENT,HACL_Client from OH,OHGF,HACL,OV where OHGF_OH_IDNR = OH_IDNR and OH_CLIENT = &$CLIENT# and OH_OTYPE = 'HOSTG' and OH_IDNR > 100000 and OH_DELETEFLAG = 0 and OH_Client = HACL_CLIENT and OH_NAME in (    select JBA_HostDst from oh,jba    where oh_otype like 'JOBS'    and oh_name like @MASTERJOB    and oh_idnr = jba_oh_idnr    and oh_deleteflag = 0    and oh_client = &$CLIENT# ) and OHGF_NAME in (    select OH_NAME    from OH,HOST    where HOST_OH_Idnr = OH_Idnr    and OH_NAME NOT LIKE '<%>' ) and OHGF_NAME in (    select OH_NAME from oh, HOST, HACL    where OH_Idnr=HOST_OH_Idnr and HOST_Active=1    and (HACL_Execute=1 or HACL_Read=1 or HACL_Write=1)    and OH_DeleteFlag =0    and OH_Idnr = HACL_OH_Idnr    and HACL_Client=&$CLIENT# )


This results in an error:


Any ideas of what it's complaining about?

[Pete Wirfs]

Your error message starts with ORA, which tells me you are in Oracle.  It is complaining about the DECLARE statement because it is using SQLServer syntax, and not Oracle syntax.

Suggest you consider discarding the need for @MASTERJOB all together by doing this in a SEQ_SQLI object, and pass all of your dynamic values as bind parameters.  Using bind parameters improves database efficiency.

There is a good example here;
https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11/All%20Guides/help.htm#ucaabq.htm#07

[Pete Wirfs]

I just noticed you coded this as a 2-phase process;
(1) populate MASTERJOB
(2) use MASTERJOB in the second SQL

You should be able to combine both SQLs into one.  

Replace this;

   and oh_name like @MASTERJOB

With this;

      and oh_name like (select cast(OV.OV_Value as varchar(50))                   from OH,OV                   where OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3'                   and OV_VNAME like '&MASTERJOB#'                   and OH_IDNR = OV_OH_IDNR                   and OH_CLIENT = &$CLIENT#)

[Michael_Coxson_5769]

Hi Pete, appreciate the assistance.  I combined the two as you mentioned and added aliases, per another user's recommendation.  Now it is indicating that a subquery is returning more than one value.

select distinct OH_NAME,OHGF_NAME,OH_CLIENT,HACL_Client from OH H,OHGF G,HACL A,OV V where G.OHGF_OH_IDNR = H.OH_IDNR and H.OH_CLIENT = &$CLIENT# and H.OH_OTYPE = 'HOSTG' and H.OH_IDNR > 100000 and H.OH_DELETEFLAG = 0 and H.OH_Client = A.HACL_CLIENT and H.OH_NAME in (    select J.JBA_HostDst from oh H,jba J    where H.oh_otype like 'JOBS'    and H.oh_name like (select oh_name,cast(V.OV_Value as varchar(50)) from OH H,OV V       where H.OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3'       and V.OV_VNAME like '&MASTERJOB#'       and H.OH_IDNR = V.OV_OH_IDNR       and H.OH_CLIENT = &$CLIENT#)    and H.oh_idnr = J.jba_oh_idnr    and H.oh_deleteflag = 0    and H.oh_client = &$CLIENT# ) and G.OHGF_NAME in (    select H.OH_NAME from OH H,HOST T    where T.HOST_OH_Idnr = H.OH_Idnr    and H.OH_NAME NOT LIKE '<%>' ) and G.OHGF_NAME in (    select H.OH_NAME from oh H, HOST T, HACL A    where H.OH_Idnr = T.HOST_OH_Idnr and T.HOST_Active = 1    and (A.HACL_Execute = 1 or A.HACL_Read=1 or A.HACL_Write=1)    and H.OH_DeleteFlag = 0    and H.OH_Idnr = A.HACL_OH_Idnr    and A.HACL_Client = &$CLIENT# )

[Pete Wirfs]

That can happen when this select returns more than one row;

  and H.oh_name like (select oh_name,cast(V.OV_Value as varchar(50)) from OH H,OV V

      where H.OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3'

      and V.OV_VNAME like '&MASTERJOB#'

      and H.OH_IDNR = V.OV_OH_IDNR

      and H.OH_CLIENT = &$CLIENT#)

If it is always returning the same value, then just change "select" to "select distinct" to fix it.  If it is returning more than one unique value, then you have a logical problem to figure out.

[Michael_Coxson_5769]

Hmm, I'll try tossing distinct in. When I run it as a separate query it only returns a single match but hopefully it's just some odd error on my part.  Thanks again. 

[Michael_Coxson_5769]

Yeah, the issue was that I was still requesting multiple values as a return to my query.  I narrowed it to my cast value and it works.

   and H.oh_name like (select cast(V.OV_Value as varchar(50)) from OH H,OV V       where H.OH_NAME like 'DRE.JU.TASKSPAWN_JOB.P3'       and V.OV_VNAME like '&MASTERJOB#'       and H.OH_IDNR = V.OV_OH_IDNR       and H.OH_CLIENT = &$CLIENT#)

Now my issue is that the VSQLI object works perfectly and the PromptSet preview functions, but when I execute the actual Job the execution sits in "Waiting for user input" without a prompt at all.  I know PromptSets work for the user/client I'm on, as another for my Workflow version of this same process works perfectly.  Any ideas?

https://us.v-cdn.net/5019921/uploads/editor/1q/2bq84sp5tzwx.png" width="1434">

[Michael_Coxson_5769]

So it turns out that while the PromptSet works properly with only Read access for my simpler SQL query, I had to upgrade my user access to Execute for this to function.  I have updated the static JOBS name to the &$NAME# system variable, making it completely independent and usable by any JOBS object, provided it has the &MASTERJOB# variable set.

Afterward, I had to update the Job to put the agent into the activated Job using the :PUT_READ_BUFFER command and include a :READ and :PUT_ATT in the Preprocess of the template to have the value passed properly.  This can now execute a template Job against any agent belonging to the "master" Job's default HostGroup (AgentGroup).

Now I need to see if I can overwrite the behavior for HostGroup activation... If I try the Job against the "master" instead of the template it will run the Job against the selected agent as many times as the HostGroup contains a host/agent.  It appears that the engine reviews the HostGroup prior to any other action, even replacement of the Host attribute where the HostGroup resides--does anyone know how to supersede this?