Complicated Query (at least for me)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complicated Query (at least for me)

Author

Topic

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-11 : 10:15:33

I have two tables on my SQL Server and one reference table on a remote server (Oralce). The query returned 1500 rows but used 2000 rel_nar_id's from Table1.

Since there are over 6000 rel_nar_ids in Table1, why only 1500 rows were returned and 2000 rel_nar_ids used?

Does anyone see why more rel_nar_ids would be used than what was returned?

Does anyone see why this query would not return the whole 6000+?

Query...
truncate table false_churn.dbo.fc_info2
DECLARE @sql varchar(8000), @id varchar(8000)
WHILE EXISTS(SELECT rel_nar_id FROM false_churn.dbo.rel_nar_id WHERE Done=1)
BEGIN
SELECT @sql='', @id=''
SELECT TOP 500 @id=@id + cast(rel_nar_id as varchar) + ',' FROM false_churn.dbo.rel_nar_id WHERE Done=1
SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE rel_nar_id IN(' + @id + 'null)'
EXEC(@sql)
SET @sql='SELECT * FROM OPENQUERY(ndw, ''
SELECT nar_id, max(equipment_type), max(agent), max(price_plan), max(distinct data_date), max(ptn), max(ban), max(coalesce(LAST_ACT_DATE,SUB_BEG_DATE)), max(coalesce(ens_end_date,sub_end_date))
FROM bsonarview.nci_nar_units_hist_2004_btv
WHERE nar_id IN(' + @id + 'null)
group by nar_id
'')'
exec (@sql)
INSERT INTO false_churn.dbo.fc_info2
exec (@sql)
END

false_churn.dbo.rel_nar_id (Table1 Design on SQL Server)
rel_nar_id (numeric, 5, 1)
done (int, 4, 1)

false_churn.dbo.fc_info2 (Table2 Design on SQL Server)
rel_nar_id (numeric, 5, 1)
rel_equip (varchar, 24, 1)
rel_agent (varchar, 8, 1)
rel_pp (char, 9, 1)
rel_data_date (datetime, 8, 1)
rel_ptn (float, 8, 1)
rel_ban (numeric, 5, 1)
rel_act_date (datetime, 8, 1)
rel_end_date (datetime, 8, 1)

*************************
Got some code from Rob. Can anyone help?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-11 : 11:37:04

well i don't see why it would be like that...
could it be that oracle has some limit for how many values can there be in the IN ?? or the id's are doubling...

SET @sql='SELECT * FROM OPENQUERY(ndw, ''
SELECT nar_id, max(equipment_type), max(agent), max(price_plan), max(distinct data_date), max(ptn), max(ban), max(coalesce(LAST_ACT_DATE,SUB_BEG_DATE)), max(coalesce(ens_end_date,sub_end_date))
FROM bsonarview.nci_nar_units_hist_2004_btv
WHERE nar_id IN(' + @id + 'null)
group by nar_id
'')'
exec (@sql) -- you don't need this i believe
INSERT INTO false_churn.dbo.fc_info2
exec (@sql)

Go with the flow & have fun! Else fight the flow

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-11 : 11:44:58

When I checked the results (false_churn.dbo.fc_info2) table, there were no duplicate rel_nar_id's. As for the limit Oracle has, the query is limited to 500 rows at a time. I have pulled as many as 1000 at a time before. I guess I can set it to a lower number and rerun it.

I'll let you know how it goes.

*************************
Got some code from Rob. Can anyone help?

Ledell
SQL NASCAR Parsing Chick

107 Posts

Posted - 2004-10-11 : 12:17:09

I made the changes ... and got the following error message ...

(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0xfffffffd].

First...I don't understand why this will run for a bit and then quit. Any ideas?

Second...I was reading on the Microsoft site...

"A distributed transaction is a transaction that updates data on two or more networked computer systems. If the provider supports distributed transactions, it exposes ITransactionJoin on the session."

I do not want to update two different networked computer system's. Just one. Does this information change what the query should look like?

***********
Query Changed To...
truncate table false_churn.dbo.fc_info2
DECLARE @sql varchar(8000), @id varchar(8000)
WHILE EXISTS(SELECT rel_nar_id FROM false_churn.dbo.rel_nar_id WHERE Done=1)
BEGIN
SELECT @sql='', @id=''
SELECT TOP 100 @id=@id + cast(rel_nar_id as varchar) + ',' FROM false_churn.dbo.rel_nar_id WHERE Done=1
SET @sql='UPDATE false_churn.dbo.rel_nar_id SET Done=0 WHERE rel_nar_id IN(' + @id + 'null)'
EXEC(@sql)
SET @sql='SELECT * FROM OPENQUERY(ndw, ''
SELECT nar_id, max(equipment_type), max(agent), max(price_plan), max(distinct data_date), max(ptn), max(ban), max(coalesce(LAST_ACT_DATE,SUB_BEG_DATE)), max(coalesce(ens_end_date,sub_end_date))
FROM bsonarview.nci_nar_units_hist_2004_btv
WHERE nar_id IN(' + @id + 'null) group by nar_id'')'
INSERT INTO false_churn.dbo.fc_info2
exec (@sql)
END

*************************
Got some code from Rob. Can anyone help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources