|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Retrieving data from multiple tablesof resolution; the other contains data that has been approved for further use. Both tables contain the same attributes. When pending data is approved, that tuple is moved to the approved table and removed from the pending table. If new data is to be added, the incomplete data is placed in the pending table. Note that restrictions that apply to the approved table do not apply to the pending table. Duplicates are not allowed in either table nor between tables. I need to write a query that extracts all tuples from both tables that meet some criteria. I'm not interested in building two datasets and then programmatically combining them. I'd like to do the task in one SQL statement if possible. I do care from which table the data is retrieved. Thoughts? TIA -- Gus Gustafson Yeah could be, if you could procide any DDL, t hard to code some SQL on just
a common description of your tasks, post it and we will help you solving this proble,. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Show quote "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... > In the database are two tables. One contains data that is pending some > sort > of resolution; the other contains data that has been approved for further > use. Both tables contain the same attributes. When pending data is > approved, > that tuple is moved to the approved table and removed from the pending > table. > If new data is to be added, the incomplete data is placed in the pending > table. Note that restrictions that apply to the approved table do not > apply > to the pending table. Duplicates are not allowed in either table nor > between > tables. > > I need to write a query that extracts all tuples from both tables that > meet > some criteria. I'm not interested in building two datasets and then > programmatically combining them. I'd like to do the task in one SQL > statement > if possible. I do care from which table the data is retrieved. > > Thoughts? > > TIA > > -- > Gus Gustafson The creation SQL looks like
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Pending_Solutions] GO CREATE TABLE [dbo].[Pending_Solutions] ( [solution_ID] [int] IDENTITY (1, 1) NOT NULL , [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [submitted_by_ID] [int] NULL , [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FA_key_ID] [int] NULL , [initiative_code] [bit] NOT NULL , [pending_code] [bit] NOT NULL , [delete_code] [bit] NOT NULL , [complementary_system_code] [bit] NOT NULL , [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [functional_area_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [POC_DSN_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [POC_commercial_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [approval_level_code] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [initiative_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [approved_by_ID] [int] NULL , [validated_by_ID] [int] NULL , [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [d_code] [bit] NOT NULL , [o_code] [bit] NOT NULL , [t_code] [bit] NOT NULL , [m_code] [bit] NOT NULL , [l_code] [bit] NOT NULL , [p_code] [bit] NOT NULL , [f_code] [bit] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO This CREATE statement is for the Pending_Solutions table. An equivalent CREATE is used for the Solutions table except that restrictions for foreign keys and non NULL fields are applied.. The search criteria desired is something like SELECT solution_ID, solution_name, long_name_text AS description, pending_code AS pending FROM ??? WHERE domain_code LIKE @domain_code AND proponent_code LIKE @proponent_code AND functional_area_code LIKE @functional_area_code Gus Show quote "Jens Süßmeyer" wrote: > Yeah could be, if you could procide any DDL, t hard to code some SQL on just > a common description of your tasks, post it and we will help you solving > this proble,. > > HTH, Jens Suessmeyer. > > --- > http://www.sqlserver2005.de > --- > > > "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... > > In the database are two tables. One contains data that is pending some > > sort > > of resolution; the other contains data that has been approved for further > > use. Both tables contain the same attributes. When pending data is > > approved, > > that tuple is moved to the approved table and removed from the pending > > table. > > If new data is to be added, the incomplete data is placed in the pending > > table. Note that restrictions that apply to the approved table do not > > apply > > to the pending table. Duplicates are not allowed in either table nor > > between > > tables. > > > > I need to write a query that extracts all tuples from both tables that > > meet > > some criteria. I'm not interested in building two datasets and then > > programmatically combining them. I'd like to do the task in one SQL > > statement > > if possible. I do care from which table the data is retrieved. > > > > Thoughts? > > > > TIA > > > > -- > > Gus Gustafson > > > Ok, i understand what you mean, you whould to the following:
> SELECT solution_ID, Select * from Pending_Solutions> solution_name, > long_name_text AS description, > pending_code AS pending > FROM ( UNION Select * from Solutions ) Subquery > WHERE domain_code LIKE @domain_code AND but you should consider that the restrictions could be applied in the > proponent_code LIKE @proponent_code AND > functional_area_code LIKE @functional_area_code Subquery for every single query to optimize th query plan, just try that one above compared with the follwing code which evtl. could be better for executing: > SELECT solution_ID, Select * from Pending_Solutions> solution_name, > long_name_text AS description, > pending_code AS pending > FROM ( > WHERE domain_code LIKE @domain_code AND Select * from Solutions> proponent_code LIKE @proponent_code AND > functional_area_code LIKE @functional_area_code UNION > WHERE domain_code LIKE @domain_code AND HTH, Jens Suessmeyer.> proponent_code LIKE @proponent_code AND > functional_area_code LIKE @functional_area_code ) Subquery --- http://www.sqlserver2005.de --- Show quote "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im Newsbeitrag news:159DCBD1-79E7-4986-9798-022D457B7285@microsoft.com... > The creation SQL looks like > > if exists (select * from dbo.sysobjects where id = > object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > drop table [dbo].[Pending_Solutions] > GO > > CREATE TABLE [dbo].[Pending_Solutions] ( > [solution_ID] [int] IDENTITY (1, 1) NOT NULL , > [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [submitted_by_ID] [int] NULL , > [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [FA_key_ID] [int] NULL , > [initiative_code] [bit] NOT NULL , > [pending_code] [bit] NOT NULL , > [delete_code] [bit] NOT NULL , > [complementary_system_code] [bit] NOT NULL , > [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [functional_area_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [POC_DSN_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [POC_commercial_phone_number] [varchar] (32) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL , > [approval_level_code] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [initiative_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , > [approved_by_ID] [int] NULL , > [validated_by_ID] [int] NULL , > [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > , > [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [d_code] [bit] NOT NULL , > [o_code] [bit] NOT NULL , > [t_code] [bit] NOT NULL , > [m_code] [bit] NOT NULL , > [l_code] [bit] NOT NULL , > [p_code] [bit] NOT NULL , > [f_code] [bit] NOT NULL > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > > This CREATE statement is for the Pending_Solutions table. An equivalent > CREATE is used for the Solutions table except that restrictions for > foreign > keys and non NULL fields are applied.. > > The search criteria desired is something like > > SELECT solution_ID, > solution_name, > long_name_text AS description, > pending_code AS pending > FROM ??? > WHERE domain_code LIKE @domain_code AND > proponent_code LIKE @proponent_code AND > functional_area_code LIKE @functional_area_code > > Gus > > "Jens Süßmeyer" wrote: > >> Yeah could be, if you could procide any DDL, t hard to code some SQL on >> just >> a common description of your tasks, post it and we will help you solving >> this proble,. >> >> HTH, Jens Suessmeyer. >> >> --- >> http://www.sqlserver2005.de >> --- >> >> >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im >> Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... >> > In the database are two tables. One contains data that is pending some >> > sort >> > of resolution; the other contains data that has been approved for >> > further >> > use. Both tables contain the same attributes. When pending data is >> > approved, >> > that tuple is moved to the approved table and removed from the pending >> > table. >> > If new data is to be added, the incomplete data is placed in the >> > pending >> > table. Note that restrictions that apply to the approved table do not >> > apply >> > to the pending table. Duplicates are not allowed in either table nor >> > between >> > tables. >> > >> > I need to write a query that extracts all tuples from both tables that >> > meet >> > some criteria. I'm not interested in building two datasets and then >> > programmatically combining them. I'd like to do the task in one SQL >> > statement >> > if possible. I do care from which table the data is retrieved. >> > >> > Thoughts? >> > >> > TIA >> > >> > -- >> > Gus Gustafson >> >> >> To test your SQL (not that I had any doubt that it would work :-) I created
the following derivative SQL: ALTER PROCEDURE CNA.sp_GetSolutionIDFromName2 ( @solution_name varchar(64) ) AS SELECT solution_ID FROM ( SELECT * FROM Pending_Solutions WHERE solution_name LIKE @solution_name UNION SELECT * FROM Solutions WHERE solution_name LIKE @solution_name ) SUBQUERY RETURN When I try to save the new SP (in VS.NET), I get the following message ADO error: The text, ntext, or image data type cannot be selected as DISTINCT Clues? TIA, Gus Show quote "Jens Süßmeyer" wrote: > Ok, i understand what you mean, you whould to the following: > > > SELECT solution_ID, > > solution_name, > > long_name_text AS description, > > pending_code AS pending > > FROM > ( > Select * from Pending_Solutions > UNION > Select * from Solutions > ) Subquery > > WHERE domain_code LIKE @domain_code AND > > proponent_code LIKE @proponent_code AND > > functional_area_code LIKE @functional_area_code > > but you should consider that the restrictions could be applied in the > Subquery for every single query to optimize th query plan, just try that one > above compared with the follwing code which evtl. could be better for > executing: > > > > SELECT solution_ID, > > solution_name, > > long_name_text AS description, > > pending_code AS pending > > FROM > ( > Select * from Pending_Solutions > > > WHERE domain_code LIKE @domain_code AND > > proponent_code LIKE @proponent_code AND > > functional_area_code LIKE @functional_area_code > UNION > Select * from Solutions > > > WHERE domain_code LIKE @domain_code AND > > proponent_code LIKE @proponent_code AND > > functional_area_code LIKE @functional_area_code > ) Subquery > > > HTH, Jens Suessmeyer. > > --- > http://www.sqlserver2005.de > --- > > > "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > Newsbeitrag news:159DCBD1-79E7-4986-9798-022D457B7285@microsoft.com... > > The creation SQL looks like > > > > if exists (select * from dbo.sysobjects where id = > > object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id, > > N'IsUserTable') = 1) > > drop table [dbo].[Pending_Solutions] > > GO > > > > CREATE TABLE [dbo].[Pending_Solutions] ( > > [solution_ID] [int] IDENTITY (1, 1) NOT NULL , > > [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [submitted_by_ID] [int] NULL , > > [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > > , > > [FA_key_ID] [int] NULL , > > [initiative_code] [bit] NOT NULL , > > [pending_code] [bit] NOT NULL , > > [delete_code] [bit] NOT NULL , > > [complementary_system_code] [bit] NOT NULL , > > [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [functional_area_code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [POC_DSN_phone_number] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [POC_commercial_phone_number] [varchar] (32) COLLATE > > SQL_Latin1_General_CP1_CI_AS NULL , > > [approval_level_code] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [initiative_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS > > NULL , > > [approved_by_ID] [int] NULL , > > [validated_by_ID] [int] NULL , > > [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > > , > > [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > > [d_code] [bit] NOT NULL , > > [o_code] [bit] NOT NULL , > > [t_code] [bit] NOT NULL , > > [m_code] [bit] NOT NULL , > > [l_code] [bit] NOT NULL , > > [p_code] [bit] NOT NULL , > > [f_code] [bit] NOT NULL > > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > > GO > > > > This CREATE statement is for the Pending_Solutions table. An equivalent > > CREATE is used for the Solutions table except that restrictions for > > foreign > > keys and non NULL fields are applied.. > > > > The search criteria desired is something like > > > > SELECT solution_ID, > > solution_name, > > long_name_text AS description, > > pending_code AS pending > > FROM ??? > > WHERE domain_code LIKE @domain_code AND > > proponent_code LIKE @proponent_code AND > > functional_area_code LIKE @functional_area_code > > > > Gus > > > > "Jens Süßmeyer" wrote: > > > >> Yeah could be, if you could procide any DDL, t hard to code some SQL on > >> just > >> a common description of your tasks, post it and we will help you solving > >> this proble,. > >> > >> HTH, Jens Suessmeyer. > >> > >> --- > >> http://www.sqlserver2005.de > >> --- > >> > >> > >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > >> Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... > >> > In the database are two tables. One contains data that is pending some > >> > sort > >> > of resolution; the other contains data that has been approved for > >> > further > >> > use. Both tables contain the same attributes. When pending data is > >> > approved, > >> > that tuple is moved to the approved table and removed from the pending > >> > table. > >> > If new data is to be added, the incomplete data is placed in the > >> > pending > >> > table. Note that restrictions that apply to the approved table do not > >> > apply > >> > to the pending table. Duplicates are not allowed in either table nor > >> > between > >> > tables. > >> > > >> > I need to write a query that extracts all tuples from both tables that > >> > meet > >> > some criteria. I'm not interested in building two datasets and then > >> > programmatically combining them. I'd like to do the task in one SQL > >> > statement > >> > if possible. I do care from which table the data is retrieved. > >> > > >> > Thoughts? > >> > > >> > TIA > >> > > >> > -- > >> > Gus Gustafson > >> > >> > >> > > > Yes of course that rings a bell for me :-).
UNION does a distinct grouping on the presented / affected rows, you should either name the columns you want to select and make sure that you don´t Select a text, ntext, or image column in there because distinct can´t be applied to those datatypes. Or you could Add the surfix ALL to the query to make sure all results will be preseneted there. SELECT * > FROM Pending_Solutions But it is more preferable to name to the columns you want to select rather > WHERE solution_name LIKE @solution_name > UNION ALL > SELECT * > FROM Solutions > WHERE solution_name LIKE @solution_name than working with the "*". HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Show quote "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im Newsbeitrag news:AED2D9F6-30FF-4E7A-8055-17322D3CD815@microsoft.com... > To test your SQL (not that I had any doubt that it would work :-) I > created > the following derivative SQL: > > ALTER PROCEDURE CNA.sp_GetSolutionIDFromName2 > ( > @solution_name varchar(64) > ) > AS > SELECT solution_ID > FROM > ( > SELECT * > FROM Pending_Solutions > WHERE solution_name LIKE @solution_name > UNION > SELECT * > FROM Solutions > WHERE solution_name LIKE @solution_name > ) SUBQUERY > RETURN > > When I try to save the new SP (in VS.NET), I get the following message > > ADO error: The text, ntext, or image data type cannot be selected as > DISTINCT > > Clues? > > TIA, Gus > > "Jens Süßmeyer" wrote: > >> Ok, i understand what you mean, you whould to the following: >> >> > SELECT solution_ID, >> > solution_name, >> > long_name_text AS description, >> > pending_code AS pending >> > FROM >> ( >> Select * from Pending_Solutions >> UNION >> Select * from Solutions >> ) Subquery >> > WHERE domain_code LIKE @domain_code AND >> > proponent_code LIKE @proponent_code AND >> > functional_area_code LIKE @functional_area_code >> >> but you should consider that the restrictions could be applied in the >> Subquery for every single query to optimize th query plan, just try that >> one >> above compared with the follwing code which evtl. could be better for >> executing: >> >> >> > SELECT solution_ID, >> > solution_name, >> > long_name_text AS description, >> > pending_code AS pending >> > FROM >> ( >> Select * from Pending_Solutions >> >> > WHERE domain_code LIKE @domain_code AND >> > proponent_code LIKE @proponent_code AND >> > functional_area_code LIKE @functional_area_code >> UNION >> Select * from Solutions >> >> > WHERE domain_code LIKE @domain_code AND >> > proponent_code LIKE @proponent_code AND >> > functional_area_code LIKE @functional_area_code >> ) Subquery >> >> >> HTH, Jens Suessmeyer. >> >> --- >> http://www.sqlserver2005.de >> --- >> >> >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im >> Newsbeitrag news:159DCBD1-79E7-4986-9798-022D457B7285@microsoft.com... >> > The creation SQL looks like >> > >> > if exists (select * from dbo.sysobjects where id = >> > object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id, >> > N'IsUserTable') = 1) >> > drop table [dbo].[Pending_Solutions] >> > GO >> > >> > CREATE TABLE [dbo].[Pending_Solutions] ( >> > [solution_ID] [int] IDENTITY (1, 1) NOT NULL , >> > [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [submitted_by_ID] [int] NULL , >> > [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> > [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL >> > , >> > [FA_key_ID] [int] NULL , >> > [initiative_code] [bit] NOT NULL , >> > [pending_code] [bit] NOT NULL , >> > [delete_code] [bit] NOT NULL , >> > [complementary_system_code] [bit] NOT NULL , >> > [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> > [functional_area_code] [varchar] (8) COLLATE >> > SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> > [POC_DSN_phone_number] [varchar] (32) COLLATE >> > SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [POC_commercial_phone_number] [varchar] (32) COLLATE >> > SQL_Latin1_General_CP1_CI_AS NULL , >> > [approval_level_code] [varchar] (32) COLLATE >> > SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [initiative_type_name] [varchar] (16) COLLATE >> > SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL , >> > [approved_by_ID] [int] NULL , >> > [validated_by_ID] [int] NULL , >> > [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> > [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS >> > NULL >> > , >> > [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL >> > , >> > [d_code] [bit] NOT NULL , >> > [o_code] [bit] NOT NULL , >> > [t_code] [bit] NOT NULL , >> > [m_code] [bit] NOT NULL , >> > [l_code] [bit] NOT NULL , >> > [p_code] [bit] NOT NULL , >> > [f_code] [bit] NOT NULL >> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] >> > GO >> > >> > This CREATE statement is for the Pending_Solutions table. An equivalent >> > CREATE is used for the Solutions table except that restrictions for >> > foreign >> > keys and non NULL fields are applied.. >> > >> > The search criteria desired is something like >> > >> > SELECT solution_ID, >> > solution_name, >> > long_name_text AS description, >> > pending_code AS pending >> > FROM ??? >> > WHERE domain_code LIKE @domain_code AND >> > proponent_code LIKE @proponent_code AND >> > functional_area_code LIKE @functional_area_code >> > >> > Gus >> > >> > "Jens Süßmeyer" wrote: >> > >> >> Yeah could be, if you could procide any DDL, t hard to code some SQL >> >> on >> >> just >> >> a common description of your tasks, post it and we will help you >> >> solving >> >> this proble,. >> >> >> >> HTH, Jens Suessmeyer. >> >> >> >> --- >> >> http://www.sqlserver2005.de >> >> --- >> >> >> >> >> >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im >> >> Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... >> >> > In the database are two tables. One contains data that is pending >> >> > some >> >> > sort >> >> > of resolution; the other contains data that has been approved for >> >> > further >> >> > use. Both tables contain the same attributes. When pending data is >> >> > approved, >> >> > that tuple is moved to the approved table and removed from the >> >> > pending >> >> > table. >> >> > If new data is to be added, the incomplete data is placed in the >> >> > pending >> >> > table. Note that restrictions that apply to the approved table do >> >> > not >> >> > apply >> >> > to the pending table. Duplicates are not allowed in either table nor >> >> > between >> >> > tables. >> >> > >> >> > I need to write a query that extracts all tuples from both tables >> >> > that >> >> > meet >> >> > some criteria. I'm not interested in building two datasets and then >> >> > programmatically combining them. I'd like to do the task in one SQL >> >> > statement >> >> > if possible. I do care from which table the data is retrieved. >> >> > >> >> > Thoughts? >> >> > >> >> > TIA >> >> > >> >> > -- >> >> > Gus Gustafson >> >> >> >> >> >> >> >> >> I followed your suggestion to name the specific columns I wanted. Worked like
a charm. Thank you for your help and patience. Regards, Gus Show quote "Jens Süßmeyer" wrote: > Yes of course that rings a bell for me :-). > > UNION does a distinct grouping on the presented / affected rows, you should > either name the columns you want to select and make sure that you don´t > Select a text, ntext, or image column in there because distinct can´t be > applied to those datatypes. Or you could Add the surfix ALL to the query to > make sure all results will be preseneted there. > > SELECT * > > FROM Pending_Solutions > > WHERE solution_name LIKE @solution_name > > UNION ALL > > SELECT * > > FROM Solutions > > WHERE solution_name LIKE @solution_name > > But it is more preferable to name to the columns you want to select rather > than working with the "*". > > HTH, Jens Suessmeyer. > > --- > http://www.sqlserver2005.de > --- > > > "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > Newsbeitrag news:AED2D9F6-30FF-4E7A-8055-17322D3CD815@microsoft.com... > > To test your SQL (not that I had any doubt that it would work :-) I > > created > > the following derivative SQL: > > > > ALTER PROCEDURE CNA.sp_GetSolutionIDFromName2 > > ( > > @solution_name varchar(64) > > ) > > AS > > SELECT solution_ID > > FROM > > ( > > SELECT * > > FROM Pending_Solutions > > WHERE solution_name LIKE @solution_name > > UNION > > SELECT * > > FROM Solutions > > WHERE solution_name LIKE @solution_name > > ) SUBQUERY > > RETURN > > > > When I try to save the new SP (in VS.NET), I get the following message > > > > ADO error: The text, ntext, or image data type cannot be selected as > > DISTINCT > > > > Clues? > > > > TIA, Gus > > > > "Jens Süßmeyer" wrote: > > > >> Ok, i understand what you mean, you whould to the following: > >> > >> > SELECT solution_ID, > >> > solution_name, > >> > long_name_text AS description, > >> > pending_code AS pending > >> > FROM > >> ( > >> Select * from Pending_Solutions > >> UNION > >> Select * from Solutions > >> ) Subquery > >> > WHERE domain_code LIKE @domain_code AND > >> > proponent_code LIKE @proponent_code AND > >> > functional_area_code LIKE @functional_area_code > >> > >> but you should consider that the restrictions could be applied in the > >> Subquery for every single query to optimize th query plan, just try that > >> one > >> above compared with the follwing code which evtl. could be better for > >> executing: > >> > >> > >> > SELECT solution_ID, > >> > solution_name, > >> > long_name_text AS description, > >> > pending_code AS pending > >> > FROM > >> ( > >> Select * from Pending_Solutions > >> > >> > WHERE domain_code LIKE @domain_code AND > >> > proponent_code LIKE @proponent_code AND > >> > functional_area_code LIKE @functional_area_code > >> UNION > >> Select * from Solutions > >> > >> > WHERE domain_code LIKE @domain_code AND > >> > proponent_code LIKE @proponent_code AND > >> > functional_area_code LIKE @functional_area_code > >> ) Subquery > >> > >> > >> HTH, Jens Suessmeyer. > >> > >> --- > >> http://www.sqlserver2005.de > >> --- > >> > >> > >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > >> Newsbeitrag news:159DCBD1-79E7-4986-9798-022D457B7285@microsoft.com... > >> > The creation SQL looks like > >> > > >> > if exists (select * from dbo.sysobjects where id = > >> > object_id(N'[dbo].[Pending_Solutions]') and OBJECTPROPERTY(id, > >> > N'IsUserTable') = 1) > >> > drop table [dbo].[Pending_Solutions] > >> > GO > >> > > >> > CREATE TABLE [dbo].[Pending_Solutions] ( > >> > [solution_ID] [int] IDENTITY (1, 1) NOT NULL , > >> > [solution_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [submitted_by_ID] [int] NULL , > >> > [domain_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > >> > [proponent_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL > >> > , > >> > [FA_key_ID] [int] NULL , > >> > [initiative_code] [bit] NOT NULL , > >> > [pending_code] [bit] NOT NULL , > >> > [delete_code] [bit] NOT NULL , > >> > [complementary_system_code] [bit] NOT NULL , > >> > [FUE_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > >> > [functional_area_code] [varchar] (8) COLLATE > >> > SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [POC_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > >> > [POC_DSN_phone_number] [varchar] (32) COLLATE > >> > SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [POC_commercial_phone_number] [varchar] (32) COLLATE > >> > SQL_Latin1_General_CP1_CI_AS NULL , > >> > [approval_level_code] [varchar] (32) COLLATE > >> > SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [approval_date] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [program_type_name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [initiative_type_name] [varchar] (16) COLLATE > >> > SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [replaces_name] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [other_type_text] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [image_file_name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL , > >> > [approved_by_ID] [int] NULL , > >> > [validated_by_ID] [int] NULL , > >> > [solution_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > >> > [long_name_text] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS > >> > NULL > >> > , > >> > [solution_issues_text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL > >> > , > >> > [d_code] [bit] NOT NULL , > >> > [o_code] [bit] NOT NULL , > >> > [t_code] [bit] NOT NULL , > >> > [m_code] [bit] NOT NULL , > >> > [l_code] [bit] NOT NULL , > >> > [p_code] [bit] NOT NULL , > >> > [f_code] [bit] NOT NULL > >> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > >> > GO > >> > > >> > This CREATE statement is for the Pending_Solutions table. An equivalent > >> > CREATE is used for the Solutions table except that restrictions for > >> > foreign > >> > keys and non NULL fields are applied.. > >> > > >> > The search criteria desired is something like > >> > > >> > SELECT solution_ID, > >> > solution_name, > >> > long_name_text AS description, > >> > pending_code AS pending > >> > FROM ??? > >> > WHERE domain_code LIKE @domain_code AND > >> > proponent_code LIKE @proponent_code AND > >> > functional_area_code LIKE @functional_area_code > >> > > >> > Gus > >> > > >> > "Jens Süßmeyer" wrote: > >> > > >> >> Yeah could be, if you could procide any DDL, t hard to code some SQL > >> >> on > >> >> just > >> >> a common description of your tasks, post it and we will help you > >> >> solving > >> >> this proble,. > >> >> > >> >> HTH, Jens Suessmeyer. > >> >> > >> >> --- > >> >> http://www.sqlserver2005.de > >> >> --- > >> >> > >> >> > >> >> "Gus Gustafson" <GusGustaf***@discussions.microsoft.com> schrieb im > >> >> Newsbeitrag news:F0E1C952-44B3-4DF7-9A79-A7EA99525C16@microsoft.com... > >> >> > In the database are two tables. One contains data that is pending > >> >> > some > >> >> > sort > >> >> > of resolution; the other contains data that has been approved for > >> >> > further > >> >> > use. Both tables contain the same attributes. When pending data is > >> >> > approved, > >> >> > that tuple is moved to the approved table and removed from the > >> >> > pending > >> >> > table. > >> >> > If new data is to be added, the incomplete data is placed in the > >> >> > pending > >> >> > table. Note that restrictions that apply to the approved table do > >> >> > not > >> >> > apply > >> >> > to the pending table. Duplicates are not allowed in either table nor > >> >> > between > >> >> > tables. > >> >> > > >> >> > I need to write a query that extracts all tuples from both tables > >> >> > that > >> >> > meet > >> >> > some criteria. I'm not interested in building two datasets and then > >> >> > programmatically combining them. I'd like to do the task in one SQL > >> >> > statement > >> >> > if possible. I do care from which table the data is retrieved. > >> >> > > >> >> > Thoughts? > >> >> > > >> >> > TIA > >> >> > > >> >> > -- > >> >> > Gus Gustafson > >> >> > >> >> > >> >> > >> > >> > >> > > > You could create a view that cobines the two tables... Adding an expression
based attribute (column) in the process that would identify which table each row came from Create View TableView As Select 'Pend' As Source, * From PendingTable Union Select 'Aprd' As Source, * From ApprovedTable Show quote "Gus Gustafson" wrote: > In the database are two tables. One contains data that is pending some sort > of resolution; the other contains data that has been approved for further > use. Both tables contain the same attributes. When pending data is approved, > that tuple is moved to the approved table and removed from the pending table. > If new data is to be added, the incomplete data is placed in the pending > table. Note that restrictions that apply to the approved table do not apply > to the pending table. Duplicates are not allowed in either table nor between > tables. > > I need to write a query that extracts all tuples from both tables that meet > some criteria. I'm not interested in building two datasets and then > programmatically combining them. I'd like to do the task in one SQL statement > if possible. I do care from which table the data is retrieved. > > Thoughts? > > TIA > > -- > Gus Gustafson OK, but there is a small difficulty: the customer will not allow me to create
tables or views. So everything that I do must be done through SQL (usually stored procedures). I will pass on the view to the customer for implementation. In the meantime, is there another way that fits within these restrictions? Gus Show quote "CBretana" wrote: > You could create a view that cobines the two tables... Adding an expression > based attribute (column) in the process that would identify which table each > row came from > > Create View TableView > As > Select 'Pend' As Source, * > From PendingTable > Union > Select 'Aprd' As Source, * > From ApprovedTable > > > "Gus Gustafson" wrote: > > > In the database are two tables. One contains data that is pending some sort > > of resolution; the other contains data that has been approved for further > > use. Both tables contain the same attributes. When pending data is approved, > > that tuple is moved to the approved table and removed from the pending table. > > If new data is to be added, the incomplete data is placed in the pending > > table. Note that restrictions that apply to the approved table do not apply > > to the pending table. Duplicates are not allowed in either table nor between > > tables. > > > > I need to write a query that extracts all tuples from both tables that meet > > some criteria. I'm not interested in building two datasets and then > > programmatically combining them. I'd like to do the task in one SQL statement > > if possible. I do care from which table the data is retrieved. > > > > Thoughts? > > > > TIA > > > > -- > > Gus Gustafson Yes do the same thing in a stored Procedure, but you will have to code it to
handle whatever search criteria functionality the end users wish to have available, using Srored Proc parameters with null deault values to control the filtering... Create Procedure Get @Source = null,, -- pass as 'P' 'A' or null to get both @ColA Integer = Null, etc.... @ As Select 'Pend' As Source, * From PendingTable Where IsNull(@Source, 'P') = 'P' And ColA = IsNull(@ColA, ColA) And ... etc. Union Select 'Aprd' As Source, * From ApprovedTable Where IsNull(@Source, 'A') = 'A' And ColA = IsNull(@ColA, ColA) And ... etc. Show quote "Gus Gustafson" wrote: > OK, but there is a small difficulty: the customer will not allow me to create > tables or views. So everything that I do must be done through SQL (usually > stored procedures). I will pass on the view to the customer for > implementation. In the meantime, is there another way that fits within these > restrictions? > > Gus > > "CBretana" wrote: > > > You could create a view that cobines the two tables... Adding an expression > > based attribute (column) in the process that would identify which table each > > row came from > > > > Create View TableView > > As > > Select 'Pend' As Source, * > > From PendingTable > > Union > > Select 'Aprd' As Source, * > > From ApprovedTable > > > > > > "Gus Gustafson" wrote: > > > > > In the database are two tables. One contains data that is pending some sort > > > of resolution; the other contains data that has been approved for further > > > use. Both tables contain the same attributes. When pending data is approved, > > > that tuple is moved to the approved table and removed from the pending table. > > > If new data is to be added, the incomplete data is placed in the pending > > > table. Note that restrictions that apply to the approved table do not apply > > > to the pending table. Duplicates are not allowed in either table nor between > > > tables. > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > some criteria. I'm not interested in building two datasets and then > > > programmatically combining them. I'd like to do the task in one SQL statement > > > if possible. I do care from which table the data is retrieved. > > > > > > Thoughts? > > > > > > TIA > > > > > > -- > > > Gus Gustafson Sorry, didn;t edit that last right...
Create Procedure GetObjectData @Source = null, -- pass as 'P' 'A' or null to get both @ColA Integer = Null, etc.... As Select 'Pend' As Source, * From PendingTable Where IsNull(@Source, 'P') = 'P' And ColA = IsNull(@ColA, ColA) And ... etc. Union Select 'Aprd' As Source, * From ApprovedTable Where IsNull(@Source, 'A') = 'A' And ColA = IsNull(@ColA, ColA) And ... etc. Show quote "CBretana" wrote: > Yes do the same thing in a stored Procedure, but you will have to code it to > handle whatever search criteria functionality the end users wish to have > available, using Srored Proc parameters with null deault values to control > the filtering... > > Create Procedure Get > @Source = null,, -- pass as 'P' 'A' or null to get both > @ColA Integer = Null, > etc.... > @ > As > Select 'Pend' As Source, * > From PendingTable > Where IsNull(@Source, 'P') = 'P' > And ColA = IsNull(@ColA, ColA) > And ... etc. > Union > Select 'Aprd' As Source, * > From ApprovedTable > Where IsNull(@Source, 'A') = 'A' > And ColA = IsNull(@ColA, ColA) > And ... etc. > > "Gus Gustafson" wrote: > > > OK, but there is a small difficulty: the customer will not allow me to create > > tables or views. So everything that I do must be done through SQL (usually > > stored procedures). I will pass on the view to the customer for > > implementation. In the meantime, is there another way that fits within these > > restrictions? > > > > Gus > > > > "CBretana" wrote: > > > > > You could create a view that cobines the two tables... Adding an expression > > > based attribute (column) in the process that would identify which table each > > > row came from > > > > > > Create View TableView > > > As > > > Select 'Pend' As Source, * > > > From PendingTable > > > Union > > > Select 'Aprd' As Source, * > > > From ApprovedTable > > > > > > > > > "Gus Gustafson" wrote: > > > > > > > In the database are two tables. One contains data that is pending some sort > > > > of resolution; the other contains data that has been approved for further > > > > use. Both tables contain the same attributes. When pending data is approved, > > > > that tuple is moved to the approved table and removed from the pending table. > > > > If new data is to be added, the incomplete data is placed in the pending > > > > table. Note that restrictions that apply to the approved table do not apply > > > > to the pending table. Duplicates are not allowed in either table nor between > > > > tables. > > > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > > some criteria. I'm not interested in building two datasets and then > > > > programmatically combining them. I'd like to do the task in one SQL statement > > > > if possible. I do care from which table the data is retrieved. > > > > > > > > Thoughts? > > > > > > > > TIA > > > > > > > > -- > > > > Gus Gustafson Create Procedure GetSolutionData
@Source = null, -- pass as 'P' 'A' or null to get both @ColA Integer = Null, etc.... As Select 'Pend' As Source, * From PendingTable Where IsNull(@Source, 'P') = 'P' And ColA = IsNull(@ColA, ColA) And ... etc. Union Select 'Aprd' As Source, * From ApprovedTable Where IsNull(@Source, 'A') = 'A' And (@domain_code Is Null or domain_code LIKE @domain_code) And (@proponent_code Is Null Or proponent_code LIKE @proponent_code) And (@functional_area_code Is Null Or functional_area_code LIKE @functional_area_code Show quote "CBretana" wrote: > Sorry, didn;t edit that last right... > > > Create Procedure GetObjectData > @Source = null, -- pass as 'P' 'A' or null to get both > @ColA Integer = Null, > etc.... > As > Select 'Pend' As Source, * > From PendingTable > Where IsNull(@Source, 'P') = 'P' > And ColA = IsNull(@ColA, ColA) > And ... etc. > Union > Select 'Aprd' As Source, * > From ApprovedTable > Where IsNull(@Source, 'A') = 'A' > And ColA = IsNull(@ColA, ColA) > And ... etc. > > > "CBretana" wrote: > > > Yes do the same thing in a stored Procedure, but you will have to code it to > > handle whatever search criteria functionality the end users wish to have > > available, using Srored Proc parameters with null deault values to control > > the filtering... > > > > Create Procedure Get > > @Source = null,, -- pass as 'P' 'A' or null to get both > > @ColA Integer = Null, > > etc.... > > @ > > As > > Select 'Pend' As Source, * > > From PendingTable > > Where IsNull(@Source, 'P') = 'P' > > And ColA = IsNull(@ColA, ColA) > > And ... etc. > > Union > > Select 'Aprd' As Source, * > > From ApprovedTable > > Where IsNull(@Source, 'A') = 'A' > > And ColA = IsNull(@ColA, ColA) > > And ... etc. > > > > "Gus Gustafson" wrote: > > > > > OK, but there is a small difficulty: the customer will not allow me to create > > > tables or views. So everything that I do must be done through SQL (usually > > > stored procedures). I will pass on the view to the customer for > > > implementation. In the meantime, is there another way that fits within these > > > restrictions? > > > > > > Gus > > > > > > "CBretana" wrote: > > > > > > > You could create a view that cobines the two tables... Adding an expression > > > > based attribute (column) in the process that would identify which table each > > > > row came from > > > > > > > > Create View TableView > > > > As > > > > Select 'Pend' As Source, * > > > > From PendingTable > > > > Union > > > > Select 'Aprd' As Source, * > > > > From ApprovedTable > > > > > > > > > > > > "Gus Gustafson" wrote: > > > > > > > > > In the database are two tables. One contains data that is pending some sort > > > > > of resolution; the other contains data that has been approved for further > > > > > use. Both tables contain the same attributes. When pending data is approved, > > > > > that tuple is moved to the approved table and removed from the pending table. > > > > > If new data is to be added, the incomplete data is placed in the pending > > > > > table. Note that restrictions that apply to the approved table do not apply > > > > > to the pending table. Duplicates are not allowed in either table nor between > > > > > tables. > > > > > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > > > some criteria. I'm not interested in building two datasets and then > > > > > programmatically combining them. I'd like to do the task in one SQL statement > > > > > if possible. I do care from which table the data is retrieved. > > > > > > > > > > Thoughts? > > > > > > > > > > TIA > > > > > > > > > > -- > > > > > Gus Gustafson Thanks for your help. The multiple table retrieval is working. But more
imporetantly, I know how to extend the concept. Regards, Gus Show quote "CBretana" wrote: > Create Procedure GetSolutionData > @Source = null, -- pass as 'P' 'A' or null to get both > @ColA Integer = Null, > etc.... > As > Select 'Pend' As Source, * > From PendingTable > Where IsNull(@Source, 'P') = 'P' > And ColA = IsNull(@ColA, ColA) > And ... etc. > Union > Select 'Aprd' As Source, * > From ApprovedTable > Where IsNull(@Source, 'A') = 'A' > And (@domain_code Is Null or domain_code LIKE @domain_code) > And (@proponent_code Is Null Or proponent_code LIKE > @proponent_code) > And (@functional_area_code Is Null Or > functional_area_code LIKE @functional_area_code > > > "CBretana" wrote: > > > Sorry, didn;t edit that last right... > > > > > > Create Procedure GetObjectData > > @Source = null, -- pass as 'P' 'A' or null to get both > > @ColA Integer = Null, > > etc.... > > As > > Select 'Pend' As Source, * > > From PendingTable > > Where IsNull(@Source, 'P') = 'P' > > And ColA = IsNull(@ColA, ColA) > > And ... etc. > > Union > > Select 'Aprd' As Source, * > > From ApprovedTable > > Where IsNull(@Source, 'A') = 'A' > > And ColA = IsNull(@ColA, ColA) > > And ... etc. > > > > > > "CBretana" wrote: > > > > > Yes do the same thing in a stored Procedure, but you will have to code it to > > > handle whatever search criteria functionality the end users wish to have > > > available, using Srored Proc parameters with null deault values to control > > > the filtering... > > > > > > Create Procedure Get > > > @Source = null,, -- pass as 'P' 'A' or null to get both > > > @ColA Integer = Null, > > > etc.... > > > @ > > > As > > > Select 'Pend' As Source, * > > > From PendingTable > > > Where IsNull(@Source, 'P') = 'P' > > > And ColA = IsNull(@ColA, ColA) > > > And ... etc. > > > Union > > > Select 'Aprd' As Source, * > > > From ApprovedTable > > > Where IsNull(@Source, 'A') = 'A' > > > And ColA = IsNull(@ColA, ColA) > > > And ... etc. > > > > > > "Gus Gustafson" wrote: > > > > > > > OK, but there is a small difficulty: the customer will not allow me to create > > > > tables or views. So everything that I do must be done through SQL (usually > > > > stored procedures). I will pass on the view to the customer for > > > > implementation. In the meantime, is there another way that fits within these > > > > restrictions? > > > > > > > > Gus > > > > > > > > "CBretana" wrote: > > > > > > > > > You could create a view that cobines the two tables... Adding an expression > > > > > based attribute (column) in the process that would identify which table each > > > > > row came from > > > > > > > > > > Create View TableView > > > > > As > > > > > Select 'Pend' As Source, * > > > > > From PendingTable > > > > > Union > > > > > Select 'Aprd' As Source, * > > > > > From ApprovedTable > > > > > > > > > > > > > > > "Gus Gustafson" wrote: > > > > > > > > > > > In the database are two tables. One contains data that is pending some sort > > > > > > of resolution; the other contains data that has been approved for further > > > > > > use. Both tables contain the same attributes. When pending data is approved, > > > > > > that tuple is moved to the approved table and removed from the pending table. > > > > > > If new data is to be added, the incomplete data is placed in the pending > > > > > > table. Note that restrictions that apply to the approved table do not apply > > > > > > to the pending table. Duplicates are not allowed in either table nor between > > > > > > tables. > > > > > > > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > > > > some criteria. I'm not interested in building two datasets and then > > > > > > programmatically combining them. I'd like to do the task in one SQL statement > > > > > > if possible. I do care from which table the data is retrieved. > > > > > > > > > > > > Thoughts? > > > > > > > > > > > > TIA > > > > > > > > > > > > -- > > > > > > Gus Gustafson Yr welcome ! and good luck...
- Charly Show quote "Gus Gustafson" wrote: > Thanks for your help. The multiple table retrieval is working. But more > imporetantly, I know how to extend the concept. > > Regards, > > Gus > > "CBretana" wrote: > > > Create Procedure GetSolutionData > > @Source = null, -- pass as 'P' 'A' or null to get both > > @ColA Integer = Null, > > etc.... > > As > > Select 'Pend' As Source, * > > From PendingTable > > Where IsNull(@Source, 'P') = 'P' > > And ColA = IsNull(@ColA, ColA) > > And ... etc. > > Union > > Select 'Aprd' As Source, * > > From ApprovedTable > > Where IsNull(@Source, 'A') = 'A' > > And (@domain_code Is Null or domain_code LIKE @domain_code) > > And (@proponent_code Is Null Or proponent_code LIKE > > @proponent_code) > > And (@functional_area_code Is Null Or > > functional_area_code LIKE @functional_area_code > > > > > > "CBretana" wrote: > > > > > Sorry, didn;t edit that last right... > > > > > > > > > Create Procedure GetObjectData > > > @Source = null, -- pass as 'P' 'A' or null to get both > > > @ColA Integer = Null, > > > etc.... > > > As > > > Select 'Pend' As Source, * > > > From PendingTable > > > Where IsNull(@Source, 'P') = 'P' > > > And ColA = IsNull(@ColA, ColA) > > > And ... etc. > > > Union > > > Select 'Aprd' As Source, * > > > From ApprovedTable > > > Where IsNull(@Source, 'A') = 'A' > > > And ColA = IsNull(@ColA, ColA) > > > And ... etc. > > > > > > > > > "CBretana" wrote: > > > > > > > Yes do the same thing in a stored Procedure, but you will have to code it to > > > > handle whatever search criteria functionality the end users wish to have > > > > available, using Srored Proc parameters with null deault values to control > > > > the filtering... > > > > > > > > Create Procedure Get > > > > @Source = null,, -- pass as 'P' 'A' or null to get both > > > > @ColA Integer = Null, > > > > etc.... > > > > @ > > > > As > > > > Select 'Pend' As Source, * > > > > From PendingTable > > > > Where IsNull(@Source, 'P') = 'P' > > > > And ColA = IsNull(@ColA, ColA) > > > > And ... etc. > > > > Union > > > > Select 'Aprd' As Source, * > > > > From ApprovedTable > > > > Where IsNull(@Source, 'A') = 'A' > > > > And ColA = IsNull(@ColA, ColA) > > > > And ... etc. > > > > > > > > "Gus Gustafson" wrote: > > > > > > > > > OK, but there is a small difficulty: the customer will not allow me to create > > > > > tables or views. So everything that I do must be done through SQL (usually > > > > > stored procedures). I will pass on the view to the customer for > > > > > implementation. In the meantime, is there another way that fits within these > > > > > restrictions? > > > > > > > > > > Gus > > > > > > > > > > "CBretana" wrote: > > > > > > > > > > > You could create a view that cobines the two tables... Adding an expression > > > > > > based attribute (column) in the process that would identify which table each > > > > > > row came from > > > > > > > > > > > > Create View TableView > > > > > > As > > > > > > Select 'Pend' As Source, * > > > > > > From PendingTable > > > > > > Union > > > > > > Select 'Aprd' As Source, * > > > > > > From ApprovedTable > > > > > > > > > > > > > > > > > > "Gus Gustafson" wrote: > > > > > > > > > > > > > In the database are two tables. One contains data that is pending some sort > > > > > > > of resolution; the other contains data that has been approved for further > > > > > > > use. Both tables contain the same attributes. When pending data is approved, > > > > > > > that tuple is moved to the approved table and removed from the pending table. > > > > > > > If new data is to be added, the incomplete data is placed in the pending > > > > > > > table. Note that restrictions that apply to the approved table do not apply > > > > > > > to the pending table. Duplicates are not allowed in either table nor between > > > > > > > tables. > > > > > > > > > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > > > > > some criteria. I'm not interested in building two datasets and then > > > > > > > programmatically combining them. I'd like to do the task in one SQL statement > > > > > > > if possible. I do care from which table the data is retrieved. > > > > > > > > > > > > > > Thoughts? > > > > > > > > > > > > > > TIA > > > > > > > > > > > > > > -- > > > > > > > Gus Gustafson Sorry hit the enter key before I was ready..
Here is the modifed to mathc yr DDL Stored Proc... Create Procedure GetObjectData @Source = null, -- pass as 'P' 'A' or null to get both @domain_code Char(1) = Null, @proponent_code VarChar(12) = Null, @functional_area_code VarChar(8) = Null As Set NoCount On Select 'Pend' As Source, * From Pending_Solutions Where IsNull(@Source, 'P') = 'P' And (@domain_code Is Null Or domain_code LIKE @domain_code) And ( @proponent_code Is Null Or proponent_code LIKE @proponent_code) And ( @functional_area_code Is Null Or functional_area_code LIKE @functional_area_code) Union Select 'Aprd' As Source, * From Solutions Where IsNull(@Source, 'A') = 'A' And (@domain_code Is Null Or domain_code LIKE @domain_code) And ( @proponent_code Is Null Or proponent_code LIKE @proponent_code) And ( @functional_area_code Is Null Or functional_area_code LIKE @functional_area_code) Show quote "Gus Gustafson" wrote: > OK, but there is a small difficulty: the customer will not allow me to create > tables or views. So everything that I do must be done through SQL (usually > stored procedures). I will pass on the view to the customer for > implementation. In the meantime, is there another way that fits within these > restrictions? > > Gus > > "CBretana" wrote: > > > You could create a view that cobines the two tables... Adding an expression > > based attribute (column) in the process that would identify which table each > > row came from > > > > Create View TableView > > As > > Select 'Pend' As Source, * > > From PendingTable > > Union > > Select 'Aprd' As Source, * > > From ApprovedTable > > > > > > "Gus Gustafson" wrote: > > > > > In the database are two tables. One contains data that is pending some sort > > > of resolution; the other contains data that has been approved for further > > > use. Both tables contain the same attributes. When pending data is approved, > > > that tuple is moved to the approved table and removed from the pending table. > > > If new data is to be added, the incomplete data is placed in the pending > > > table. Note that restrictions that apply to the approved table do not apply > > > to the pending table. Duplicates are not allowed in either table nor between > > > tables. > > > > > > I need to write a query that extracts all tuples from both tables that meet > > > some criteria. I'm not interested in building two datasets and then > > > programmatically combining them. I'd like to do the task in one SQL statement > > > if possible. I do care from which table the data is retrieved. > > > > > > Thoughts? > > > > > > TIA > > > > > > -- > > > Gus Gustafson Create View TableView
As Select 'Pend' As Source, * From PendingTable Union Select 'Aprd' As Source, * From ApprovedTable Then you can query against that view Select * From TableView Where Source = 'Pend' Show quote "Gus Gustafson" wrote: > In the database are two tables. One contains data that is pending some sort > of resolution; the other contains data that has been approved for further > use. Both tables contain the same attributes. When pending data is approved, > that tuple is moved to the approved table and removed from the pending table. > If new data is to be added, the incomplete data is placed in the pending > table. Note that restrictions that apply to the approved table do not apply > to the pending table. Duplicates are not allowed in either table nor between > tables. > > I need to write a query that extracts all tuples from both tables that meet > some criteria. I'm not interested in building two datasets and then > programmatically combining them. I'd like to do the task in one SQL statement > if possible. I do care from which table the data is retrieved. > > Thoughts? > > TIA > > -- > Gus Gustafson On Sat, 14 May 2005 10:29:15 -0700, Gus Gustafson wrote:
>In the database are two tables. One contains data that is pending some sort Hi Gus,>of resolution; the other contains data that has been approved for further >use. Both tables contain the same attributes. When pending data is approved, >that tuple is moved to the approved table and removed from the pending table. >If new data is to be added, the incomplete data is placed in the pending >table. Note that restrictions that apply to the approved table do not apply >to the pending table. Duplicates are not allowed in either table nor between >tables. (snip) >Thoughts? Redesign this. Use one table, and add status as one extra column: CREATE TABLE (.... Status char(8) NOT NULL DEFAULT 'Pending', CHECK (Status IN ('Pending', 'Approved'), ....) Then create views to satsify those applications that expect this to be in two seperate tables and that can't be changed (yet). You'll find that many things will be lots eaasier to do with this design. And you ne longer need triggers or other tricks to prevent duplicates with different status. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics