Query for Permutation & Combination

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

Query for Permutation & Combination

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 17:18:41

I have a requirement as below, all the four table has column as user, if table is right if not it has lump as column name, in which case i need to ignore that table and print 'file not found' for such table



begin try
declare @tabcol1 varchar(50)
declare @tabcol2 varchar(50)
declare @tabcol3 varchar(50)
declare @tabcol4 varchar(50)

SELECT @tabcol1 = column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name ='tab1' and column_name='lump'
.
.
Select @tabCol4 = .... --same as above for tab4

/*if all 4 @tabcol1...@tabcol4 are equals to lump then raise error... else if any of these 4 do have have col name as lump then display data as below

username  vtab1   vtab2             vtab3    vtab
Joe       Yes     file not found    Yes      file not found 
smpt      Yes     file not found    Yes      file not found 

*/

IF (
	 @tabcol1= 'lump' AND 
	 @tabcol2= 'lump' AND 
	 @tabcol3= 'lump' AND 
	 @tabcol4= 'lump'
   ) 
BEGIN
SET @msg= 'All File  does not exist.'
RAISERROR( @msg,11,1)
END

select @tabcol1 = isnull(@tabcol1,'nolump')
.
.
select @tabcol1 = isnull(@tabcol4,'nolump')




CASE When @tabcol1 = 'lump' AND @tabcol2= 'nolump'.... AND @tabcol4='nolump'
then
set SQLString = '
with cte(userName) as
(
select username from tab1
union
select 'tab2 file not exists' 
union
select 'tab3 file not exists'
union
select 'tab4 file not exists'
)
select userName,
vtab1=CASE WHEN EXISTS(SELECT 1 FROM tab1 WHERE [UserName] = CTE.[UserName]) THEN ''Yes'' ELSE ''No'' END,
vtab2=CASE WHEN EXISTS(SELECT 1 FROM tab2 WHERE [UserName] = CTE.[UserName]) THEN ''Yes'' ELSE ''No'' END,
vtab3=CASE WHEN EXISTS(SELECT 1 FROM tab3 WHERE [UserName] = CTE.[UserName]) THEN ''Yes'' ELSE ''No'' END,
vtab4=CASE WHEN EXISTS(SELECT 1 FROM tab4 WHERE [UserName] = CTE.[UserName]) THEN ''Yes'' ELSE ''No'' END,
'

--All the possible combination then print the results, earlier I did in this was as there were only 2 tables now I have 4 
.
.
.
.
EXEC (@sqlString)
end try


begin catch
---exception handling
end catch

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 17:54:28

I am doing something like below correct me if i am wrong, so far I am able to get the user list now I am left out with assigning 'Yes' 'No' based on that users existence in that table


