Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can have this code in SQL Server?

Posted on 2004-09-15
23
Medium Priority
?
2,067 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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
 

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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

704 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