June 9, 2011 at 12:01 am
I have a simple query ( Part of a procedure). Code Given Below
update #temp
set #temp.Quantity_mc = a.Total
from #temp Inner join
(
select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn
-- WHERE
-- NOT EXISTS(SELECT * FROM common..ims_variant_master Where description like 'optional%' AND
-- stock_no =(SELECT q.psp_item_no from pmddb..pmd_mpsp_ps_postn q where
-- --psp_ps_no = a.psp_ps_no and
-- q.psp_item_no = item_code AND
-- q.psp_item_var = variant_code and
-- q.psp_io_flag = 'o'))
--
group by psp_item_no,psp_item_var
) as a
on item_code = a.psp_item_no AND
variant_code=a.psp_item_var
Suppose item_code =DRR01DELT0004 and variant_code = ##
And by this group I am getting below Data Set
Psp_ps_no psp_item_no psp_item_var psp_qty_prpnl
6 DRR01DELT0004 ## 10.00000000
352 DRR01DELT0004 ## 10.00000000
353 DRR01DELT0004 ## 6.00000000
I want a condition which will avoid the 352 row because 352 has optional in its description field in another Table.
How to do it ?:hehe:
June 9, 2011 at 12:31 am
An AND clause to the WHERE clause, may be??
Like, WHERE Description = 'Optional' ??
June 9, 2011 at 12:58 am
beacause 352 OPTIONAL AUTO WIRING FOR DRR (ARAI)
in descriotion field in another table
June 9, 2011 at 1:02 am
I dont get clearly what u mean by another table. can u describe more ? Sample table(s), some sample data and expected result, to start with??
June 9, 2011 at 1:21 am
Psp_ps_no psp_item_no psp_item_var psp_qty_prpnl
6 DRR01DELT0004 ## 10.00000000
352 DRR01DELT0004 ## 10.00000000
353 DRR01DELT0004 ## 6.00000000
I want to filter pmddb..pmd_mpsp_ps_postn table
where Psp_ps_no =352 AND
psp_item_var =## AND
psp_io_flag = 'o'
This will give the result :
psp_item_no = DRR24D
psp_item_var =##
then I want to check
NOT EXISTS(SELECT * FROM common..ims_variant_master
Where description like 'optional%' and
stock_no =DRR24D and variant_no = ##)
I will add more if you need:hehe:
June 9, 2011 at 3:29 am
Sample Data in the attachment
June 9, 2011 at 3:00 pm
something like:
select pmd.psp_item_no,pmd.psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn pmd
--== added freshly
left join common..ims_variant_master ims
on ims.psp_ps_no = pmd.psp_ps_no
where ims.description NOT LIKE '%OPTIONAL%'
--==
group by psp_item_no,psp_item_var
June 10, 2011 at 12:28 am
Thanks for reply..
Plz look my code below :
update #temp
set #temp.Quantity_mc = a.Total
from #temp Inner join
(
select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn
group by psp_item_no,psp_item_var
) as a
on item_code = a.psp_item_no AND
variant_code=a.psp_item_var
This WAS my 1st code
Where #temp table getting Quantity_mc =26 against the item_code =DRR01DELT0004 because by this group I'm getting below data set :
Psp_ps_no psp_item_no psp_item_var psp_qty_prpnl
6 DRR01DELT0004 ## 10.00000000
352 DRR01DELT0004 ## 10.00000000
353 DRR01DELT0004 ## 6.00000000
Now I want that Psp_ps_no =352 should not come in this grouping becasue If I filter the pmddb..pmd_mpsp_ps table as below
Select psp_item_no from pmddb..pmd_mpsp_ps where
Psp_ps_no =352 and
psp_io_flag = 'o'
I'm getting psp_item_no = DRR24D and this Item code has OPTIONAL in its description column in the common..ims_variant_master table .
How to do rest of the things ? Kindly reply.
Thanks!
June 10, 2011 at 4:16 am
ColdCoffee (6/9/2011)
something like:
select pmd.psp_item_no,pmd.psp_item_var,sum(psp_qty_prpnl) as Totalfrom pmddb..pmd_mpsp_ps_postn pmd
--== added freshly
left join common..ims_variant_master ims
on ims.psp_ps_no = pmd.psp_ps_no
where ims.description NOT LIKE '%OPTIONAL%'
--==
group by psp_item_no,psp_item_var
I have modified the code and it seems to me working fine.
select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn pmd
where NOT EXISTS (SELECT * From common..ims_variant_master
ims,pmddb..pmd_mpsp_ps_postn pmd2
where description like 'Optional%' and
ims.stock_no= pmd2.psp_item_no and
pmd2.psp_ps_no = pmd.psp_ps_no and
pmd2.psp_io_flag = 'o'
)
group by psp_item_no,psp_item_var
June 29, 2011 at 4:28 am
update #temp
set #temp.Quantity_mc = a.Total
from #temp Inner join
(
select psp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
from pmddb..pmd_mpsp_ps_postn pmd
WHERE NOT EXISTS (SELECT * From common..ims_variant_master ims,pmddb..pmd_mpsp_ps_postn pmd2
where description like 'Optional%' and
ims.stock_no= pmd2.psp_item_no and
pmd2.psp_ps_no = pmd.psp_ps_no and
pmd2.psp_io_flag = 'o'--and pmd2. psp_ps_no = @BomNo2
)
group by psp_item_no,psp_item_var
) as a
on #temp.item_code = a.psp_item_no AND
#temp.variant_code=a.psp_item_var
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply