SQL query to convert CLOB to standard string

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#


(the static OH_NAME will eventually be variablized to &$NAME#)

I appreciate any help with this!

Comments

  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 949 Innovator ✭✭✭✭✭✭
    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
    Pete
    AE 11.2.1/Windows/SQLServer
  • Michael CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    That did it!  Thanks for the quick response.


  • Michael CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    edited February 17
    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 WirfsPete Wirfs Software Engineer Salem OregonPosts: 949 Innovator ✭✭✭✭✭✭
    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 Guides/help.htm#ucaabq.htm#07
    Pete
    AE 11.2.1/Windows/SQLServer
  • Pete WirfsPete Wirfs Software Engineer Salem OregonPosts: 949 Innovator ✭✭✭✭✭✭
    edited February 17
    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#)
    Pete
    AE 11.2.1/Windows/SQLServer
  • Michael CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    edited February 17
    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 WirfsPete Wirfs Software Engineer Salem OregonPosts: 949 Innovator ✭✭✭✭✭✭
    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.
    Pete
    AE 11.2.1/Windows/SQLServer
  • Michael CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    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 CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    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?



  • Michael CoxsonMichael Coxson Posts: 138 Journeyman ✭✭✭
    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?
Sign In or Register to comment.