oracle query help

select distinct a.drive_id,a.drive_date,location_name,d.qc_type_id,d.qc_subtype_id
  from drives@pdon_new a,locations@pdon_new b,
          qc_daily c,qc_dept_subtypes d
 where a.location_id = b.location_id
 and a.drive_id = c.drive_id(+)
 and c.qc_subtype_id = d.qc_subtype_id(+)
 and c.qc_type_id = d.qc_type_id(+)
  and to_char(a.drive_date,'mm/dd/rrrr') = to_char(sysdate,'mm/dd/rrrr')
   and a.drive_id like ('DRV2%')
   order by 1
-------------

We will not get the qc_subtype_id and qc_type_id unless the lot release is being performed in daily lot release program(table qc_daily)

qc_dept_subtypes  table has the data for qc_type_id and qc_subtype_id
For eg dept 95 (mobile dept)
 select dept_id, qc_type_id, qc_subtype_id
   from qc_dept_subtypes
   where dept_id = 95
DEPT_ID|QC_TYPE_ID|QC_SUBTYPE_ID
95|1008|1012
95|1008|1013
95|1008|1014
95|1008|1018
95|1008|1020
95|1008|1025
95|1008|1026
95|1014|1012
95|1014|1013
95|1014|1014
95|1014|1018
95|1014|1020
95|1014|1025
95|1014|1026
95|1015|1012
95|1015|1013
95|1015|1014
95|1015|1020
95|1015|1026
95|1016|1020
-------------------
I want to show all these in my previous query
mobile-drives.xls
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
can you rephrase the question?  

you have posted 2 sets of data, one text, one excel (text is better)

but there is no connection between them that I see,  the xls data has empty cells for the type and subtype columns

and the other information doesn't seem to synch.

I think your xls is the result of your first query, your text is the result of the second query, but what is the connection?

what results are you looking for given those two sets of input data?
anumosesAuthor Commented:
qc_dept_subtypes - In this table we have defined the qc_type and qc_subtype for each dept that needs to be completed.
We will find an entry in qc_daily when they start doing daily lot release.
But we need to get this query to tell QA dept that these are the qc_types and qc_subtypes to be performed each day by each dept. Hope I have answered.
anumosesAuthor Commented:
Like if we see out testing data for yesterday

select distinct c.dept_id,a.drive_id,a.drive_date,location_name,d.qc_type_id,d.qc_subtype_id
  from drives@pdon_new a,locations@pdon_new b,qc_daily c,qc_dept_subtypes d
 where a.location_id = b.location_id
 and a.drive_id = c.drive_id(+)
 and c.qc_subtype_id = d.qc_subtype_id(+)
 and c.qc_type_id = d.qc_type_id(+)
  and to_char(a.drive_date,'mm/dd/rrrr') = to_char(sysdate-1,'mm/dd/rrrr')
   and a.drive_id like ('DRV2%')
   order by 1
------------------
But I was not able to get the qc_types and qc_subtypes since I dont find entry in qc_daily

mobiles-for-yesterday.xls
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Christoffer SwanströmPartnerCommented:
Is there any connection between dept_id and either drive or location? If not, what you are asking for does not sound feasible.
Christoffer SwanströmPartnerCommented:
What do the tables drivers, locations, qc_daily and qc_dept_subtypes look like?
anumosesAuthor Commented:
select distinct d.department_id,a.drive_id,a.drive_date,location_name
  from drives@pdon_new a,locations@pdon_new b,sites s,dept_staff d
 where a.location_id = b.location_id
   and a.location_id = s.lt_location_id
   and s.site_code = d.site_code
  and to_char(a.drive_date,'mm/dd/rrrr') = to_char(sysdate,'mm/dd/rrrr')
  and a.drive_date = d.schedule_date
   and a.drive_id like ('DRV2%')
   order by 1
