SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query for Permutation & Combination
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 08/31/2012 :  17:18:41  Show Profile  Reply with Quote
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

India
570 Posts

Posted - 08/31/2012 :  17:54:28  Show Profile  Reply with Quote
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
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 08/31/2012 :  18:09:19  Show Profile  Reply with Quote
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
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 08/31/2012 :  18:27:42  Show Profile  Reply with Quote
Now trying this

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

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 08/31/2012 :  18:48:47  Show Profile  Reply with Quote
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
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 08/31/2012 :  18:49:37  Show Profile  Reply with Quote
tab1 and tab3 has column name as lump and tab2 and tab4 has column name as username

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 09/01/2012 :  03:50:39  Show Profile  Reply with Quote
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
Go to Top of Page

aakcse
Aged Yak Warrior

India
570 Posts

Posted - 09/01/2012 :  13:33:10  Show Profile  Reply with Quote
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

Edited by - aakcse on 09/01/2012 13:34:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000