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?
0
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.
0
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
0
Christoffer SwanströmPartnerCommented:
What do the tables drivers, locations, qc_daily and qc_dept_subtypes look like?
0
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
0
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?
0
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
0
anumosesAuthor Commented:
I can do better with spread sheet
0
anumosesAuthor Commented:
excel sheet enclosed
expected-data.xls
0
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

0

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
0
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.
0
anumosesAuthor Commented:
let me know what more info I can provide.
0
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?
0
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
0
anumosesAuthor Commented:
thanks a lot
0
Christoffer SwanströmPartnerCommented:
You're welcome
0
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.