Home » SQL & PL/SQL » SQL & PL/SQL » Sql query related issue (Sql * plus,Oracle apps 11.5.10,oracle 10g database,Windows Xp Client )
Sql query related issue [message #429834] Thu, 05 November 2009 23:05 Go to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
Dear all,

This is vizith kumar i had small doubt in an sql query.I had sql query with rank concept in it.

Quote:

select
item_no
,whse_code
,lot_no
,round(unit_cost,2) UNIT_COST
,qty
,round(cost,2) COST
,rank
from
(select item.item_no
,lot.lot_no,
c.whse_code,
c.unit_cost,
temp.qty,
round(c.unit_cost*temp.qty,2)cost,
rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <=(to_date('&&1','DD-MON-RR') + (1-1/24/60/60))and c.cost_mthd_code = 'TLC')
where rank=1;


Quote:


Qry2:

select
item_no
,whse_code
,lot_no
,round(unit_cost,2) UNIT_COST
,qty
,round(cost,2) COST
,rank
from
(select item.item_no
,lot.lot_no,
c.whse_code,
c.unit_cost,
temp.qty,
round(c.unit_cost*temp.qty,2)cost,
rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <=(to_date('&&1','DD-MON-RR') + (1-1/24/60/60))and c.cost_mthd_code = 'TLC')
where rank=1;



pls can any one suggest what way these queries are diffrent because I am getting huge cost diffrence in these two.

Thanks in advance

regards
vizith kumar
Re: Sql query related issue [message #429848 is a reply to message #429834] Fri, 06 November 2009 00:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you posted two identical queries, I find it hard to believe that there is any difference in cost between the two
Re: Sql query related issue [message #429849 is a reply to message #429834] Fri, 06 November 2009 00:08 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Vizith wrote on Thu, 05 November 2009 23:05


pls can any one suggest what way these queries are diffrent because I am getting huge cost diffrence in these two.

Thanks in advance

regards
vizith kumar


Attached document not suported unfortunetly.
Files are identical.
Just 5 sec late with frank... Smile

Sriram

[Updated on: Fri, 06 November 2009 00:13]

Report message to a moderator

Re: Sql query related issue [message #429851 is a reply to message #429848] Fri, 06 November 2009 00:09 Go to previous messageGo to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
sorry these two are the queries am extreemly sorry for my mistake.

Quote:


select * from
(select item.item_no ,lot.lot_no, c.whse_code,c.unit_cost,temp.qty,round(c.unit_cost*temp.qty,2)cost,
rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst
lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <= to_date ('24-AUG-2009 23:59:59','dd-mon-yyyy hh24:mi:ss')
and c.cost_mthd_code = 'TLC')
where rank=1

select
item_no
,whse_code
,lot_no
,round(unit_cost,2) UNIT_COST
,qty
,round(cost,2) COST
,rank
from
(select item.item_no
,lot.lot_no,
c.whse_code,
c.unit_cost,
temp.qty,
round(c.unit_cost*temp.qty,2)cost,
rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <=(to_date('&&1','DD-MON-RR') + (1-1/24/60/60))and c.cost_mthd_code = 'TLC')
where rank=1;



regards
vizith kumar
Re: Sql query related issue [message #429852 is a reply to message #429834] Fri, 06 November 2009 00:10 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
back to work

[Updated on: Fri, 06 November 2009 00:11]

Report message to a moderator

Re: Sql query related issue [message #429853 is a reply to message #429851] Fri, 06 November 2009 00:13 Go to previous messageGo to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
dear all,

Till august 24th 2009 first report returning me 441075 rows and second query retruning 441073 but cost(specified in the report) diffrence is 39 crores. we will execute this script after our lacp process completion.

regards
vizith kumar
Re: Sql query related issue [message #429854 is a reply to message #429853] Fri, 06 November 2009 00:15 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
(TO_DATE ('&&1', 'DD-MON-RR') + (1 - 1 / 24 / 60 / 60)


what are you entering here??
Re: Sql query related issue [message #429855 is a reply to message #429854] Fri, 06 November 2009 00:18 Go to previous messageGo to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
DEAR AYUSH,

I am entering "24-AUG-2009" AND TRIED TO DISPLAY THAT INPUT PARAMETER FEILD IN MY OUTPUT ALSO THAT GIVES
24-08-2009 23:59:59.

REGARDS
VIZITH KUMAR
Re: Sql query related issue [message #429857 is a reply to message #429855] Fri, 06 November 2009 00:27 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I think the first query with hardcoded date will have lesser cost in execution plan as the optimiser knows the exact date so the exact stats.
In my view both query will take same time while execution if they are showing the same plans
But still the difference in rows I cant understand.

please post the execution plans
Re: Sql query related issue [message #429859 is a reply to message #429857] Fri, 06 November 2009 00:36 Go to previous messageGo to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
Dear Ayush,

Thanks for your reply,I have small doubt in this rank concept.Will this rank concept work diffrently if u select feild names instead of giving select * from(...
)

regards
vizith kuamr
Re: Sql query related issue [message #429865 is a reply to message #429859] Fri, 06 November 2009 00:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Will this rank concept work diffrently

No
Re: Sql query related issue [message #429867 is a reply to message #429865] Fri, 06 November 2009 00:55 Go to previous messageGo to next message
Vizith
Messages: 32
Registered: October 2007
Location: bangalore
Member
Thanks for the info ayush .. i wil check this out again and get back to you all with some data.Pls post any thing if u find wrong in these queries.

Thanks for all your patience and immediate response.

Regards
vizith kumar
Re: Sql query related issue [message #429884 is a reply to message #429851] Fri, 06 November 2009 01:49 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Vizith wrote on Fri, 06 November 2009 00:09

pls can any one suggest what way these queries are diffrent because I am getting huge cost diffrence in these two.

sorry these two are the queries am extreemly sorry for my mistake.

Quote:


select * from
(select item.item_no ,lot.lot_no, c.whse_code,c.unit_cost,temp.qty,round(c.unit_cost*temp.qty,2)cost, rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst
lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <= to_date ('24-AUG-2009 23:59:59','dd-mon-yyyy hh24:mi:ss')
and c.cost_mthd_code = 'TLC')
where rank=1

select
item_no
,whse_code
,lot_no
,round(unit_cost,2) UNIT_COST
,qty
,round(cost,2) COST
,rank
from
(select item.item_no
,lot.lot_no,
c.whse_code,
c.unit_cost,
temp.qty,
round(c.unit_cost*temp.qty,2)cost,
rank() over ( partition by c.item_id,c.lot_id,c.whse_code order by cost_date desc,header_id desc) rank
from gmf_lot_costs c,ic_item_mst_b item,ic_lots_mst lot,tnq_opm_lot_cost_temp temp
where
c.item_id=item.item_id
and lot.lot_id=c.lot_id
and item.item_id=lot.item_id
and temp.whse_code = c.whse_code
and temp.lot_no = lot.lot_no
and temp.item_no = item.item_no
and lot.lot_id > 0
and c.cost_date <=(to_date('&&1','DD-MON-RR') + (1-1/24/60/60))and c.cost_mthd_code = 'TLC')
where rank=1;



regards
vizith kumar


Quote:
ayush_anand
I think the first query with hardcoded date will have lesser cost in execution plan as the optimiser knows the exact date so the exact stats.
In my view both query will take same time while execution if they are showing the same plans
But still the difference in rows I cant understand.

please post the execution plans


Which cost your talking about? optimiser cost or your select list?

Sriram.

[Updated on: Fri, 06 November 2009 01:55]

Report message to a moderator

Previous Topic: Serially Reusable package
Next Topic: LEFT JOIN PROBLEM
Goto Forum:
  


Current Time: Mon Sep 26 07:51:08 CDT 2016

Total time taken to generate the page: 0.06699 seconds