Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 11th, 2008, 08:08 PM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default [Resolved] Combine select from different platforms

I need help getting this to work. Hope I have explained the scope well enough.

I have an asp application that that connects to a sp on SQL Server 2005 that again calls a sp on SQL Server 2000 (using linked server) to select production data. I use a SqlDataAdapter to fill a DataSet and consequently a GridView.

[u]Existing SP code on Sequel Server 2005:</u>

Code:
CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare  
@JobNumber int
as
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 
GO
Result will return a Cost Code and Quantity

Note: This procedure I need to expand with an additional select from a db2 table (cost qty table on an AS400). The objective is to compare production date vs accounting data. The select element is @JobNumber for both tables. I'm not sure if need to use a temp table. There could be entries in either table that may not exists in the other.


[u]Existing SP code on Sequel Server 2000:</u>

Code:
CREATE PROCEDURE sp_Vecellio_ProductionInquiry  
@JobNumber int
as
SELECT         distinct(dbo.Item.CompanyItemId), substring(dbo.Item.Name, 1, 15) as description, 
                       case dbo.SourceType.CompanySourceTypeId
                       when 'PR' then SUM(dbo.ProductionEvent.Quantity)
                       end
                       AS Ttl_Qty 
FROM             dbo.Batch INNER JOIN
                       dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                       dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                       dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                       dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                       dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                       dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                       dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
                         dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE                  dbo.job.CompanyJobId = @JobNumber and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY               dbo.Item.CompanyItemId,  dbo.SourceType.CompanySourceTypeId, dbo.Item.Name
GO
[u]SP code that will call procedure on the db2/400 (should be part of "sp_Vecellio_ProductionQtyCompare" </u>

Code:
declare @JobNumber char(12)
Set @JobNumber ='     1100281'  
Exec ('Call QGPL.get_jobqty(?)', @JobNumber) AT AS400SRV_MSDASQL

[u]SP code on db2/AS400 (that will return cost code and qty)</u>

Code:
create procedure get_jobqty                                             
                (in @JobNumber varchar(12))                                  
                result set 1                                             
                language sql                                             
                reads sql data                                           
 begin                                                                 
                declare c1 scroll cursor with return for                       
                select gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +   
                gban04 + gban05 + gban06 + gban07 + gban08 +                   
                gban09 + gban10 + gban11 + gban12 + gban13 +                   
                gban14)/FLOAT(100.00),38,2) as sum_qty                         
                from vgiprddta/f0902lc where gbmcu = @JobNumber and  
                gblt = 'AU'                                   
                GROUP BY gbsub;                        
                open c1;                                      
                set result sets cursor c1;                    
  end;
 
Old November 11th, 2008, 09:06 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

The scope is explained wonderfully. But what's the QUESTION???

Only thing I noticed that you clearly aren't handling is this:

> There could be entries in either table that may not exists in the other.

That implies you need to do a FULL OUTER JOIN, if you weren't aware of it.
 
Old November 12th, 2008, 09:24 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Sorry I missed the most important thing which is my question.

I'm kinda lost putting code together to combine results from the 2 sp's into a single result set. Normally I am dealing with "straight" selects and joins and I am not sure how call 2 separate sp's that need to be joined. If somebody could give me a head start? Thank you.

PS The reason I am calling a sp on the db2 is that the table has more than 17 mill records and I am using a predefined access path = my selection (@JobNumber). Othervise, response time is very very bad. I tried using a straight "select" but had to leave that idea.
 
Old November 12th, 2008, 01:23 PM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

Well, I have managed to put together some code and my last issue is that I need to merge 2 tables. I need to insert records into table1 with records from table2 if they do not already exist (in table1)

Code:
insert into #table1 (costcode, jdesumqty) 
where not exists
(select jde_cost_code, jde_sum_qty
from #table2)
Error: Incorrect syntax near the keyword 'where'.

In table1 "costcode" is unique and in table2 "cost_code" is unique
 
Old November 13th, 2008, 09:19 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 205
Thanks: 4
Thanked 0 Times in 0 Posts
Default

I got some help and here is code that will do the job:


insert into #table1 (costcode, jdesumqty)
select distinct t2.jde_cost_code, t2.jde_sum_qty
from #table2 AS t2
where not exists (select * from #table1 AS t1 where t1.costcode = t2.jde_cost_code and t1.jdesumqty = t2.jde_sum_qty)


 
Old November 14th, 2008, 06:08 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114348






Similar Threads
Thread Thread Starter Forum Replies Last Post
VB.NET On Other Platforms neogortex Pro Visual Basic 2005 1 June 12th, 2006 01:48 PM
Running C# apps on platforms without .NET ? DanCase C# 2005 1 May 12th, 2006 12:12 AM
(Resolved) Select top10 problems jrogers SQL Language 1 April 12th, 2006 09:34 AM
New to Java Platforms vishvardh JSP Basics 1 August 9th, 2004 03:55 AM
Combine 2 classes blur.kid JSP Basics 1 September 12th, 2003 04:05 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.