2 select statments 1 table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

2 select statments 1 table

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-07 : 05:38:00

hello all,
i am trying to display on my web application the result of 2 deferent sql select statements in 1 table(grid).. the two select statments produce identical columns with the same column names...that is what i did exactly

(select col1,col2 from table1)full join (select col1,col2 from table2)

of course my 2 select statements are not as simple as shown they contain joins and collations and order by 's but here i showed this for simplification purposes ..when i ran this statement i got<br/> Incorrect syntax near the keyword 'full' thanks in advance for youre help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-07 : 05:39:54

use union all

(select col1,col2 from table1)union all (select col1,col2 from table2)

if col1,col2 are same datatype in both tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 09:58:28

beware that union all will return duplicates if there are same instances of records from both queries. if you're unsure of duplicates and want only distinct set to return use union rather than union all

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-07 : 10:07:01

thanks for your reply it worked (i think) but now i got the following error:
OLE DB provider "SQLNCLI" for linked server "ERP2" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16937, Level 16, State 1, Procedure _SKB_Sites_LstVw, Line 1
A server cursor is not allowed on a remote stored procedure or stored procedure with more than one SELECT statement. Use a default result set or client cursor.

does any one know what is the source for this error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:10:24

so one of the table is actually in linked server? can you post the exact query?

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-07 : 10:14:49

ok

(SELECT f1.[Client Name] AS Client_Name, updates_table.[Node Code] collate SQL_Latin1_General_CP1256_CI_AS AS Node_Code1 , Zone collate SQL_Latin1_General_CP1256_CI_AS ,f1.Support_Zone,District, f1.[Technology], f1.[Contact Person] AS Contact_Person, f1.[Contact Number] AS Contact_Number, f1.[Status] AS [Original Status], updates_table.status collate SQL_Latin1_General_CP1256_CI_AS as [updated_status], f1.[Required Date] as Required_Date,updates_table.notes from dbo.remote_list_view() f1 join updates_table on f1.[node code]=updates_table.[node code] collate SQL_Latin1_General_CP1256_CI_AS)
union all
(select [Client Name] AS Client_Name, [Node Code] AS Node_Code1, other_activities.Zone, Support_Zone,District,[Technology],[Contact Person] AS Contact_Person,[Contact Number] AS Contact_Number,[Status] AS [Original Status], status as [updated_status],[Required Date] as Required_Date,notes from dbo.remote_list_view() f1 right join other_activities on f1.[node code]=other_activities.[node_code1] collate SQL_Latin1_General_CP1256_CI_AS)

the function is bassically a select * from openquery reading from a stored proceedure on the remote data base and as you can see here there is a collation issue:S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:24:10

please use code tags in future while posting code part and indent it too like below. b/w i cant spot the OPENQUERY. are you sure this is your full query?


SELECT f1.[Client Name] AS Client_Name, 
updates_table.[Node Code] collate SQL_Latin1_General_CP1256_CI_AS AS Node_Code1 , 
Zone collate SQL_Latin1_General_CP1256_CI_AS ,
f1.Support_Zone,
District, 
f1.[Technology], 
f1.[Contact Person] AS Contact_Person, 
f1.[Contact Number] AS Contact_Number, 
f1.[Status] AS [Original Status], 
updates_table.status collate SQL_Latin1_General_CP1256_CI_AS as [updated_status], 
f1.[Required Date] as Required_Date,
updates_table.notes 
from dbo.remote_list_view() f1 
join updates_table 
on f1.[node code]=updates_table.[node code] collate SQL_Latin1_General_CP1256_CI_AS 
union all 
select [Client Name] AS Client_Name, 
[Node Code] AS Node_Code1, 
other_activities.Zone, 
Support_Zone,
District,
[Technology],
[Contact Person] AS Contact_Person,
[Contact Number] AS Contact_Number,
[Status] AS [Original Status], 
status as [updated_status],
[Required Date] as Required_Date,
notes 
from dbo.remote_list_view() f1 
right join other_activities on f1.[node code]=other_activities.[node_code1] collate SQL_Latin1_General_CP1256_CI_AS

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-07 : 10:43:17

ok thanks man..yes it is as i mentioned in my the preceding post the function
from dbo.remote_list_view() contains the following
<code>set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER FUNCTION [dbo].[remote_list_view]
(

)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT * FROM OPENQUERY(ERP2,'EXEC SKYBAND.dbo._SKB_Sites_LstVw')
)

</code>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:58:05

can you try putting the results of OPENQUERY on a temporary table and then join onto it rather than returning it from a function?

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-07 : 12:05:27

ok sorry for being late it worked..could it be a authority in the remote database issue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 12:55:15

i think it may be because you tried to wrap it in a function.