[Resolved] Combine select from different platforms

snufse · November 11th, 2008, 08:08 PM

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 Pedant · November 11th, 2008, 09:06 PM

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.

snufse · November 12th, 2008, 09:24 AM

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.

snufse · November 12th, 2008, 01:23 PM

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

snufse · November 13th, 2008, 09:19 AM

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)

Peso · November 14th, 2008, 06:08 AM

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