Home » SQL & PL/SQL » SQL & PL/SQL » New newbie faces SQL with subSELECTs & multiple joins
New newbie faces SQL with subSELECTs & multiple joins [message #198000] Fri, 13 October 2006 13:36 Go to next message
RickLovesKDE
Messages: 2
Registered: October 2006
Location: Detroit MI USA
Junior Member
Well as a newbie to SQL and Oracle, naturally a higher up has given me a complex SQL query script to use to pull daily data from a database to put into a spreadsheet for each day of a week and save the week of spreadsheets as a single worksheet to give to another higher up.
.
I am working with an Oracle 9i database on a Solaris Unix box (the box is running 64-bit sparcv9 applications and 32-bit sparc applications).
.
I would like to present a simplified version of the script given to me, present my understanding, and would like feedback. Basically, am I right or should I find a job at Wendy's? Wink (Wendy’s is a fast food chain in the USA.)
.
Simplified SQL script:
.
SELECT a.frst_nm, a.last_nm, a.emp_num, DailyItemCount, ProdDetailCount
FROM
   (
   SELECT COUNT(daily_prod.item_num) AS DailyItemCount, emp.emp_num, emp.frst_nm, emp.last_nm
   FROM
   daily_prod
   JOIN emp ON daily_prod.lst_updt_emp_num = emp.emp_num
   WHERE
   trunc(daily_prod.lst_updt_dt) >= to_date('16-SEP-06') AND Length(daily_prod.lst_updt_emp_num) > 0
   GROUP BY emp.emp_num, emp.frst_nm, emp.last_nm
   ) a

   LEFT JOIN
   (
   SELECT COUNT(daily_prod_dtl.item_dtl_num) AS ProdDetailCount, emp.emp_num, emp.frst_nm, emp.last_nm
   FROM
   daily_prod
   JOIN daily_prod_dtl ON daily_prod.item_num = daily_prod_dtl.item_num
   JOIN emp ON daily_prod_dtl.lst_updt_emp_num = emp.emp_num
   WHERE
   trunc(daily_prod.lst_updt_dt) >= to_date('16-SEP-06') AND Length(daily_prod_dtl.lst_updt_emp_num) > 0
   GROUP BY emp.emp_num, emp.frst_nm, emp.last_nm
   ) b ON a.emp_num = b.emp_num

ORDER BY a.emp_num

.
Okay, the daily_prod.lst_updt_dt is an Oracle DATE field which also includes a hidden timestamp.
The trunc() function removes that hidden timestamp so that meaningful date vs. date testing can occur.
.
Please focus on the ">=" in the 2 date vs. date tests.
My thought: If you are interested in a single day, then should not the 2 date tests just have an "="?
.
When I ran the real first subSELECT by itself with ">=", it pulled in 30,000 + rows (aka, records).
When I ran the real first subSELECT by itself with only "=", it pulled in 707 rows (aka, records).
Am I right, or am I wrong? Give me courage to talk to higher ups. Wink

Thanks, Rick
Re: New newbie faces SQL with subSELECTs & multiple joins [message #198003 is a reply to message #198000] Fri, 13 October 2006 14:15 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, you are correct - a filter for a single day would have an equals comparison, not greater than or equals.

If this date column is not indexed and/or the size of the table is relatively "small" (very subjective term), then the trunc of the date column is fine.

If this is a huge table and there is an index starting with this date column, then you would not want to trunc the date column because that will not use the index. Instead:

(    daily_prod.lst_updt_dt >= to_date('16-SEP-06', 'DD-MON-RR')
 and daily_prod.lst_updt_dt <  to_date('17-SEP-06', 'DD-MON-RR'))

or

daily_prod.lst_updt_dt between to_date('16-SEP-06', 'DD-MON-RR') and to_date('16-SEP-06 11:59:59pm', 'DD-MON-RR HH:MI:SSpm'))


Also, be sure to explicitly use format masks in those TO_DATE conversions instead of relying on the default nls_date_format setting.
Re: New newbie faces SQL with subSELECTs & multiple joins [message #198017 is a reply to message #198000] Fri, 13 October 2006 17:19 Go to previous messageGo to next message
RickLovesKDE
Messages: 2
Registered: October 2006
Location: Detroit MI USA
Junior Member
Thanks Todd for the useful info.
.
By the way, one of the indexes for the table DLY_UTLZ (it has several) is created using this function TRUNC("DLY_UTLZ_DT",'fmmonth').
.
What is the 'fmmonth' about? I have 2 Oracle books and can not find a reference to it.
.
Rick
Re: New newbie faces SQL with subSELECTs & multiple joins [message #198046 is a reply to message #198017] Sat, 14 October 2006 01:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Normally fm is added to a date or number formatmask to strip spaces:
SCOTT@xe>select '#'||to_char(sysdate, 'MONTH')||'#' from dual;

'#'||TO_CHA
-----------
#OCTOBER  #

SCOTT@xe>select '#'||to_char(sysdate, 'fmMONTH')||'#' from dual;

'#'||TO_CHA
-----------
#OCTOBER#

In the case you describe it has no meaning whatsoever.
trunc(date, <unit>) rounds a date down to the lower <unit>, so trunc(sysdate, 'MONTH') returns the first day of this month.
There is no point in adding the fm here.
Previous Topic: PRAGMA
Next Topic: how to install d2k
Goto Forum:
  


Current Time: Sat Dec 03 04:15:31 CST 2016

Total time taken to generate the page: 0.07839 seconds