Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2071
  • Last Modified:

Can have this code in SQL Server?

I need to adjust this code to run on SQL server. P

create or replace function to_base36(s number)
return varchar2 as
begin
 if s < 36 then
  if s < 10 then
   return chr(s+48);
  else
   return chr(s+55);
  end if;
 else
  return(concat(to_base36(floor(s/36)), to_base36(s-floor(s/36)*36)));
 end if;
end;
/

create or replace function shortstamp(d date, hs number)
return varchar2 as
begin
 return concat(to_base36(466560 + (extract(year from d) - 1900) * 512 + extract(month from d) * 32 +
  extract(day from d)), to_base36(extract(hour from d) * 524288 + extract(minute from d) * 8192 +
  extract(second from d) * 128 + hs));
end;
/

hsecs := hsecs + 1;
record_id := shortstamp(sysdate, hsecs);
0
Vasi04
Asked:
Vasi04
  • 10
  • 6
  • 5
2 Solutions
 
Vasi04Author Commented:
This code was written for Oracle. I need to run this code on SQL Server.
When the run above code in QA, I get these errors:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'or'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'then'.
Server: Msg 195, Level 15, State 1, Line 6
'chr' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 8
'chr' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 11
'to_base36' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'from'.
0
 
Vasi04Author Commented:
Bill, can you help me on this :-)
0
 
imrancsCommented:
create function dbo.to_base36(@s numeric)
returns varchar(100) as
begin
 if @s < 36
 begin
  if @s < 10
   return char(@s+48)
  else
   return char(@s+55)
  end
 else
  return (Cast(dbo.to_base36(floor(@s/36)) as varchar)+ cast(dbo.to_base36(@s-floor(@s/36)*36)as varchar))

return '0'
end



create function dbo.shortstamp(@d datetime, @hs numeric)
returns varchar(100) as
begin
 return       cast(dbo.to_base36(466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 +
                    datepart(day,@d)) as varchar) +
                  cast(dbo.to_base36(datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 +
                    datepart(second,@d) * 128 + @hs) as varchar)
end
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
imrancsCommented:
if you dont want to ignore the decimle points in the calculations than please change numeric to numeric(18,2)

Imran
0
 
Vasi04Author Commented:
Thanks so much Imran

I get these errors in my QA:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@s'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 178, Level 15, State 1, Line 14
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '('.

Any idea?
0
 
Vasi04Author Commented:
I want to set values to these variables so that I can use these varibales in my SP

hsecs := hsecs + 1;
record_id := shortstamp(sysdate, hsecs);

Is this correct?
@hsecs = hsecs + 1;
@record_id = shortstamp(sysdate, hsecs);
0
 
BillAn1Commented:
to creat the functions above, just seperate them with a GO, or else run the two pieces of code seperately.
the correct syntax for your variables is

@hsecs = @hsecs + 1
@record_id = dbo.shortstamp(getdate(), @hsecs)
0
 
Vasi04Author Commented:
Thanks Bill. Nice to hera from you again
I had posted my oracle code above, wnated same code to run on my SQL server.  I get syntax errors with code suggested my Imran. I think there are minor changes.
Please help
0
 
Vasi04Author Commented:
Please help. I want have this code iny my SP
-----
Go
create function dbo.to_base36(@s numeric)
returns varchar(100) as
begin
 if @s < 36
 begin
  if @s < 10
   return char(@s+48)
  else
   return char(@s+55)
  end
 else
  return (Cast(dbo.to_base36(floor(@s/36)) as varchar)+ cast(dbo.to_base36(@s-floor(@s/36)*36)as varchar))

return 0
end

GO
create function dbo.shortstamp(@d datetime, @hs numeric)
returns varchar(100) as
begin
 return      cast(dbo.to_base36(466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 +
                 datepart(day,@d)) as varchar) +
               cast(dbo.to_base36(datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 +
                 datepart(second,@d) * 128 + @hs) as varchar)
end

@hsecs = @hsecs + 1
@record_id = dbo.shortstamp(getdate(), @hsecs)
---
I get these errors when I parse this my QA

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@s'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 178, Level 15, State 1, Line 14
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '('.
0
 
BillAn1Commented:
Vasi,
we're back to the old problem again - you are using SQLServer 7.0 right?
SQL7 doesn't suppport functions. You will need to do this another way - e.g. create then as procedures.
If you already have a sotred proc, that you want to call these functions, perhaps you can just put the code into the stored proc?
0
 
Vasi04Author Commented:
Thanks bill
yes sql 7.0
Pls help me to create procedures with above codes

Then pls advice how to call them as functions from another sp or trigger
0
 
BillAn1Commented:
here is the code in stored procedure format, plus an example of how to call them....

create procedure to_base36(@s numeric, @result varchar(100) OUTPUT)
as
begin
 if @s < 36
 begin
  if @s < 10
   set @result =  char(@s+48)
  else
   set @result =  char(@s+55)
  end
 else
 begin
  declare @res1 varchar(100)
  declare @res2 varchar(100)
  declare @s1 numeric
  declare @s2 numeric
  set @s1 = @s/36
  set @s2 = @s-floor(@s/36)*36
  exec to_base36 @s1, @res1 OUTPUT
  exec to_base36 @s2, @res2 OUTPUT
   set @result =  @res1 +  @res2
 end
end


GO
create procedure shortstamp(@d datetime, @hs numeric, @result varchar(100) OUTPUT)
as
begin

      declare @res1 varchar(100)
      declare @res2 varchar(100)

      declare @num1 numeric
      declare @num2 numeric

      set @num1 = 466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 + datepart(day,@d)
      exec to_base36 @num1, @res1 OUTPUT

      set @num2 = datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 + datepart(second,@d) * 128 + @hs
      exec to_base36 @num2 , @res2 OUTPUT

      set @result = @res1 + @res2
end


GO

declare @result varchar(100)
declare @hsecs numeric
declare @date datetime

set @hsecs = 1
set @date = getdate()
exec shortstamp @date, @hsecs, @result OUTPUT
select @result
0
 
Vasi04Author Commented:
Thanks Bill, you are a darling!!!
0
 
BillAn1Commented:
I think you should give credit to imrancs too, he did translate the Oracle to SQLServer for you !! - can you split points?
0
 
imrancsCommented:
thanks BillAn1 for you kind favour, I really appriciate it. It doesn't matter that I have credited some points or not, I am just glad that Vasi04 has got his problem solved.

Thanks again. :o)


Imran
0
 
Vasi04Author Commented:
Sorry Imran, will try to split the points.
Actually I was expecting your comments on error msgs which I posted twice. There was no follow-up for a long time. I was wondering whether to give up on this and develop a diff solution, instead of copying logic from oracle code. But for Bill's answer.
Thanks again. I have intentions of hurting you. :-)
Please advice me how could I split.
0
 
BillAn1Commented:
I think you mean "I have NO intentions of hurting you. :-)"  !!!
I think now that points have been assigned, you need to post a Q in the support area (0 points) asking the moderator to re-open Q, then you can split again.
0
 
Vasi04Author Commented:
Oops!! man....... I am somewhere else. May be I am stressed out by correcting and reprogramming SQL stuff!
Imran if you are able to read this post, I will be happy.
I HAVE NO INTENTIONS OF HURTING YOU!!! :-))


0
 
imrancsCommented:
Its okey man.


Imran
0
 
imrancsCommented:
hmmm,

Vasi04 I think you really take it serious :o) , any way I really appreiciate it.


Imran
0
 
imrancsCommented:
thanks Vasi04 and BillAn1 too.


Imran
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now