Solved

Can have this code in SQL Server?

Posted on 2004-09-15
23
2,044 Views
Last Modified: 2012-05-05
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
Comment
Question by:Vasi04
  • 10
  • 6
  • 5
23 Comments
 

Author Comment

by:Vasi04
ID: 12062964
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
 

Author Comment

by:Vasi04
ID: 12063206
Bill, can you help me on this :-)
0
 
LVL 10

Assisted Solution

by:imrancs
imrancs earned 250 total points
ID: 12063227
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
 
LVL 10

Expert Comment

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

Imran
0
 

Author Comment

by:Vasi04
ID: 12063319
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
 

Author Comment

by:Vasi04
ID: 12063351
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12063534
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
 

Author Comment

by:Vasi04
ID: 12063620
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
 

Author Comment

by:Vasi04
ID: 12063666
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12064274
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Vasi04
ID: 12064414
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
ID: 12065417
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
 

Author Comment

by:Vasi04
ID: 12065657
Thanks Bill, you are a darling!!!
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12066118
I think you should give credit to imrancs too, he did translate the Oracle to SQLServer for you !! - can you split points?
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12066668
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
 

Author Comment

by:Vasi04
ID: 12068056
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12069154
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
 

Author Comment

by:Vasi04
ID: 12072526
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
 
LVL 10

Expert Comment

by:imrancs
ID: 12072714
Its okey man.


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12075020
hmmm,

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


Imran
0
 
LVL 10

Expert Comment

by:imrancs
ID: 12092937
thanks Vasi04 and BillAn1 too.


Imran
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now