Topic
  • 8 replies
  • Latest Post - ‏2013-05-17T20:30:51Z by robinsgusa
DanielFarrell
DanielFarrell
15 Posts

Pinned topic AQL Case statement, data types and casting

‏2013-05-17T17:39:27Z |

 

 

Hello Experts ..

 

   Question please ?

   Given the AQL-

    create dictionary OSBitness_dict
      as
      (
      'i686;'        ,
      'x86_64;'
      );
   create view OSBitness_view
      as
      extract dictionary 'OSBitness_dict'
      on R.text as text
      from Document R;
   create view OSBitnessCleaned_view
      as
      select
         case
         when Equals(R.text, 'i686;'  )  then '32'
         when Equals(R.text, 'x86_64;')  then '64'
         else                                 '98'
         as text
      from OSBitness_view R;
   

   I get the runtime error below,
 
         --  Can't compare types 'Span over Document.text' and 'String'

   How do I overcome this ?  Presumably I have to cast ?

   Can I (should I) get the case statement inside the first 'create view' statement ?

 

 

   thanks in advance
 

  • robinsgusa
    robinsgusa
    5 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T17:54:12Z  

    Hi Daniel,

    I think you need to use the GetText funtion on your spans:

    when Equals(GetText(R.text,) 'i686;'  )  then '32'
             when Equals(GetText(R.text), 'x86_64;')  then '64'

    My preference is to keep the AQL as simple and easy to understand as possible and let the optimizer figure out how to minimize the number of steps.

    Gary

  • DanielFarrell
    DanielFarrell
    15 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T18:03:31Z  

    Hi Daniel,

    I think you need to use the GetText funtion on your spans:

    when Equals(GetText(R.text,) 'i686;'  )  then '32'
             when Equals(GetText(R.text), 'x86_64;')  then '64'

    My preference is to keep the AQL as simple and easy to understand as possible and let the optimizer figure out how to minimize the number of steps.

    Gary

     

     

    Hi Gary ..

     

    Implemented your sugesstion, thanks !

     

    Error went away, but now I get an empty Annotation Explorer view result set;

    no data is returned. (Code is below.)

     

     

    create dictionary OSBitness_dict
       as
       (
       'i686;'        ,
       'x86_64;'
       );
        
    create view OSBitness_view
       as
       extract dictionary 'OSBitness_dict'
       on R.text as text
       from Document R;
         
    create view OSBitnessCleaned_view
       as
       select
          case
          when Equals(GetText(R.text), 'i686;'  ) then '32'
          when Equals(GetText(R.text), 'x86_64;') then '64'
          else                                                        '98'
          as sss
       from OSBitness_view R;

     

     

  • robinsgusa
    robinsgusa
    5 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T18:13:44Z  

     

     

    Hi Gary ..

     

    Implemented your sugesstion, thanks !

     

    Error went away, but now I get an empty Annotation Explorer view result set;

    no data is returned. (Code is below.)

     

     

    create dictionary OSBitness_dict
       as
       (
       'i686;'        ,
       'x86_64;'
       );
        
    create view OSBitness_view
       as
       extract dictionary 'OSBitness_dict'
       on R.text as text
       from Document R;
         
    create view OSBitnessCleaned_view
       as
       select
          case
          when Equals(GetText(R.text), 'i686;'  ) then '32'
          when Equals(GetText(R.text), 'x86_64;') then '64'
          else                                                        '98'
          as sss
       from OSBitness_view R;

     

     

    Daniel,

    I built a simple test case with a text file containing 3 lines, one containing i686;, one containing x86_64; and one containing nothing that would match.

    My AQL ran and output the expected two results in each view. Here's the AQL

    module MachineData;

    create dictionary OSBitness_viewDict
    as ('i686;','x86_64;');

    create view OSBitness_view as
    extract dictionary 'OSBitness_viewDict'
        on R.text as match
    from Document R;

    output view OSBitness_view;

    create view OSBitnessCleaned_view as
    select case
      when Equals(GetText(R.match), 'i686;'  )  then '32'
      when Equals(GetText(R.match), 'x86_64;')  then '64'
      else '98'
      as match
    from OSBitness_view R;

    output view OSBitnessCleaned_view;

    Are you getting matches in your first view and what do they look like?

    What does your input data look like, can you post a sample of the text so I can see what you're working with?

    Gary

  • DanielFarrell
    DanielFarrell
    15 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T18:40:42Z  

    Daniel,

    I built a simple test case with a text file containing 3 lines, one containing i686;, one containing x86_64; and one containing nothing that would match.

    My AQL ran and output the expected two results in each view. Here's the AQL

    module MachineData;

    create dictionary OSBitness_viewDict
    as ('i686;','x86_64;');

    create view OSBitness_view as
    extract dictionary 'OSBitness_viewDict'
        on R.text as match
    from Document R;

    output view OSBitness_view;

    create view OSBitnessCleaned_view as
    select case
      when Equals(GetText(R.match), 'i686;'  )  then '32'
      when Equals(GetText(R.match), 'x86_64;')  then '64'
      else '98'
      as match
    from OSBitness_view R;

    output view OSBitnessCleaned_view;

    Are you getting matches in your first view and what do they look like?

    What does your input data look like, can you post a sample of the text so I can see what you're working with?

    Gary

     

     

    Hi Gary ..

     

    Thank you for your continued interest and support !

     

    Yes, the first view has matches-
     

    Sample data,

    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux x86_64; en-us) applewebkit/533.4 (khtml, like gecko) chrome/5.0.375.55 safari/533.4"

     

  • robinsgusa
    robinsgusa
    5 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T18:54:51Z  

     

     

    Hi Gary ..

     

    Thank you for your continued interest and support !

     

    Yes, the first view has matches-
     

    Sample data,

    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux x86_64; en-us) applewebkit/533.4 (khtml, like gecko) chrome/5.0.375.55 safari/533.4"

     

    Daniel,

    It's working for me with your code and your data.

    Can you try cleaning up your workspace, closing and saving all files, exiting Eclipse, then come back in clean, open the extraction plan and from the workflow run your test and see what happens.

    As far as I can tell your code is good and it's working for me...

    If that help can you export your project and send it to me so I can see what's going on in more detail?

    Gary

  • robinsgusa
    robinsgusa
    5 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T19:34:08Z  

     

     

    Hi Gary ..

     

    Thank you for your continued interest and support !

     

    Yes, the first view has matches-
     

    Sample data,

    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux i686; en-us; rv:1.8.1.11) gecko/20070914 mandriva/2.0.0.11-1.1mdv2007.0 (2007.0) firefox/2.0.0.11"
    "mozilla/5.0 (x11; u; linux x86_64; en-us) applewebkit/533.4 (khtml, like gecko) chrome/5.0.375.55 safari/533.4"

     

    Daniel,

    You won't see anything from OSBitnessCleaned_view in the Annotation Explorer because you aren't extracting anything in that view.

    To see that view, select the toolbar drop down button in Annotation Explorer and select  OSBitnessCleaned_view, this will bring up the tabular view and you will see results.

    Gary

  • DanielFarrell
    DanielFarrell
    15 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T19:55:07Z  

    Daniel,

    You won't see anything from OSBitnessCleaned_view in the Annotation Explorer because you aren't extracting anything in that view.

    To see that view, select the toolbar drop down button in Annotation Explorer and select  OSBitnessCleaned_view, this will bring up the tabular view and you will see results.

    Gary

     

     

    Thank you Sir !!  Yup, that was it.

     

    IF you wish to comment further; why are there two views, why are they different ?

     

    Is it because one 'output view' output Text values and one output strings ?

     

     

  • robinsgusa
    robinsgusa
    5 Posts

    Re: AQL Case statement, data types and casting

    ‏2013-05-17T20:30:51Z  

     

     

    Thank you Sir !!  Yup, that was it.

     

    IF you wish to comment further; why are there two views, why are they different ?

     

    Is it because one 'output view' output Text values and one output strings ?

     

     

    Daniel,

    Yes, you weren't outputing the first view and the second view didn't include any extracted spans, so nothing showed up in Annotation Explorer. If you had output the first view or included the span from the first view in your second view then you would have seen something in Annotation Explorer.

    Maybe the name of this panel is a little confusing? Maybe it should be Span Explorer or Extraction Explorer. I'm sure Laura can tell us why it ended up with this name, and I'm sure there's a good reason.

    BTW, your original question, you could do the same with a single view:

    create view OSBitnessExtractandClean
    as
       select
       case
          when Equals(GetText(D.match), 'i686;'  ) then '32'
          when Equals(GetText(D.match), 'x86_64;') then '64'
          else                                          '98'
        as match
    from (extract dictionary 'OSBitness_dict' on R.text as match from Document R) D;

    output view OSBitnessExtractandClean;

    You wouldn't see anything in Annotation Explorer with this method either.

    Gary