anumoses
asked on
oracle query help
select distinct a.drive_id,a.drive_date,lo cation_nam e,d.qc_typ e_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/d d/rrrr') = to_char(sysdate,'mm/dd/rrr r')
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_SUBT YPE_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
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/d
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_SUBT
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
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.
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.
ASKER
Like if we see out testing data for yesterday
select distinct c.dept_id,a.drive_id,a.dri ve_date,lo cation_nam e,d.qc_typ e_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/d d/rrrr') = to_char(sysdate-1,'mm/dd/r rrr')
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
select distinct c.dept_id,a.drive_id,a.dri
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/d
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?
ASKER
select distinct d.department_id,a.drive_id ,a.drive_d ate,locati on_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/d d/rrrr') = to_char(sysdate,'mm/dd/rrr r')
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
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/d
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?
ASKER
When I have dept 95 I expect
DRIVE_ID|DRIVE_DATE|LOCATI ON_NAME|QC _TYPE_ID|Q C_SUBTYPE_ ID
DRV2012940|12/14/2011|MEDE LA, 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|CHIC AGO EXTRUDED METALS
DRV2013928|12/14/2011|AUTO TRUCK
DRV2013929|12/14/2011|SAIN T JOSEPH CATHOLIC CHURCH
DRV2013931|12/14/2011|CVS PHARMACY - GENEVA
DRV2013932|12/14/2011|KAPL AN COLLEGE
DRV2013934|12/14/2011|PLAI NFIELD NORTH HIGH SCHOOL
DRV2013938|12/14/2011|ST. EDWARD CENTRAL CATHOLIC HIGH SCHOOL
DRV2013967|12/14/2011|CANT ERA TWO
DRV2016048|12/14/2011|ASSU MPTION PARISH
------------
similarly for all drive ids
DRIVE_ID|DRIVE_DATE|LOCATI
DRV2012940|12/14/2011|MEDE
|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|CHIC
DRV2013928|12/14/2011|AUTO
DRV2013929|12/14/2011|SAIN
DRV2013931|12/14/2011|CVS PHARMACY - GENEVA
DRV2013932|12/14/2011|KAPL
DRV2013934|12/14/2011|PLAI
DRV2013938|12/14/2011|ST. EDWARD CENTRAL CATHOLIC HIGH SCHOOL
DRV2013967|12/14/2011|CANT
DRV2016048|12/14/2011|ASSU
------------
similarly for all drive ids
ASKER
I can do better with spread sheet
ASKER
excel sheet enclosed
expected-data.xls
expected-data.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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?
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?
ASKER
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.dri ve_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/d d/rrrr') = to_char(sysdate,'mm/dd/rrr r')
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
SELECT distinct e.dept_id,A.drive_id,A.dri
location_name, e.qc_type_id,e.qc_subtype_
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/d
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
ASKER
thanks a lot
You're welcome
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?