Solved

Can have this code in SQL Server?

Posted on 2004-09-15
23
2,061 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 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

632 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