Prev
Next
Forums Integration / Customization IPA/ProcessFlow

Binary Fields and Sql Query's in Flows

 6 Replies
 1 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
JimY
Veteran Member
Posts: 510
Veteran Member
1/12/2016 5:51 PM

    I am using the following Sql query in a flow.  The Agent field is binary and instead of getting a binary value I am getting something like this: Ap'7 .  If I run this in Sql Manager I get the correct value.  Is there something I need to do in process automation to get the correct value.  Our Landmark version is 10.1.1.23.  Thank you,

    SELECT [AGENT]       
          ,[BORBUSINESSCLASSNAME]     
          ,[KEYTEXT]     
      FROM [ltmtest].[AGENT]
      where KEYTEXT = '1000, 11113985' 

    mikeP
    Veteran Member
    Posts: 151
    Veteran Member
    1/12/2016 7:46 PM
      Try casting your binary value to char or numeric, e.g.

      CAST(SUM(DISTR_FTE) AS NUMERIC (16,3)) GL_FTE,
      CAST(SUM(PrtWageAmount) AS DECIMAL(9,0)) TotWages
      CAST(S.EMPLOYEE AS CHAR) EMPLOYEE,
      JimY
      Veteran Member
      Posts: 510
      Veteran Member
      1/14/2016 11:07 AM
        Hi Mikep,
        That didn't work. It's strange that it works in Sql Manager, but not when I use the same query in a Process Flow. Thank you for the response.
        mikeP
        Veteran Member
        Posts: 151
        Veteran Member
        1/14/2016 1:10 PM
          Are you getting incorrect results from the Cast, or a SQL syntax error?
          Woozy
          Veteran Member
          Posts: 709
          Veteran Member
          1/14/2016 2:50 PM
            Hi JimY,

            You don't mention what database you're using, or what you are wanting to do with the agent value, so I'm not sure if this will help you. However, we use DB2, and using HEX(AGENT) converts the value into a string. However, if you are just joining to another binary value then you can leave it in the original format, even though it isn't pretty in the log. I'd think in MSSQL then MikeP is correct, the CAST or maybe CONVERT(varchar(64),AGENT,0) should work.

            Good Luck!
            Kelly Meade
            J. R. Simplot Company
            Boise, ID
            JimY
            Veteran Member
            Posts: 510
            Veteran Member
            1/14/2016 6:09 PM
              It is Microsoft Sql Server. I tried to put the values here, but they don't display correctly. Sql Manager displays the value correctly, but Process Automation does not. The cast did not work in Process Automation, but did work in Sql Server Manager. I have opened a ticket with Infor to see what they have to say.
              JimY
              Veteran Member
              Posts: 510
              Veteran Member
              1/22/2016 12:21 PM
                I was able to resolve this by using a Landmark Query Node. It took some time to get the quoting correct, but it work great.