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
 Convert query ( Resolved)

Author  Topic 

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 15:37:09
Hi,

I want to run the below query in SQL Server, could anyone help me in this



QL> !cat q.sql
WITH unix_perms AS
(
	SELECT 'lrwxrwxrwx' AS perm FROM DUAL UNION ALL
	SELECT 'drwxr-xr-x' AS perm FROM DUAL UNION ALL
	SELECT '-rw-r--r--' AS perm FROM DUAL UNION ALL
	SELECT '-rw-------' AS perm FROM DUAL
)
/* End Sample Data */
select
	perm
	,decode(translate(substr(perm,2,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7')
	||decode(translate(substr(perm,5,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7')
	||decode(translate(substr(perm,8,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7') dec
	,case substr(perm,1,1)
	when 'l' then 'sym'
	else to_char(777 - to_number(decode(translate(substr(perm,2,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7')
	||decode(translate(substr(perm,5,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7')
	||decode(translate(substr(perm,8,3),'rwx-','1110'),'000','0','001','1','010','2','011','3','100','4','101','5','110','6','111','7')),'fm000')
	end umsk
from unix_perms

PERM       DEC USK
---------- --- --------
lrwxrwxrwx 777 sym
drwxr-xr-x 755 022
-rw-r--r-- 644 133
-rw------- 600 177



-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-09 : 15:40:47
the posted query doesnt correspond to t-sql syntax. where are you trying to run this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 15:57:41
Hi Visakh this is Oracle query, I want to run a similar query in SQL Server to get the same output.

-Neil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-09 : 16:05:49
ok you need to replace the below

|| with + for concatenation
decode with CASE...WHEN...THEN...ELSE
translate with REPLACE()
substr with SUBSTRING()
to_char to CONVERT(char(xx),...)
to_number to CONVERT(int,...)

look for syntaxes of corresponding functions in msdn/books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 16:28:44
Thanks Visakh,

I'll try to change this 


step one
WITH unix_perms AS
(
	SELECT 'lrwxrwxrwx' AS perm  UNION ALL
	SELECT 'drwxr-xr-x' AS perm  UNION ALL
	SELECT '-rw-r--r--' AS perm  UNION ALL
	SELECT '-rw-------' AS perm 
) select * from unix_perms



-Neil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-09 : 16:47:51
quote:
Originally posted by aakcse

Thanks Visakh,

I'll try to change this 


step one
WITH unix_perms AS
(
	SELECT 'lrwxrwxrwx' AS perm  UNION ALL
	SELECT 'drwxr-xr-x' AS perm  UNION ALL
	SELECT '-rw-r--r--' AS perm  UNION ALL
	SELECT '-rw-------' AS perm 
) select * from unix_perms



-Neil

this will work in t-sql as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 16:49:20
Changed the code but not getting the desired result as Ora..


WITH unix_perms AS
(
	SELECT 'lrwxrwxrwx' AS perm  UNION ALL
	SELECT 'drwxr-xr-x' AS perm  UNION ALL
	SELECT '-rw-r--r--' AS perm  UNION ALL
	SELECT '-rw-------' AS perm 
) 
select
	perm
	,CASE replace(substring(perm,2,3),'rwx-','1110')  WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7' ELSE '' END
	+CASE replace(substring(perm,5,3),'rwx-','1110') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN'7'  ELSE '' END
	+CASE replace(substring(perm,8,3),'rwx-','1110') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN'7'  ELSE '' END
	,case substring(perm,1,1)
	when 'l' then 'symbolic'
	else cast(777 - cast(CASE replace(substring(perm,2,3),'rwx-','1110') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN'7'  ELSE '' END
	+CASE replace(substring(perm,5,3),'rwx-','1110') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN'7'  ELSE '' END
	+CASE replace(substring(perm,8,3),'rwx-','1110')WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN'7'  ELSE '' END as INT)  as varchar)
	end umsk
from unix_perms

perm	  (No column name)	umsk
lrwxrwxrwx		        symbolic
drwxr-xr-x		        777
-rw-r--r--		        777
-rw-------		        777


-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 16:50:41
looks like 'fm000' is some formatting in Ora and few more changes needed to get the o/p as ora

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 19:18:19
Hi Visakh,

Is there anything we can do with the above query..


-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-09 : 20:07:55
Oh Visakh, here Replace will not work need to do a rework I'll post the query once completed Sir.. thanks

select replace(substring('rwx',1,3),'rwx-','1110')
--rwx


-Neil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-10 : 10:01:13
quote:
Originally posted by aakcse

Oh Visakh, here Replace will not work need to do a rework I'll post the query once completed Sir.. thanks

select replace(substring('rwx',1,3),'rwx-','1110')
--rwx


-Neil

how can you find pattern which is longer than actual string?
so above replace doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts
Posted - 2012-08-10 : 14:05:00
Hi Visakh, I got it, the below query worked for me


WITH unix_perms AS
(
	SELECT 'lrwxrwxrwx' AS perm  UNION ALL
	SELECT 'drwxr-xr-x' AS perm  UNION ALL
	SELECT '-rw-r--r--' AS perm  UNION ALL
	SELECT '-rw-------' AS perm 
) 
select
	perm
	
	,CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0')  WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7' ELSE '' END
	+CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0')  WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
	+CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0')  WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101'THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
	
	,CASE substring(perm,1,1)
	when 'l' then 'symbolic'
	else 
	CASE  LEN(cast(777 - cast(CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
	      +CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
	      +CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END as INT)  as varchar)
	       ) 
	      
	      WHEN 0 THEN '000'+ cast(777 - cast(CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END as INT)  as varchar)
		         
		  WHEN 1 THEN '00' + cast(777 - cast(CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END as INT)  as varchar)
		          
		  WHEN 2 THEN '0'  + cast(777 - cast(CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END as INT)  as varchar)
		   
	ELSE
		  cast(777 - cast(CASE replace(replace(replace(replace(substring(perm,2,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,5,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END
		  +CASE replace(replace(replace(replace(substring(perm,8,3),'r','1'),'w','1'),'x','1'),'-','0') WHEN '000' THEN '0' WHEN '001'THEN '1'WHEN '010' THEN '2' WHEN '011' THEN '3' WHEN '100' THEN '4' WHEN '101' THEN '5' WHEN '110' THEN '6' WHEN '111' THEN '7'  ELSE '' END as INT)  as varchar)
	END 
  END as umsk	
from unix_perms



-Neil
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-10 : 15:19:26
cool...great stuff!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2012-08-16 : 10:16:22
Also prefix ; with WITH

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-16 : 14:39:07
quote:
Originally posted by madhivanan

Also prefix ; with WITH

Madhivanan

Failing to plan is Planning to fail

Only required if its within a batch with preceding statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2012-08-17 : 09:28:26
quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Also prefix ; with WITH

Madhivanan

Failing to plan is Planning to fail

Only required if its within a batch with preceding statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Yes but for the sake of safety always use it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-17 : 10:06:44
quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Also prefix ; with WITH

Madhivanan

Failing to plan is Planning to fail

Only required if its within a batch with preceding statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Yes but for the sake of safety always use it

Madhivanan

Failing to plan is Planning to fail

yep... thats anyways a good practise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site