quote:

---

Originally posted by khtan

Maybe you can tell us specifically what problem are facing ? What you are asking here is way too general for us to help you (that's why you didn't get any response from your previous thread).

You don't know how to write a stored procedure ? You have problem with the syntax ? Do post your code here so that we can help you.

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

Maybe you can tell us specifically what problem are facing ? What you are asking here is way too general for us to help you (that's why you didn't get any response from your previous thread).

You don't know how to write a stored procedure ? You have problem with the syntax ? Do post your code here so that we can help you.

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by khtan

Maybe you can tell us specifically what problem are facing ? What you are asking here is way too general for us to help you (that's why you didn't get any response from your previous thread).

You don't know how to write a stored procedure ? You have problem with the syntax ? Do post your code here so that we can help you.

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by avijit111@gmail.com

i m workind as a dba in a company where user using erp......
pleeeeezz tell me the way how to write the strong stored procedured on sql server 2000 ..........because i m new in sql server2000
currently i m working on po-pr module.......
create table #temp
(
company_code GLCOMPANY,
locn_no GLLOCN,
po_no cmn_tranno,
po_date datetime,
stock_no IMSSTKNO,
vendor_code cmn_cust_ven_code,
vendor_name CMN_NAME,
purchase_uom IMSUOMNO,
ordered_qty_puom cmn_Pqty,
amend_no cmn_slno

)




insert into #temp(company_code,locn_no,po_no,po_date,vendor_code,vendor_name,stock_no,purchase_uom,ordered_qty_puom,amend_no)
select a.company_code,a.locn_no,a.po_no,a.po_date,a.vendor_code,vendor_name,b.stock_no,b.purchase_uom,b.ordered_qty_puom,a.amend_no
from pur_po_header a(nolock), pur_po_detail b(nolock),c.pur_company_vendor_master(nolock)
where a.company_code =@company_no
and b.locn_no = @location_no
and a.po_no = b.po_no
and a.po_date between @date_from and @date_to

update #temp
set vendor_name= c.vendor_name
from pur_company_vendor_master
where a.vendor_code=c.vendor_code

select *from #temp
end
how do i write the update code????????where in a,b,c the common object is vendor_code...please help me out.....

---

quote:

---

Originally posted by avijit111@gmail.com

quote:

---

Originally posted by avijit111@gmail.com

i m workind as a dba in a company where user using erp......
pleeeeezz tell me the way how to write the strong stored procedured on sql server 2000 ..........because i m new in sql server2000
currently i m working on po-pr module.......
create table #temp
(
company_code GLCOMPANY,
locn_no GLLOCN,
po_no cmn_tranno,
po_date datetime,
stock_no IMSSTKNO,
vendor_code cmn_cust_ven_code,
vendor_name CMN_NAME,
purchase_uom IMSUOMNO,
ordered_qty_puom cmn_Pqty,
amend_no cmn_slno

)




insert into #temp(company_code,locn_no,po_no,po_date,vendor_code,vendor_name,stock_no,purchase_uom,ordered_qty_puom,amend_no)
select a.company_code,a.locn_no,a.po_no,a.po_date,a.vendor_code,vendor_name,b.stock_no,b.purchase_uom,b.ordered_qty_puom,a.amend_no
from pur_po_header a(nolock), pur_po_detail b(nolock),c.pur_company_vendor_master(nolock)
where a.company_code =@company_no
and b.locn_no = @location_no
and a.po_no = b.po_no
and a.po_date between @date_from and @date_to

update #temp
set vendor_name= c.vendor_name
from common..pur_company_vendor_master,pur_po_header a
where a.vendor_code=c.vendor_code

select *from #temp
end
/********************************/
in the above update code, below given in quotion the from line "pur_po_header a" if i will declare the #temp table name then the answer is correct if i will write the "pur_po_header a" below witten code the all vendor_name is same compare to vendor_code
''from common..pur_company_vendor_master,pur_po_header a''
please help me to know why is this happning....please.........

---

---

quote:

---

Originally posted by tosscrosby

Something like this MIGHT work and allow you to elimnate the update altogether.

insert into #temp(company_code,locn_no,po_no,po_date,vendor_code,vendor_name,stock_no,
purchase_uom,ordered_qty_puom,amend_no)
select a.company_code,a.locn_no,a.po_no,a.po_date,a.vendor_code,
c.vendor_name,b.stock_no,b.purchase_uom,b.ordered_qty_puom,a.amend_no
from pur_po_header a
inner join pur_po_detail b
on a.po_no = b.po_no
inner join c.pur_company_vendor_master
on a.vendor_code=c.vendor_code
where a.company_code =@company_no
and b.locn_no = @location_no
and a.po_date between @date_from and @date_to


If you provided DDL for your tables and sample data for each of the tables we can provide better assitance. Also, instead of quoting your own reply and updating it with something you fogot, edit the original reply.

Terry

-- Procrastinate now!

---

quote:

---

Originally posted by tosscrosby

Something like this MIGHT work and allow you to elimnate the update altogether.

insert into #temp(company_code,locn_no,po_no,po_date,vendor_code,vendor_name,stock_no,
purchase_uom,ordered_qty_puom,amend_no)
select a.company_code,a.locn_no,a.po_no,a.po_date,a.vendor_code,
c.vendor_name,b.stock_no,b.purchase_uom,b.ordered_qty_puom,a.amend_no
from pur_po_header a
inner join pur_po_detail b
on a.po_no = b.po_no
inner join c.pur_company_vendor_master
on a.vendor_code=c.vendor_code
where a.company_code =@company_no
and b.locn_no = @location_no
and a.po_date between @date_from and @date_to


If you provided DDL for your tables and sample data for each of the tables we can provide better assitance. Also, instead of quoting your own reply and updating it with something you fogot, edit the original reply.

Terry

-- Procrastinate now!

---

quote:

---

Originally posted by tosscrosby

You still didn't show your DDL for any of your tables. Why not simply do the following?

select a.company_code,a.locn_no,a.po_no,a.po_date,a.vendor_code,
c.vendor_name,b.stock_no,b.purchase_uom,b.ordered_qty_puom,a.amend_no,c.vendor_add1
into #temp
from pur_po_header a inner join pur_po_detail b
on a.po_no = b.po_no inner join common..pur_company_vendor_master c
on a.vendor_code = c.vendor_code
where a.company_code = @company_no
and b.locn_no = @location_no
and a.po_date between @date_from and @date_to

This way, if you make changes to field lengths or types on your "production" tables, they are automatically reflected on your #temp table.




Terry

-- Procrastinate now!

---