-------------------------
I have the query with dept id. Now I need help to get my query
mobiles-with-dept-id.xls
Christoffer SwanströmPartnerCommented:
Ok, now you have the department id and you can join to the qc_subtypes table. However, I still don't quite understand the logic of the output you expect. You seem to expect one row per drive, but your qc_subtypes table contains many entries per department. Can you elaborate a bit? What exactly do you expect as output, when you have a drive with department_id 95?
anumosesAuthor Commented:
When I have dept 95 I expect
DRIVE_ID|DRIVE_DATE|LOCATION_NAME|QC_TYPE_ID|QC_SUBTYPE_ID
DRV2012940|12/14/2011|MEDELA, INC.|1008|1012                                                   |1008|1013                                                   |1008|1018                                                   |1008|1020                                                             |1008|1026
                                                               |1014|1012                                                   |1014|1013                                                   |1014|1014                                                   |1014|1018                                                   |1014|1020                                                   |1014|1025                                                   |1014|1026                                                   |1015|1012                                                   |1015|1013                                                             |1015|1014                                                   |1015|1020                                                   |1015|1026                                                   |1016|1012                                                   |1016|1013                                                   |1016|1020                                                   |1016|1026
DRV2013414|12/14/2011|CHICAGO EXTRUDED METALS
DRV2013928|12/14/2011|AUTO TRUCK
DRV2013929|12/14/2011|SAINT JOSEPH CATHOLIC CHURCH
DRV2013931|12/14/2011|CVS PHARMACY - GENEVA
DRV2013932|12/14/2011|KAPLAN COLLEGE
DRV2013934|12/14/2011|PLAINFIELD NORTH HIGH SCHOOL
DRV2013938|12/14/2011|ST. EDWARD CENTRAL CATHOLIC HIGH SCHOOL
DRV2013967|12/14/2011|CANTERA TWO
DRV2016048|12/14/2011|ASSUMPTION PARISH
------------
similarly for all drive ids
anumosesAuthor Commented:
I can do better with spread sheet
anumosesAuthor Commented:
excel sheet enclosed
expected-data.xls
Christoffer SwanströmPartnerCommented:
How about this:
SELECT DISTINCT d.department_id,A.drive_id,A.drive_date,location_name, e.qc_type_id,e.qc_subtype_id
  from drives@pdon_new a,locations@pdon_new b,sites s,dept_staff d, qc_dept_subtypes e
 where a.location_id = b.location_id
   and a.location_id = s.lt_location_id
   and s.site_code = d.site_code
  and to_char(a.drive_date,'mm/dd/rrrr') = to_char(sysdate,'mm/dd/rrrr')
  and a.drive_date = d.schedule_date
   and a.drive_id LIKE ('DRV2%') 
	 and e.department_id = d.department_id
   order by 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
I am only getting 6 distinct drives but there are 10 drives for today
Christoffer SwanströmPartnerCommented:
You really have to provide a bit more information here... can you give the complete structure of the tables you are talking about, as well as the contents at least for a subset of the data you are dealing with. Without that it's going to be pretty close to impossible to help you out.
anumosesAuthor Commented:
let me know what more info I can provide.
Christoffer SwanströmPartnerCommented:
As I said, the structure of the tables (column names and datatypes), as well as sample contents (give a complete example: contents of all involved tables for e.g. one drive).

Also, the purpose of your query would be good to know: what is the information that you need to get out of the query and what is someone going to do based on that information?
anumosesAuthor Commented:
Your query worked for me. I tweeked it a little more to add some more combinations. Yet got the same data.

SELECT distinct e.dept_id,A.drive_id,A.drive_date,
                location_name, e.qc_type_id,e.qc_subtype_id ,y.description
  from drives@pdon_new a,locations@pdon_new b,
       sites s,dept_staff d, qc_dept_subtypes e,qc_subtype y
 where a.location_id = b.location_id
   and a.location_id = s.lt_location_id
   and s.site_code = d.site_code
  and to_char(a.drive_date,'mm/dd/rrrr') = to_char(sysdate,'mm/dd/rrrr')
  and a.drive_date = d.schedule_date
   and a.drive_id LIKE ('DRV2%')
   and e.dept_id = d.department_id
   and e.qc_subtype_id = y.qc_subtype_id
   order by a.drive_id,y.description
anumosesAuthor Commented:
thanks a lot
Christoffer SwanströmPartnerCommented:
You're welcome
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.