cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple field mappings?

Former Member
0 Kudos

Post Author: cr_nub

CA Forum: Data Connectivity and SQL

I am new to CR and SQL so I hope I don't sounds too noobish with this problem.

I am trying to pull a dataset that will yield 3 sets of data to be used for running totals using CR XIWhile there are other fields in the tables, the ones shown are the ones I need to use to select my data.

Table 1 - INCIDENTS Table 2 - REPS Table 3 - HISTORY_REPID_AUTHOR ID ID_OWNERID_CLOSED_BY INCIDENT_NUMBERNUMBER DT_CREATEDDT_CREATEDDT_CLOSED

I want to run a report with 3 seperate running totals. Each RT is set to reset on change of group REPS.ID

Opened: Distinct count of incidents.number where incidents.dt_created is between two dates and reps.id is in a list{INCIDENTS.DT_CREATED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{INCIDENTS.ID_AUTHOR} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

Closed: Distinct count of incidents.number where incidents.dt_closed is between two dates and reps.id is in a list{INCIDENTS.DT_CLOSED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{INCIDENTS.ID_CLOSED_BY} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

Modified: Distinct count of history_rep.incident_number where history_rep.dt_created is between two dates and history_rep.id_owner is in a list{HISTORY_REP.DT_CREATED} in DateTime (2007, 08, 01, 00, 00, 00) to DateTime (2007, 08, 31, 23, 59, 59) and{HISTORY_REP.ID_OWNER} in [135, 382, 383, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89]

My problem is how to link my fields so that my resulting data can yield data that meets all of the criteria:INCIDENTS.ID_AUTHOR = REPS.ID orINCIDENTS.ID_CLOSED_BY = REPS.ID orHISTORY_REP.ID_OWNER = REPS.ID

I also need an inner join between INCIDENTS.NUMBER and HISTORY_REP.DENT_NUMBER since not all tickets have history entries.

Inner joins only yield results which match all of the linked criteria which does not help.

I tried adding an additional alias for the INCIDENTS table and used INCIDENTS to link the ID_AUTHOR field and then the INCIDENTS_1 table to link the ID_CLOSED_BY field.This worked to give me the first 2 running totals (with increased record searching), but adding the third set resulted in way to many records being read.

Is there a way to do this purely through SQL so I can bypass linking the tables? Again, I'm new to SQL and still waiting for boss to get me training.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Post Author: cr_nub

CA Forum: Data Connectivity and SQL

Thank you for the feedback. The "Add Command" was what I needed but being new did not know how to use it. I spent a lot of time researching and testing and came up with an SQL statement that gave me all of the data I needed. Not sure if it is setup correctly for optimal processing but it does the trick.

I posted the final statement for reference or in case somebody knows a more optimal way to code this.

SELECT INCIDENTS.DT_CREATED, DT_CLOSED, ID_AUTHOR, ID_CLOSED_BY, NUMBER, REPS.ID, FNAME, LNAME, HISTORY_REP.DT_CREATED, ID_OWNER from INCIDENTS LEFT OUTER JOIN HISTORY_REP ON INCIDENTS.NUMBER = HISTORY_REP.INCIDENT_NUMBER, REPSWHERE(INCIDENTS.ID_AUTHOR = REPS.ID)AND(REPS.ID=78 OR REPS.ID=79 OR REPS.ID=80 OR REPS.ID=81 OR REPS.ID=82 OR REPS.ID=83 OR REPS.ID=84 OR REPS.ID=85 OR REPS.ID=86 OR REPS.ID=87 OR REPS.ID=88 OR REPS.ID=89 OR REPS.ID=135 OR REPS.ID=382 OR REPS.ID=383)AND(INCIDENTS.DT_CREATED between {ts '2007-08-01 00:00:00'} AND {ts '2007-09-01 00:00:00'})

UNION

SELECT INCIDENTS.DT_CREATED, DT_CLOSED, ID_AUTHOR, ID_CLOSED_BY, NUMBER, REPS.ID, FNAME, LNAME, HISTORY_REP.DT_CREATED, ID_OWNER from INCIDENTS LEFT OUTER JOIN HISTORY_REP ON INCIDENTS.NUMBER = HISTORY_REP.INCIDENT_NUMBER, REPSWHERE(INCIDENTS.ID_CLOSED_BY = REPS.ID)AND(REPS.ID=78 OR REPS.ID=79 OR REPS.ID=80 OR REPS.ID=81 OR REPS.ID=82 OR REPS.ID=83 OR REPS.ID=84 OR REPS.ID=85 OR REPS.ID=86 OR REPS.ID=87 OR REPS.ID=88 OR REPS.ID=89 OR REPS.ID=135 OR REPS.ID=382 OR REPS.ID=383)AND(INCIDENTS.DT_CLOSED between {ts '2007-08-01 00:00:00'} AND {ts '2007-09-01 00:00:00'})

UNION

SELECT INCIDENTS.DT_CREATED, DT_CLOSED, ID_AUTHOR, ID_CLOSED_BY, NUMBER, REPS.ID, FNAME, LNAME, HISTORY_REP.DT_CREATED, ID_OWNER from INCIDENTS LEFT OUTER JOIN HISTORY_REP ON INCIDENTS.NUMBER = HISTORY_REP.INCIDENT_NUMBER, REPSWHERE(HISTORY_REP.ID_OWNER = REPS.ID)AND(REPS.ID=78 OR REPS.ID=79 OR REPS.ID=80 OR REPS.ID=81 OR REPS.ID=82 OR REPS.ID=83 OR REPS.ID=84 OR REPS.ID=85 OR REPS.ID=86 OR REPS.ID=87 OR REPS.ID=88 OR REPS.ID=89 OR REPS.ID=135 OR REPS.ID=382 OR REPS.ID=383)AND(HISTORY_REP.DT_CREATED between {ts '2007-08-01 00:00:00'} AND {ts '2007-09-01 00:00:00'})

Former Member
0 Kudos

Post Author: pvierheilig

CA Forum: Data Connectivity and SQL

Hello - great information.

I believe that from what you have provided (short of perhaps some tweaks to the SQL statement), you can use the 'Add Command' menu item from within the Database Expert, where you'd set up a new database connection.

All of your SQL (a full SQL statement, join's included) should be able to be set within that option and provide your desired results.

Hope this helps.