April 18, 2011 at 10:54 am
I'm trying to combine the data from one dataset into another. I was able to do a lookup with a different dataset and pull the data I needed. However, I can't figure out how to pull the data from another dataset to add it to the current dataset. Below are my datasets. The first one is the main dataset that is used in my table. The second dataset is what I need to add the table.
SELECT (CASE Circumstance WHEN '01' THEN 'Vehicle Defects' WHEN '02' THEN 'Traffic Control Missing/Inoperable' WHEN '03' THEN 'Improperly Stopped on Roadway'
WHEN '04' THEN 'Speed' WHEN '06' THEN 'Improper Passing' WHEN '07' THEN 'Violation Stop Sign/Signal' WHEN '08' THEN 'Wrong Side Not Passing'
WHEN '09' THEN 'Following Too Close' WHEN '10' THEN 'Improper Signal' WHEN '11' THEN 'Improper Backing' WHEN '12' THEN 'Improper Turn' WHEN
'13' THEN 'Improper Lane Usage/Change' WHEN '14' THEN 'Wrong Way One Way Street' WHEN '15' THEN 'Improper Start From Park' WHEN '16' THEN
'Improperly Parked' WHEN '17' THEN 'Failed To Yield' WHEN '18' THEN 'Drinking' WHEN '19' THEN 'Drugs' WHEN '20' THEN 'Physical Impairment' WHEN
'21' THEN 'Inattention' WHEN '22' THEN 'Twenty-two' WHEN 'U ' THEN 'U' END) AS "CRSHCIRCUM", ACCNT
FROM TABLE
(SELECT CASE QO05DR_VH_CONT_CIRC WHEN '05' THEN '04' ELSE QO05DR_VH_CONT_CIRC END AS Circumstance,
COUNT(DISTINCT a.QO01ACCIDENT_NO) AS ACCNT
FROM XTECH.TR10TBACCIDENT a, XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
WHERE (QO01ACCIDENT_YEAR IN ('2008', '2009', '2010')) AND (QO01SUBMIT_AGENCY_ORI = 'XXXXXXXXX') AND
(vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) OR
(QO01ACCIDENT_YEAR IN ('2008', '2009', '2010')) AND (vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) AND (QO01MUNICIPALITY = 'XXXX')
GROUP BY CASE QO05DR_VH_CONT_CIRC WHEN '05' THEN '04' ELSE QO05DR_VH_CONT_CIRC END) AS "DATA"
SELECT COUNT(DISTINCT QO01ACCIDENT_NO) AS TOTCRSH
FROM XTECH.TR10TBACCIDENT a, XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
WHERE (QO01SUBMIT_AGENCY_ORI = 'MO0840100') AND (QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (a.QO01ACCIDENT_NO = vc.FK_QO01ACCIDENT_NO) OR
(QO01ACCIDENT_YEAR = '2008' OR
QO01ACCIDENT_YEAR = '2009' OR
QO01ACCIDENT_YEAR = '2010') AND (a.QO01ACCIDENT_NO = vc.FK_QO01ACCIDENT_NO) AND (QO01MUNICIPALITY = '0230')
April 18, 2011 at 11:30 am
Combine? Try:
select * from
( <your 1st query>) a
inner join
( <your 2nd query>) b on 1=1
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 18, 2011 at 12:12 pm
What field would I join on? There are multiple fields in the first dataset and only one field in the second dataset.
April 18, 2011 at 12:18 pm
This is my dilemna. The first dataset which I use for the report is doing a count of the ACCIDENT_NO based on a cirumstance. So, there could be many circumstances for one accident. I got the numbers I need for that. I need to calculate the second dataset just soley on ACCIDENT_NO. So, regardless of how many circumstances contributed to the accident, I just need to count the accident once. I wasn't sure how to do this in one dataset, so that's why I'm trying it in two. Any suggestions would be much appreciated.
April 18, 2011 at 12:33 pm
db2mo (4/18/2011)
What field would I join on? There are multiple fields in the first dataset and only one field in the second dataset.
None, just paste your queries where I have the brackets. Your second query only returns 1 value, right? If your first query runs (which I don't see how), then joining the two will place the totals on the end of each line of the first query.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 18, 2011 at 12:58 pm
I'm not getting it to work. I'm getting an error that the name of my query is undefined.
April 18, 2011 at 1:21 pm
Copy and paste what you currently have.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 18, 2011 at 1:34 pm
I type my query names into the parentheses then click on Query Designer and it automatically changes it to this:
SELECT *
FROM (CircumstanceByCrashSeverityData2) a INNER JOIN
(TotalCrashes) b ON 1 = 1
April 18, 2011 at 1:47 pm
Ah, I thought you would copy the whole queries from the first post into the parenthesis. If these are views, then just remove the parenthesis.
SELECT *
FROM CircumstanceByCrashSeverityData2 a INNER JOIN
TotalCrashes b ON 1 = 1
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 18, 2011 at 1:57 pm
Okay, I inserted the full queries into the brackets and it worked. I'm now gettting to total I need. I'm sure I'll have more questions. Sorry for my newness to this and thanks very much for your help.:-D
April 18, 2011 at 1:59 pm
It didn't take long. Next question already. Is this limited to the two datasets or can I do multiple? I have four datasets I'd like to combine into the one if possible.
April 18, 2011 at 3:53 pm
Yes, you can tack on as many as you like if your additional queries return one value. If they contain more than one row, your results will cross-multiply and we'll have to look at it from a different angle.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 19, 2011 at 7:05 am
Two of the four datasets I want to combine have two fields, the other two have one field.
April 19, 2011 at 9:27 am
Multiple columns (not fields) won't matter. Try it out and you'll see that they are tacked on to the end of the rows just like a scalar total. You'll only have a problem when the new query has multiple rows.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply