10 Replies Latest reply on May 2, 2018 7:43 AM by GreggSmith

    Can we place queries in datamodeleditor to extract the required data?

    vamseekp Apprentice

      As of now, what i know is: after connecting to database we can create view and add table which belongs to that database. We can pull the columns(all/any) using Database lookup. Also we can use filters to filter data from the table which we created. Is there any alternate way like placing SQL query and extracting the required data instead of using filters?  

        • 1. Re: Can we place queries in datamodeleditor to extract the required data?
          Waldy Employee

          You can use a SQL query as a derived table rather than a reference to a physical table.

           

          Any SQL added to a derived table to filter data would always be in place.

          • 2. Re: Can we place queries in datamodeleditor to extract the required data?
            GreggSmith SupportEmployee

            But, you should only use the SQL query as a derived table if there is a reason for doing that instead of just mapping the logic in as tables/joins.

            • 3. Re: Can we place queries in datamodeleditor to extract the required data?
              vamseekp Apprentice

              Guys, I've place the query in the table field and also updated the Schema. The connection is successfully established. I'm able to pull the data by given table name in the table field. But when i give a Query its throwing the following error.

              The query which i used is

              select ticketid, description, reportedpriority, status, creationdate as submitdate from incident

              where (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')

               

              Also tried in this way:

              select incident.ticketid, incident.description, incident.reportedpriority, incident.status, incident.creationdate as submitdate from incident

              where (incident.STATUS not like 'RESOLVED' AND incident.STATUS not like 'CLOSED')

               

              Please let me know is this the right way. If yes how to solve the issue which is arising here.

               

               

              • 4. Re: Can we place queries in datamodeleditor to extract the required data?
                Waldy Employee

                First of all, derived table SQL statements need to be a sub select, so should be in (brackets).  Secondly, you need to remove the Schema value for derived tables.  You may need to specify it in the SQL if this is not the default schema for the connected user.

                1 of 1 people found this helpful
                • 5. Re: Can we place queries in datamodeleditor to extract the required data?
                  GreggSmith SupportEmployee

                  Am I correct in assuming, from the logic of your query, that you are trying to create an Xtraction View to always show only active Incidents without having to use an "All Incidents" view and put filters in the dashboard to limit to active Incidents?

                   

                  If so, the way you are proposing to implement it WILL work, however it will be slightly harder to maintain in the future AND will result in slightly less efficient queries than if you use a more "traditional" way.

                   

                  Using the nested select method will result in queries like:

                   

                  SELECT INC.Status

                  FROM

                    (select ticketid, description, reportedpriority, status, creationdate as submitdate from incident

                  where (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')) INC

                   

                  Where the more traditional modeling will result in queries like:

                   

                  SELECT INC.Status

                  FROM Incident INC

                  where INC.STATUS not like 'RESOLVED' AND INC.STATUS not like 'CLOSED'

                   

                  The more traditional way would be as follows:

                  1. Create the Xtraction View, "Active Incidents"
                  2. Add the table, Incident
                  3. Use Add Field (Database Lookup) to quickly and easily map all the fields you are interested in
                  4. In the View properties dialog, add "Status Doesn't Equal Resolved" and "Status Doesn't Equal Closed" filters on the Filters tab (see screenshot below)
                  5. The filters added in the above step will ALWAYS be included in the queries created by Xtraction, but the user never has to add them in the web interface and won't see them in the interface, either.

                   

                  • 6. Re: Can we place queries in datamodeleditor to extract the required data?
                    vamseekp Apprentice

                    Hi Gregg,

                     

                    Where should the suggested nested query to be placed. If its in the table field as described in my previous screenshot. If i do so, I'm facing the same issue here.

                     

                     

                    -Vamshi

                    • 7. Re: Can we place queries in datamodeleditor to extract the required data?
                      vamseekp Apprentice

                      Also if the query is small your suggested traditional way of using filters is good. What if the query is complex like below:

                       

                      SELECT  'Critical' as Priority, 1 as IntPriority,

                      SUM(CASE WHEN REPORTEDPRIORITY = 1 THEN 1 ELSE 0 END) as COUNT 

                      FROM INCIDENT

                      WHERE

                      (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')

                      UNION

                      SELECT  'High' as Priority, 2 as IntPriority,

                      SUM(CASE WHEN REPORTEDPRIORITY = 2 THEN 1 ELSE 0 END) as COUNT 

                      FROM INCIDENT

                      WHERE

                      (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')

                      UNION

                      SELECT  'Medium' as Priority, 3 as IntPriority,

                      SUM(CASE WHEN REPORTEDPRIORITY = 3 THEN 1 ELSE 0 END) as COUNT 

                      FROM INCIDENT

                      WHERE

                      (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')

                      UNION

                      SELECT  'Low' as Priority, 4 as IntPriority,

                      SUM(CASE WHEN REPORTEDPRIORITY = 4 THEN 1 ELSE 0 END) as COUNT 

                      FROM INCIDENT

                      WHERE

                      (STATUS not like 'RESOLVED' AND STATUS not like 'CLOSED')

                      ORDER BY IntPriority

                       

                       

                      • 8. Re: Can we place queries in datamodeleditor to extract the required data?
                        GreggSmith SupportEmployee

                        Well, looking at that example, at a minimum, I would simplify it to:

                         

                        SELECT ReportedPriority, Count(*)

                        FROM Incident

                        WHERE Status <> 'Resolved' AND Status <> 'Closed'

                         

                        and then map a "Priority" field in the data model that looked like this:

                         

                        CASE INCIDENT.REPORTEDPRIORITY WHEN 1 THEN 'Critical' WHEN 2 THEN 'Resolved' WHEN 3 THEN 'Medium' WHEN 4 THEN 'Low' END

                         

                        But, even then that is just counting the total number of open/active tickets in the system by priority.  I wouldn't hard code/map this into the data model, since that would likely have to be its own Xtraction View with little benefit, considering how easy it is to duplicate the same logic in the web front end using more traditional modeling.

                         

                        Assuming you have an Incident view that starts with the Incident table:

                        • You map the Reported Priority field with the case statement above
                        • You map an "Active" field with the expression CASE WHEN Status = 'Resolved' THEN 'No' WHEN Status = 'Closed' THEN 'No' ELSE 'Yes' END
                        • In the web interface, set the filter Active Equals Yes and then add a pie chart on Reported Priority

                         

                        This method gives you additional flexibility.  Suppose someone wanted the count of open incidents by priority for only 1 assigned group or only 1 ticket category or by priority by group for all groups?  With the Union query example, you would have to go back and create different versions of your query for every different variation folks asked for.  With this more traditional/intended method, the user is free to add filters and/or slice and dice the results in many different ways in the web front end without requiring you to go back and do more data modeling on the backend.

                        1 of 1 people found this helpful
                        • 9. Re: Can we place queries in datamodeleditor to extract the required data?
                          vamseekp Apprentice

                          Thanks Gregg!

                          But it works for REPORTEDPRIORITY instead of INCIDENT.REPORTEDPRIORITY.

                          • 10. Re: Can we place queries in datamodeleditor to extract the required data?
                            GreggSmith SupportEmployee

                            Well, the "INCIDENT." portion should be the ALIAS name for the table. So, if the alias is "Table" as in your screenshot above, you would need to use that.  However, I'm pretty sure that TABLE is a reserved word, so your alias would need to be [Table] or changed to something else that is not a reserved word (like, INCIDENT) .

                             

                            While it may have worked to just use REPORTEDPRIORITY, this may lead to future problems if that same field exists in other tables used in subsequent queries that Xtraction generates.  It is always a good idea to have the field expressions reference fields as ALIAS.FIELDNAME.

                            1 of 1 people found this helpful