Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

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
Avatar of Sean Stuber
Sean Stuber

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?
Avatar of anumoses

ASKER

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.
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
Is there any connection between dept_id and either drive or location? If not, what you are asking for does not sound feasible.
What do the tables drivers, locations, qc_daily and qc_dept_subtypes look like?
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
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?
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
I can do better with spread sheet
excel sheet enclosed
expected-data.xls
ASKER CERTIFIED SOLUTION
Avatar of Christoffer Swanström
Christoffer Swanström
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am only getting 6 distinct drives but there are 10 drives for today
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.
let me know what more info I can provide.
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?
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
thanks a lot
You're welcome