Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2013-10-09T14:34:39Z by JohnnyRapid
JohnnyRapid
JohnnyRapid
7 Posts
ACCEPTED ANSWER

Pinned topic Retrieve properties along with computer id

‏2013-10-08T16:19:12Z |

I am trying to retrieve multiple property results along with the computer id of the computer they correspond to. I am hoping to have each property correspond to a different column and only have one row per computer. So far I have the following which does not meet these requirements and additionally doesn't even return data.

(
    values of results from 
    (
        bes property "Serial Number" 
    ) of it,
    values of results from 
    (
        bes property "Model" 
    ) of it, 
    id of it 
)
of bes computers
  • Lee Wei
    Lee Wei
    598 Posts
    ACCEPTED ANSWER

    Re: Retrieve properties along with computer id

    ‏2013-10-08T17:22:03Z  in response to JohnnyRapid

    The statement that you have has 2 potential issues.

    - Firstly, if a computer is missing either the Serial Number or Model, then it will not be returned as part of the results.

    - Also, the construct bes property "SomeName" is not a good form because the result is undeterministic if it is not unique.

    I used the Excel Connector, which generated the following statement for me.

    Note that the If-Then-Else statement is used to ensure that a computer is returned even if not data is available.

    Also note the specific reference by ID of the properties.

    (
        id of item 0 of it,
        (if (exists result (item 0 of it, item 1 of it) and
             exists values of result (item 0 of it, item 1 of it) )
            then (concatenation "%0A" of values of result (item 0 of it, item 1 of it))
            else ("<none>")),
        (if (exists result (item 0 of it, item 2 of it) and
             exists values of result (item 0 of it, item 2 of it) )
            then (concatenation "%0A" of values of result (item 0 of it, item 2 of it))
            else ("<none>")),
        (if (exists result (item 0 of it, item 3 of it) and
             exists values of result (item 0 of it, item 3 of it) )
            then (concatenation "%0A" of values of result (item 0 of it, item 3 of it))
            else ("<none>")))
    of (
        bes computers,
        bes property "Computer Name",
        bes property whose (name of it = "Model" and
            (item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 3) of id of it),
        bes property whose (name of it = "Serial Number" and
            (item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 8) of id of it))
           

    • JohnnyRapid
      JohnnyRapid
      7 Posts
      ACCEPTED ANSWER

      Re: Retrieve properties along with computer id

      ‏2013-10-08T18:03:16Z  in response to Lee Wei

      Thank you for your speedy response.

      My trouble with this query is that it returns rows for computers that do not have those properties. I need all properties (there will at least be 4 of them, though I omitted the others for the purpose of brevity) and each must be defined. How would I accomplish this?

      Thanks again! 

      EDIT:  (concatenation "%0A" of values of result (item 0 of it, item 3 of it)) upon further reflection I understand all but the 'concatenation "%0A"'. With these properties could there be duplicates? What does the %0A entail?

      Updated on 2013-10-08T19:41:37Z at 2013-10-08T19:41:37Z by JohnnyRapid
    • JohnnyRapid
      JohnnyRapid
      7 Posts
      ACCEPTED ANSWER

      Re: Retrieve properties along with computer id

      ‏2013-10-09T14:34:39Z  in response to Lee Wei

      I am accomplishing this by adding a "whose" clause with

      exists result (item 0 of it , item 1 of it)
      and exists values of result (item 0 of it , item 1 of it)

      And getting rid of the if/then since they are no longer needed.

      The full query being

      (
      id of item 0 of it,
      concatenation "%0A" of values of result (item 0 of it , item 1 of it),
      concatenation "%0A" of values of result (item 0 of it , item 2 of it)
      )
      of 
      (
      bes computers,
         bes properties whose (name of it = "Model" and
              (item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 3) of id of it),
          bes properties whose (name of it = "Serial Number" and
              (item 0 of it = 9337 and item 1 of it = 10 and item 2 of it = 8) of id of it)
      )
      whose
      (
      exists result (item 0 of it , item 1 of it)
      and exists values of result (item 0 of it , item 1 of it)
      and exists result (item 0 of it , item 2 of it)
      and exists values of result (item 0 of it , item 2 of it)
      )

      EDIT: "bes property" had to become "bes properties"

      Updated on 2013-10-09T15:19:52Z at 2013-10-09T15:19:52Z by JohnnyRapid