set SQLString = '
with cte(userName) as
(
select (Case @tabcol1 when 'nolump' then username else '' end ) from tab1
union
select (Case @tabcol2 when 'nolump' then username else '' end ) from tab2
union
select (Case @tabcol3 when 'nolump' then username else '' end ) from tab3
union
select (Case @tabcol4 when 'nolump' then username else '' end ) from tab4
)
select username .../* here i need to develop this logic....

from 
cte
were cte.username<>''

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 18:09:19

The above is not working now trying the below one


with cte(userName) as
(
select (Case @tabcol1 when 'nolump' then select column_name from INFORMATION_SCHEMA.COLUMNS where table_name='tab1' else '' end ) from tab1
union
.
.

Any idea how I can dynamically do this.. or any other idea to implement this

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 18:27:42

Now trying this

SELECT (CASE @tabcol1 WHEN 'nolump' THEN UserName WHEN 'lump' THEN '' END) AS [UserName]
UNION..

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 18:48:47

for the table which has username as column it is working but giving error for the table which has column name as lump....


WITH CTE([UserName]) AS   
( 
  SELECT (CASE @coltab1 WHEN 'nolump' THEN UserName WHEN 'lump' THEN    NULL END) as username    FROM tab1
  UNION   
  SELECT (CASE @coltab2 WHEN  'nolump' THEN UserName WHEN 'lump' THEN    NULL END) as username    FROM tab2   
  UNION   
  SELECT (CASE @coltab3 WHEN 'nolump' THEN UserName WHEN 'lump' THEN  NULL END) as username    FROM tab3   
  UNION   
  SELECT (CASE @coltab4 WHEN  'nolump' THEN UserName WHEN 'lump' THEN  NULL END) as username    FROM tab4   
)     
select * from   CTE

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-31 : 18:49:37

tab1 and tab3 has column name as lump and tab2 and tab4 has column name as username

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-09-01 : 03:50:39

I was able to develop the cte query which good as per requirement, now I am left out with only developing the sub query to execute... could any one help me in developing the dynamic sub query....for this.. I have pasted the actual sub query but I want to construct this dynamically by adding 'missing file' as values to the tables which have column name as lump



begin try
DECLARE @col_var_adm_cron_at_allow VARCHAR(100)
DECLARE @col_var_adm_cron_at_deny VARCHAR(100)
DECLARE @col_var_adm_cron_cron_allow VARCHAR(100)
DECLARE @col_var_adm_cron_cron_deny VARCHAR(100)

DECLARE @msg VARCHAR(500)
DECLARE @SQLString VARCHAR(max)

SELECT @col_var_adm_cron_at_allow= column_name
FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_at_allow' and column_name ='LUMP'

SELECT @col_var_adm_cron_at_deny= column_name
FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_at_deny' and column_name ='LUMP'

SELECT @col_var_adm_cron_cron_allow= column_name
FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_cron_allow' and column_name ='LUMP'

SELECT @col_var_adm_cron_cron_deny= column_name
FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_cron_deny' and column_name ='LUMP'

IF (
	 @col_var_adm_cron_at_allow = 'lump'	AND 
	 @col_var_adm_cron_at_deny = 'lump'		AND 
	 @col_var_adm_cron_cron_allow = 'lump'	AND 
	 @col_var_adm_cron_cron_deny = 'lump'
   ) 
BEGIN
SET @msg= 'All File ''/var/adm/cron/at.allow AND /var/adm/cron/at.deny AND /var/adm/cron/cron.allow AND var_adm_cron_cron_deny'' does not exist.'
RAISERROR( @msg,11,1)
END
 
select @col_var_adm_cron_at_allow=ISNULL(@col_var_adm_cron_at_allow,'nolump')
select @col_var_adm_cron_at_deny=ISNULL(@col_var_adm_cron_at_deny,'nolump')
select @col_var_adm_cron_cron_allow=ISNULL(@col_var_adm_cron_cron_allow,'nolump')
select @col_var_adm_cron_cron_deny=ISNULL(@col_var_adm_cron_cron_deny,'nolump')


declare @table_Name table (id int identity(1,1) , tab_name nvarchar(max))
insert into @table_Name
select 'raw_3177_Normvar_adm_cron_at_allow' union
select 'raw_3177_Normvar_adm_cron_at_deny' union
select 'raw_3177_Normvar_adm_cron_cron_allow' union
select 'raw_3177_Normvar_adm_cron_cron_deny' 

declare @Cte_Query varchar(max)
declare @table_name2 varchar(max)
Declare @Sub_Query varchar(max)
declare @i int =1
set @cte_query = 'WITH CTE([UserName]) AS ('
while @i<=4
begin
	set @table_name2 =''
	select @table_name2= table_name from information_schema.columns where column_name <> 'lump' and table_name= (select tab_name from @table_Name where id=@i)


   	if isnull(@table_name2,'')<>''
		set @cte_query = @cte_query + ' SELECT [UserName] FROM '+ @table_name2 + ' Union '
		set @i=@i+1
end
set @cte_query = reverse(substring(reverse(rtrim(@cte_query)),6,len(@cte_query))) + ')'
print @cte_query

--Result
WITH CTE([UserName]) AS 
( SELECT [UserName] FROM raw_3177_Normvar_adm_cron_at_deny 
  Union  
  SELECT [UserName] FROM raw_3177_Normvar_adm_cron_cron_deny 
)

Now I have to develop the below query dynamically

set @Sub_Query ='
SELECT [UserName],
[Cron Allow]= CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_cron_allow WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
[Cron Deny] = CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_cron_deny WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
[At Allow] = CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_at_allow WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END,
[At Deny] = CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_at_deny WHERE [UserName] = CTE.[UserName]) THEN 'Yes' ELSE 'No' END, '

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-09-01 : 13:33:10

the below is the final query I have prepared which is working file, also contains the create table script and sample data insert script to run the query which is working perfectly fine... let me know if I can improve this in any way...


--First create the below tables and keep changing the column names in the tables 
--from lump to username and insert the correspounding records as below and execute the code to check the results

drop table raw_3177_Normvar_adm_cron_at_allow
drop table raw_3177_Normvar_adm_cron_at_deny
drop table raw_3177_Normvar_adm_cron_cron_allow
drop table raw_3177_Normvar_adm_cron_cron_deny

create table raw_3177_Normvar_adm_cron_at_allow ( username varchar(max))
create table raw_3177_Normvar_adm_cron_at_deny ( username varchar(max))
create table raw_3177_Normvar_adm_cron_cron_allow (lump varchar(max))
create table raw_3177_Normvar_adm_cron_cron_deny ( username varchar(max))

insert into raw_3177_Normvar_adm_cron_at_allow values('ABC1')
insert into raw_3177_Normvar_adm_cron_at_deny values('sch') ,('sch2')
insert into raw_3177_Normvar_adm_cron_cron_allow values('ABC2')
insert into raw_3177_Normvar_adm_cron_cron_deny values('sch1')

select * from raw_3177_Normvar_adm_cron_at_allow
select * from raw_3177_Normvar_adm_cron_at_deny
select * from raw_3177_Normvar_adm_cron_cron_allow
select * from raw_3177_Normvar_adm_cron_cron_deny


BEGIN TRY
	DECLARE @col_var_adm_cron_at_allow		VARCHAR(100)
	DECLARE @col_var_adm_cron_at_deny		VARCHAR(100)
	DECLARE @col_var_adm_cron_cron_allow	        VARCHAR(100)
	DECLARE @col_var_adm_cron_cron_deny		VARCHAR(100)

	DECLARE @msg VARCHAR(500)
	DECLARE @SQLString VARCHAR(max)

	SELECT @col_var_adm_cron_at_allow= column_name
	FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_at_allow' and column_name ='LUMP'
	SELECT @col_var_adm_cron_at_deny= column_name
	FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_at_deny' and column_name ='LUMP'
	SELECT @col_var_adm_cron_cron_allow= column_name
	FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_cron_allow' and column_name ='LUMP'
	SELECT @col_var_adm_cron_cron_deny= column_name
	FROM INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'raw_3177_Normvar_adm_cron_cron_deny' and column_name ='LUMP'

	IF (
		 @col_var_adm_cron_at_allow = 'lump'	AND 
		 @col_var_adm_cron_at_deny = 'lump'		AND 
		 @col_var_adm_cron_cron_allow = 'lump'	AND 
		 @col_var_adm_cron_cron_deny = 'lump'
	   ) 
	BEGIN
		SET @msg= 'All File ''/var/adm/cron/at.allow AND /var/adm/cron/at.deny AND /var/adm/cron/cron.allow AND var_adm_cron_cron_deny'' does not exist.'
		RAISERROR( @msg,11,1)
	END
 
	select @col_var_adm_cron_at_allow=ISNULL(@col_var_adm_cron_at_allow,'nolump')
	select @col_var_adm_cron_at_deny=ISNULL(@col_var_adm_cron_at_deny,'nolump')
	select @col_var_adm_cron_cron_allow=ISNULL(@col_var_adm_cron_cron_allow,'nolump')
	select @col_var_adm_cron_cron_deny=ISNULL(@col_var_adm_cron_cron_deny,'nolump')


	declare @table_Name_norm table (id int identity(1,1) , tab_name nvarchar(max))
	insert into @table_Name_norm
	select 'raw_3177_Normvar_adm_cron_at_allow' union
	select 'raw_3177_Normvar_adm_cron_at_deny' union
	select 'raw_3177_Normvar_adm_cron_cron_allow' union
	select 'raw_3177_Normvar_adm_cron_cron_deny' 
	
	if object_id('table_Name_lump') is not null
	drop table table_Name_lump
	create table table_Name_lump (id int identity(1,1) , tab_name nvarchar(max))

	declare @Cte_Query varchar(max)
	declare @Cte_Query1 varchar(max)
	declare @table_Name_exists varchar(max)
	declare @table_Name_NotExists varchar(max)
	declare @Sub_Query varchar(max)
	declare @i int =1
	set @cte_query1 = 'WITH CTE([UserName]) AS ('
	set @cte_query = ''
	while (@i<=4)
	begin
		set @table_Name_exists =''
		set @table_Name_NotExists=''

		select @table_Name_exists= table_name from information_schema.columns where column_name <> 'lump' and table_name= (select tab_name from @table_Name_norm where id=@i)
		select @table_Name_NotExists= table_name from information_schema.columns where column_name = 'lump' and table_name= (select tab_name from @table_Name_norm where id=@i)

   		if isnull(@table_Name_exists,'')<>''
   		begin
			select @cte_query = @cte_query + ' SELECT [UserName] FROM '+ @table_Name_exists + ' Union '
	    end
	    if isnull(@table_Name_NotExists,'')<>''
	    begin
			insert into table_Name_lump select @table_Name_NotExists 
		end
		set @i=@i+1
	end    

set @cte_query =  reverse(substring(reverse(rtrim(@cte_query)),6,len(@cte_query))) +')'
set @cte_query =  @cte_query1 + @cte_query

set @Sub_Query ='
				SELECT [UserName],    
				[Cron Allow]= CASE WHEN EXISTS(SELECT 1 FROM table_Name_lump where tab_name = ''raw_3177_Normvar_adm_cron_cron_allow'') 
				                 THEN ''corn allow File do not exists''
					           ELSE 
				                 CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_cron_allow WHERE [UserName] = CTE.[UserName]) 
				                 THEN ''Yes'' ELSE ''No'' 
				                 END
				               END,   
				
				[Cron Deny]	= CASE WHEN EXISTS(SELECT 1 FROM table_Name_lump where tab_name = ''raw_3177_Normvar_adm_cron_cron_deny'') 
								 THEN ''var adm cron cron deny file do not exists''
							  ELSE
				    			 CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_cron_deny WHERE [UserName] = CTE.[UserName]) 
				    			 THEN ''Yes'' ELSE ''No'' 
				    			 END   
				              END,
				
				[At Allow]	= CASE WHEN EXISTS(SELECT 1 FROM table_Name_lump where tab_name = ''raw_3177_Normvar_adm_cron_at_allow'') 
								THEN ''var adm cron at allow file do not exists''
							  ELSE	
								CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_at_allow WHERE [UserName] = CTE.[UserName]) 
								THEN ''Yes'' ELSE ''No'' 
								END   
							  END,
				
				[At Deny]	= CASE WHEN EXISTS(SELECT 1 FROM table_Name_lump where tab_name = ''raw_3177_Normvar_adm_cron_at_deny'') 
								THEN ''var adm cron at deny file do not exists''
							  ELSE
								CASE WHEN EXISTS(SELECT 1 FROM raw_3177_Normvar_adm_cron_at_deny WHERE [UserName] = CTE.[UserName]) 
							    THEN ''Yes'' ELSE ''No'' 
							    END
							  END 
				
				
				FROM CTE'
select @sqlstring = @Cte_Query + @Sub_Query			
exec (@sqlstring)

END TRY
BEGIN CATCH
 SELECT  ERROR_MESSAGE() AS [UserName],'' AS [Cron Allow],'' AS [Cron Deny] ,'' AS  [At Allow],'' AS [At Deny]
END CATCH

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources