1 / 32

Informer 4: Advanced Criteria Options for SQL & U2

Join SQL & U2 criteria, order of operations, logic conditions, compound conditions, subselects, scalar joins, and U2 conditions in Informer 4.

kristij
Download Presentation

Informer 4: Advanced Criteria Options for SQL & U2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. February 11-13, 2019 Raleigh, NC

  2. Informer 4: Advanced Criteria Options for SQL & U2 Presenter: Sarah Rodger Informer Support Specialist

  3. Overview • Joint SQL & U2 Criteria • SQL Criteria • U2 Criteria

  4. U2 & SQL Criteria • Order of Operations • Logic conditions: And, Or, Not • Compound Conditions

  5. Order of Operations • Order matters? • Yes for performance! No for result content. • Processed Top-down so… • Place the most restrictive condition first • Parenthetical grouping (Compound Condition) • Order matters • (1 + 2 ) * 3 = 9 but 1 + (2 * 3) = 7 • U2: Put I-Descriptors/Computed Columns last • You can’t use these to filter if they are in a linked File

  6. Logic Conditions • AND = all conditions have to be true (both) • OR = only one condition has to be true (either) • NONE = none of the conditions are true

  7. Condition Examples AND OR NONE

  8. Compound Conditions • Mix conjunctions • Use Parenthetical grouping: a and b and (c or d) AND OR

  9. SQL Overview • Subselects • Scalar Joins

  10. SQL Subselect  There are three main reasons why you woulduse a subselect in your Informer report. You want to… • see if a value occurs in a certain dataset • see if a value does not occur in a certain dataset • compare a value to an aggregate value from a certain dataset

  11. SQL Subselect / Value in Dataset? Example: List all products with orders of at least $10,000 Return:a unique list of IDs

  12. SQL Subselect / Value not in Dataset? Example: List all products that do not orders of at least $10,000 Return:a unique list of IDs

  13. SQL Subselect / Compare to Aggregate? Example: List all sales reps with a sales quota more than the average sales quota for all reps. Return:a unique list of IDs

  14. SQL Scalar Joins Add constraints to the link conditions: Language = English

  15. SQL Scalar Joins Use in a report like this: create a report from the Country table and list code from Country, and Language from our new link, the result set will list only the countries with English as one of the languages

  16. SQL Scalar Joins Produces

  17. U2 Conditions • Using Select Return • Select Return Conditions: AND, OR, NOT • Associated Multivalues: parallel arrays

  18. U2 SELECT/RETURNING • Same as SELECT…SAVING UNIQUE… • Return distinct list of @IDs from selected records • Files must have reverse links • Forward links are not required

  19. U2 SELECT/RETURNING example “Return a list of Person IDs that ordered this year”

  20. U2 SELECT/RETURNING conditions • Using multiple SELECT/RETURNING conditions • Conjunction determines operator: • Intersect / AND / Both • Union / OR / Either • Difference / NOT

  21. U2 Conditions: Intersect / And • Use AND conjunction • Values that only exist in both lists “Return a list of Person IDs that ordered both this year and last year”

  22. U2 Conditions: : Union / Or • Use OR conjunction • Combines Lists “Return a list of Person IDs that ordered either this year or last year”

  23. U2 Conditions: Difference / Not • Use AND with NONE Compound Condition • Values that exist in one list but not the other “Return a list of Person IDs that ordered this year but not last year: the list of new customers”

  24. U2 Multivalues: Limiters • Field containing multiple values (an array) • Use of Limiter: Any, Every, No, When

  25. U2 Multivalues: Limiter Any Any of the values match the condition

  26. U2 Multivalues: Limiter Every Every value must match the condition

  27. U2 Multivalues: Limiter No None of the values match the condition

  28. U2 Associated Multivalues: When • Use with two or more related multivalue fields • Ex: Status and Status Date are linked • Use WHEN delimiter for each condition • Example follows…

  29. U2 Associated Multivalues: When • For example, • if you have a multivalve field for status and one for status date • and you want to know every record that had a certain status on a certain date • You would use the WHEN limiter on both conditions.   • For an associated comparison to work correctly, the two multivalve fields • have to be associated in the database file's dictionary

  30. Resources: Search the Help Center These articles are available with more detail • SQL: Creating Scalar Joins • SQL: Using Subselects • How to Create a Compound Condition • How to Use a Select Return Condition (For U2 Databases Only) • Training and User Guides: Informer Report User Training Guide

  31. I4 Advanced Criteria Options Thank you! Any Questions?

  32. February 11-13, 2019 Raleigh, NC

More Related