Home » Other » Training & Certification » SQL Tuning
SQL Tuning [message #289772] Wed, 26 December 2007 01:49 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
SELECT date,emp_no,LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name,
DECODE(mail_to,'R',NVL(enc_add_1,' ')||NVL(enc_add_2,' ')||NVL(enc_add_3,' ')||NVL(enc_add_4,' '),
'O',NVL(enc_mail_add_1,' ')||NVL(enc_mail_add_2,' ')||NVL(enc_mail_add_3,' ')||NVL(enc_mail_add_4,' '),
'L',NVL(leg_add_1,' ')||NVL(leg_add_2,' ')||NVL(leg_add_3,' ')||NVL(leg_add_4,' ')) Address,
DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),
'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' ')) Phone_No,
trim(t_pic) Tin_No,
TO_CHAR(d_o_b,'DD-MON-YYYY') DOB,
trim(dr_lc_no) Driving_Licence,
trim(pass_no) Passport_No,
trim(email_id) Email_Id1,
trim(email_id1) Email_Id2
FROM emp WHERE
 LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
IN
(SELECT Name
--NVL(LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)),' ')
 FROM (SELECT COUNT(*), LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name
           FROM emp where trunc(date)='26-FEB-2007'
       GROUP BY LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
         HAVING COUNT(*)>1)
) 		 
		 OR
DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5
,' '))
IN
(SELECT ph_no
--DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' '))
   FROM (SELECT DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' ')) ph_no ,COUNT(*)
           FROM emp WHERE TRUNC(date)='26-FEB-2007'
       GROUP BY DECODE(mail_to,'R',NVL(ph_no_0,' ')||NVL(ph_no_1,' '),'O',NVL(ph_no_2,' ')||NVL(ph_no_3,' '),'L',NVL(ph_no_4,' ')||NVL(ph_no_5,' '))
         HAVING COUNT(*)>1)
) AND
TRUNC(date) ='26-FEB-2007' 
ORDER BY auth_dt


when i am execute the above query i got 3880 records but for date=''26-FEB-2007' condition the table contains only 11 records

can any one guide me and reformat the query to meet the condition




SQL> select count(*) from emp where trunc(date)='26-FEB-2007';

  COUNT(*)
----------
        11

SQL> 



select count(*)
FROM emp WHERE
LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
IN
(SELECT Name
--NVL(LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)),' ')
FROM (SELECT COUNT(*), LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name)) Name
FROM emp
GROUP BY LTRIM(RTRIM(f_name))||LTRIM(RTRIM(l_name))
HAVING COUNT(*)>1)
) and TRUNC(date) ='26-FEB-2007' 
Re: SQL Tuning [message #289777 is a reply to message #289772] Wed, 26 December 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

trunc(date)='26-FEB-2007'

you compare a string to a date, very bad, fix that.

Quote:

can any one guide me and reformat the query

Can you format your query in a pretty way so that it is readable.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

Re: SQL Tuning [message #289798 is a reply to message #289777] Wed, 26 December 2007 03:00 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
when i changed into below

trunc(date)=to_date('26-FEB-2007'
)

still now the returned record will be 3880
Re: SQL Tuning [message #289816 is a reply to message #289798] Wed, 26 December 2007 03:38 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_DATE
Datetime Format Models

Regards
Michel
Previous Topic: Query regarding correlated sub-query and top-n
Next Topic: Conditionally display previous values
Goto Forum:
  


Current Time: Fri Mar 29 07:23:54 CDT 2024