Обсуждение: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

<div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi all,</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I am facing a problem trying to convert from MSSQL
procedureto PostgreSQL function. </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue">CREATE</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> <span
style="color:blue">PROCEDURE</span>dbo<span style="color:gray">.</span>THUBAN_SP_GENERATEID</span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> </span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New"">        @NEWID <span style="color:blue">VARCHAR</span><span style="color:gray">(</span>20<span
style="color:gray">)</span> <span style="color:blue">OUTPUT</span></span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue">AS</span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:blue"> </span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew"">        <span style="color:blue">SET</span> @NEWID <span
style="color:gray">=</span><span style="color:gray">(</span></span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New"">                       <span style="color:blue">SELECT</span> <span style="color:fuchsia">REPLACE</span><span
style="color:gray">(</span><spanstyle="color:fuchsia">SUBSTRING</span><span style="color:gray">(</span><span
style="color:fuchsia">CONVERT</span><spanstyle="color:gray">(</span><span style="color:blue">CHAR</span><span
style="color:gray">(</span>10<spanstyle="color:gray">),</span><span style="color:fuchsia">GETDATE</span><span
style="color:gray">(),</span>20<span style="color:gray">),</span>1<span style="color:gray">,</span>10<span
style="color:gray">),</span><spanstyle="color:red">'-'</span><span style="color:gray">,</span><span
style="color:red">''</span><spanstyle="color:gray">)</span></span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New"">                               <span style="color:gray">+</span> <span style="color:fuchsia">CAST</span><span
style="color:gray">(</span><spanstyle="color:fuchsia">REPLICATE</span><span style="color:gray">(</span>0<span
style="color:gray">,</span>8<spanstyle="color:gray">-</span><span style="color:fuchsia">LEN</span> <span
style="color:gray">(</span><spanstyle="color:fuchsia">ISNULL</span><span style="color:gray">(</span><span
style="color:fuchsia">CAST</span><spanstyle="color:gray">(</span><span style="color:fuchsia">SUBSTRING</span><span
style="color:gray">(</span><spanstyle="color:fuchsia">MAX</span><span style="color:gray">(</span>SEQ_ID<span
style="color:gray">),</span>9<spanstyle="color:gray">,</span>8<span style="color:gray">)</span> <span
style="color:blue">AS</span></span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew"">INTEGER<span style="color:gray">),</span>0<span
style="color:gray">)</span><span style="color:gray">+</span> 1<span style="color:gray">))</span> <span
style="color:blue">AS</span><span style="color:blue">VARCHAR</span><span style="color:gray">)</span></span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New"">                                       <span style="color:gray">+</span> <span
style="color:fuchsia">CAST</span><spanstyle="color:gray">(</span><span style="color:fuchsia">ISNULL</span><span
style="color:gray">(</span><spanstyle="color:fuchsia">CAST</span><span style="color:gray">(</span><span
style="color:fuchsia">SUBSTRING</span><spanstyle="color:gray">(</span><span style="color:fuchsia">MAX</span><span
style="color:gray">(</span>SEQ_ID<spanstyle="color:gray">),</span>9<span style="color:gray">,</span>8<span
style="color:gray">)</span><span style="color:blue">AS</span> INTEGER<span style="color:gray">),</span>0<span
style="color:gray">)</span><span style="color:gray">+</span> 1  <span style="color:blue">AS</span></span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue">VARCHAR</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray">)</span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew"">                        <span style="color:blue">FROM</span>
THUBAN_SEQ</span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew"">                        <span style="color:blue">WHERE</span> <span
style="color:fuchsia">SUBSTRING</span><spanstyle="color:gray">(</span>SEQ_ID<span style="color:gray">,</span>1<span
style="color:gray">,</span>8<spanstyle="color:gray">)=</span></span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:fuchsia">REPLACE</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:fuchsia">SUBSTRING</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:fuchsia">CONVERT</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue">CHAR</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray">(</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">10<span
style="color:gray">),</span><spanstyle="color:fuchsia">GETDATE</span><span style="color:gray">(),</span>20 <span
style="color:gray">),</span>1<spanstyle="color:gray">,</span>10<span style="color:gray">),</span><span
style="color:red">'-'</span><spanstyle="color:gray">,</span><span style="color:red">''</span><span
style="color:gray">)</span></span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew"">                        <span style="color:gray">)</span></span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:gray"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";color:gray"> </span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New"">        <span
style="color:blue">INSERT</span><span style="color:blue">INTO</span> THUBAN_SEQ <span style="color:blue">VALUES</span>
<spanstyle="color:gray">(</span>@NEWID<span style="color:gray">)</span></span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";color:gray"> </span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New"">       <span style="color:blue">SELECT</span> @NEWID <span style="color:blue">AS</span> ITEM_ID<span
style="color:gray">;</span></span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier
New"">GO</span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New""> </span><p
class="MsoNormal"><spanlang="EN-US">This is what I made, </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()</span><p class="MsoNormal"><span
lang="EN-US">RETURNSVARCHAR</span><p class="MsoNormal"><span lang="EN-US">AS $$</span><p class="MsoNormal"><span
lang="EN-US">DECLARENEWID VARCHAR;</span><p class="MsoNormal"><span lang="EN-US">DECLARE             SEQID
VARCHAR;</span><pclass="MsoNormal"><span lang="EN-US">BEGIN</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">                SELECT INTO NEWID TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD');</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">                -- IF EXISTS A ROW
INTHE TABLE STARTING WITH THE CURRENT_DATE, SELECT THE MAX OF THEM.</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">                IF EXISTS(SELECT(MAX(SEQ_ID)) FROM
THUBAN_SEQWHERE SEQ_ID LIKE (SELECT TO_CHAR(CURRENT_DATE::DATE, 'YYYYMMDD') || '%')) THEN</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">                              
SELECTINTO NEWID ((SELECT(MAX(SEQID)) FROM THUBAN_SEQ WHERE SEQ_ID LIKE NEWID || '%') + 1);</span><p
class="MsoNormal"><spanlang="EN-US">                               </span><p class="MsoNormal"><span
lang="EN-US">               </span>ELSE<p class="MsoNormal"><span lang="EN-US">                               -- THIS
ISNOT RIGHT AT ALL, RIGHT? HOW CAN I DO TO CONCATENATE AN INTEGER NUMBER LIKE 14 + SOME NUMBER OF 0 BEFORE?</span><p
class="MsoNormal"><spanlang="EN-US">                               SEQID := '00000001';</span><p
class="MsoNormal"><spanlang="EN-US">                               NEWID := NEWID + SEQID;</span><p
class="MsoNormal"><spanlang="EN-US">                               </span><p class="MsoNormal"><span
lang="EN-US">               END IF;</span><p class="MsoNormal"><span lang="EN-US">                </span><p
class="MsoNormal"><spanlang="EN-US">RETURN NEWID;</span><p class="MsoNormal"><span lang="EN-US">END;</span><p
class="MsoNormal"><spanlang="EN-US">$$ LANGUAGE plpgsql;</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">SELECT THUBAN_SP_GENERATEID();</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">Beside this, there is something than I would like to ask
thanI couldn’t find. How can I do to set a variable in a way like this as MSSQL does:</span><p class="MsoNormal"
style="margin-left:36.0pt"><spanlang="EN-US"> </span><p class="MsoNormal"
style="text-indent:18.0pt;text-autospace:none"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:blue">SET</span><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> @NEWID <span
style="color:gray">=</span><span style="color:gray">(</span><span style="color:blue">SELECT…… </span></span><p
class="MsoNormal"style="text-indent:18.0pt;text-autospace:none"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span><p class="MsoNormal"
style="text-indent:18.0pt;text-autospace:none"><spanlang="EN-US">And not doing SELECT INTO VARIABLE_TO_SET
(SELECT…..</span><pclass="MsoNormal" style="text-indent:18.0pt;text-autospace:none"><span lang="EN-US"> </span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US">All comments will be welcome, I am pretty new with
PostgreSQLbut I find It very interesting.</span><p class="MsoNormal" style="text-autospace:none"><span
lang="EN-US"> </span><pclass="MsoNormal" style="text-autospace:none"><span lang="EN-US">Thanks & Regards,</span><p
class="MsoNormal"style="text-autospace:none"><span lang="EN-US"> </span><p class="MsoNormal"
style="text-autospace:none"><spanlang="EN-US"> </span><p class="MsoNormal" style="text-autospace:none"><span
lang="EN-US">Ignacio</span></div>

On 03/17/10 17:52, Ignacio Balcarce wrote:
> CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID
>
>           @NEWID VARCHAR(20) OUTPUT
>
> AS
>
> SET @NEWID = (
>
> SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
>
> + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS
>
> INTEGER),0) + 1)) AS VARCHAR)
>
> + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS
>
> VARCHAR)
>
> FROM THUBAN_SEQ
>
> WHERE SUBSTRING(SEQ_ID,1,8)=
>
> REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
>
> )
>
> INSERT INTO THUBAN_SEQ VALUES (@NEWID)
>
> SELECT @NEWID AS ITEM_ID;
>
> GO
>

At a first glance
it looks like an

INSERT INTO thuban_seq(seq_id) VALUES (your_strange_string_processing( now() ))  RETURNING seq_id;

But i couldn't interpret your extremely strange string processing with 
dates.
Please, FIRST OF ALL, get rid of this unnecessary brainfuck,
use postgres date-time arithmetic and clarify the idea of this routine.

On 03/17/10 17:52, Ignacio Balcarce wrote:
> -- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE

Sorry, your field is not an atom => your database does not met a FIRST 
normal form.

it needs normalization urgently.

On 3/17/2010 9:52 AM, Ignacio Balcarce wrote: <blockquote
cite="mid:000001cac5e1$8851dbe0$98f593a0$@balcarce@vivatia.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Wingdings;panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face{font-family:Wingdings;panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph,
div.MsoListParagraph{mso-style-priority:34;margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
span.EstiloCorreo17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
.MsoChpDefault{mso-style-type:export-only;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1{page:Section1;}/* List Definitions */@list
l0{mso-list-id:1067802487;mso-list-type:hybrid;mso-list-template-ids:1065240002-1447672184 201981977 201981979
201981967201981977 201981979 201981967 201981977 201981979;}
 
@list l0:level1{mso-level-text:%1-;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level2{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level3{mso-level-number-format:roman-lower;mso-level-tab-stop:none;mso-level-number-position:right;text-indent:-9.0pt;}
@list l0:level4{mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level5{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level6{mso-level-number-format:roman-lower;mso-level-tab-stop:none;mso-level-number-position:right;text-indent:-9.0pt;}
@list l0:level7{mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list
l0:level8{mso-level-number-format:alpha-lower;mso-level-tab-stop:none;mso-level-number-position:left;text-indent:-18.0pt;}
@list l1{mso-list-id:1376933343;mso-list-type:hybrid;mso-list-template-ids:996162576 307771808 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l1:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:-;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:"Calibri","sans-serif";mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
@list l2{mso-list-id:1798378956;mso-list-type:hybrid;mso-list-template-ids:-914604966 2037692506 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l2:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:\F06E;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:Wingdings;mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
@list l3{mso-list-id:1934242401;mso-list-type:hybrid;mso-list-template-ids:-1502859232 -129998872 201981955 201981957
201981953201981955 201981957 201981953 201981955 201981957;}
 
@list
l3:level1{mso-level-start-at:2;mso-level-number-format:bullet;mso-level-text:-;mso-level-tab-stop:none;mso-level-number-position:left;margin-left:88.5pt;text-indent:-18.0pt;font-family:"Calibri","sans-serif";mso-fareast-font-family:Calibri;mso-bidi-font-family:"Times
NewRoman";}
 
ol{margin-bottom:0cm;}
ul{margin-bottom:0cm;}
--> </style><div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi all,</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">I am facing a problem trying to convert from MSSQL
procedureto PostgreSQL function. </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color: blue;">CREATE</span><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New";"> <span style="color: blue;">PROCEDURE</span> dbo<span
style="color:gray;">.</span>THUBAN_SP_GENERATEID</span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";"> </span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">         @NEWID <span style="color: blue;">VARCHAR</span><span
style="color:gray;">(</span>20<span style="color: gray;">)</span>  <span style="color: blue;">OUTPUT</span></span><p
class="MsoNormal"style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
blue;">AS</span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New";
color:blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family:
"CourierNew"; color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: blue;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size:
10pt;font-family: "Courier New";">        <span style="color: blue;">SET</span> @NEWID <span style="color:
gray;">=</span><span style="color: gray;">(</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">                        <span style="color: blue;">SELECT</span>
<spanstyle="color: fuchsia;">REPLACE</span><span style="color: gray;">(</span><span style="color:
fuchsia;">SUBSTRING</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">CONVERT</span><span
style="color:gray;">(</span><span style="color: blue;">CHAR</span><span style="color: gray;">(</span>10<span
style="color:gray;">),</span><span style="color: fuchsia;">GETDATE</span><span style="color: gray;">(),</span>20 <span
style="color:gray;">),</span>1<span style="color: gray;">,</span>10<span style="color: gray;">),</span><span
style="color:red;">'-'</span><span style="color: gray;">,</span><span style="color: red;">''</span><span style="color:
gray;">)</span></span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";">                               <span style="color: gray;">+</span> <span style="color:
fuchsia;">CAST</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">REPLICATE</span><span
style="color:gray;">(</span>0<span style="color: gray;">,</span>8<span style="color: gray;">-</span><span style="color:
fuchsia;">LEN</span><span style="color: gray;">(</span><span style="color: fuchsia;">ISNULL</span><span style="color:
gray;">(</span><spanstyle="color: fuchsia;">CAST</span><span style="color: gray;">(</span><span style="color:
fuchsia;">SUBSTRING</span><spanstyle="color: gray;">(</span><span style="color: fuchsia;">MAX</span><span style="color:
gray;">(</span>SEQ_ID<spanstyle="color: gray;">),</span>9<span style="color: gray;">,</span>8<span style="color:
gray;">)</span><span style="color: blue;">AS</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">INTEGER<span style="color: gray;">),</span>0<span style="color:
gray;">)</span><span style="color: gray;">+</span> 1<span style="color: gray;">))</span> <span style="color:
blue;">AS</span><span style="color: blue;">VARCHAR</span><span style="color: gray;">)</span></span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                                       
<spanstyle="color: gray;">+</span> <span style="color: fuchsia;">CAST</span><span style="color: gray;">(</span><span
style="color:fuchsia;">ISNULL</span><span style="color: gray;">(</span><span style="color: fuchsia;">CAST</span><span
style="color:gray;">(</span><span style="color: fuchsia;">SUBSTRING</span><span style="color: gray;">(</span><span
style="color:fuchsia;">MAX</span><span style="color: gray;">(</span>SEQ_ID<span style="color: gray;">),</span>9<span
style="color:gray;">,</span>8<span style="color: gray;">)</span> <span style="color: blue;">AS</span> INTEGER<span
style="color:gray;">),</span>0<span style="color: gray;">)</span> <span style="color: gray;">+</span> 1  <span
style="color:blue;">AS</span></span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: blue;">VARCHAR</span><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: gray;">)</span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family:
"CourierNew";">                        <span style="color: blue;">FROM</span> THUBAN_SEQ</span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                        <span
style="color:blue;">WHERE</span> <span style="color: fuchsia;">SUBSTRING</span><span style="color:
gray;">(</span>SEQ_ID<spanstyle="color: gray;">,</span>1<span style="color: gray;">,</span>8<span style="color:
gray;">)=</span></span><pclass="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt; font-family: "Courier
New";color: fuchsia;">REPLACE</span><span lang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
fuchsia;">SUBSTRING</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
fuchsia;">CONVERT</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color:
gray;">(</span><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New"; color: blue;">CHAR</span><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New"; color: gray;">(</span><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">10<span style="color: gray;">),</span><span style="color:
fuchsia;">GETDATE</span><spanstyle="color: gray;">(),</span>20 <span style="color: gray;">),</span>1<span style="color:
gray;">,</span>10<spanstyle="color: gray;">),</span><span style="color: red;">'-'</span><span style="color:
gray;">,</span><spanstyle="color: red;">''</span><span style="color: gray;">)</span></span><p class="MsoNormal"
style=""><spanlang="EN-US" style="font-size: 10pt; font-family: "Courier New";">                        <span
style="color:gray;">)</span></span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size: 10pt;
font-family:"Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span lang="EN-US" style="font-size:
10pt;font-family: "Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New";">        <span style="color: blue;">INSERT</span> <span
style="color:blue;">INTO</span> THUBAN_SEQ <span style="color: blue;">VALUES</span> <span style="color:
gray;">(</span>@NEWID<spanstyle="color: gray;">)</span></span><p class="MsoNormal" style=""><span lang="EN-US"
style="font-size:10pt; font-family: "Courier New"; color: gray;"> </span><p class="MsoNormal" style=""><span
lang="EN-US"style="font-size: 10pt; font-family: "Courier New";">        <span style="color: blue;">SELECT</span>
@NEWID<span style="color: blue;">AS</span> ITEM_ID<span style="color: gray;">;</span></span><p class="MsoNormal"><span
style="font-size:10pt; font-family: "Courier New";">GO</span><p class="MsoNormal"><span style="font-size: 10pt;
font-family:"Courier New";"> </span><span lang="EN-US"></span></div></blockquote><br /><br /> I surprised this works in
MSSQL<br/><br /> CREATE SEQUENCE THUBAN_SEQ<br />   INCREMENT 1<br />   MINVALUE 1<br />   MAXVALUE
9223372036854775807<br/>   START 1<br />   CACHE 1;<br /><br /><br /> Now for the function to generate the ID with the
dateleading <br /><br /><p class="MsoNormal"><span lang="EN-US">CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()<br
/></span><spanlang="EN-US">RETURNS VARCHAR</span><p class="MsoNormal"><span lang="EN-US">AS $$<br /></span><span
lang="EN-US"></span><spanlang="EN-US">BEGIN</span><br /><br /> --now we get the next value from the thuban_seq and add
thedate to the  front.  <br /><br /> return  to_char( current_timestamp,   'MMDDYYYY')::varchar ||
nextval('THUBAN_SEQ')::varchar<br/><p class="MsoNormal"><span lang="EN-US">                </span><p
class="MsoNormal"><spanlang="EN-US">RETURN NEWID;</span><p class="MsoNormal"><span lang="EN-US">END;</span><span
lang="EN-US">$$LANGUAGE plpgsql;</span><br /><br /> If this is not what your after you need to give more information
whatyou want to accomplish <br /><br /><br /><br /><br /> All legitimate Magwerks Corporation quotations are sent in a
.PDFfile attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any
otherform of communication will not be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail, including
attachments,may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and
isintended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended
recipientor authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution
orcopying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by
replyingto this message and destroy all occurrences of this e-mail immediately. <br /> Thank you. <br /> 

On 3/18/2010 12:53 PM, Ignacio Balcarce wrote: <blockquote
cite="mid:000b01cac6c3$f9f58520$ede08f60$@balcarce@vivatia.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph,
div.MsoListParagraph{mso-style-priority:34;margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
span.EstiloCorreo18{mso-style-type:personal;font-family:"Calibri","sans-serif";color:windowtext;}
span.EstiloCorreo19{mso-style-type:personal-reply;font-family:"Calibri","sans-serif";color:#1F497D;}
.MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><span style="color: rgb(31, 73, 125);">Justin,</span><p
class="MsoNormal"><spanstyle="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);">Thanks in advance for your email. I forgot to tell than everyday IDs must start from 0. So… sequence
idwould look like: YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73,
125);">00000001,YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73, 125);">00000002,
etc.</span><pclass="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span
lang="EN-US"style="color: rgb(31, 73, 125);">Is there any way to make this sequence start from 0 every day?</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US"
style="color:rgb(31, 73, 125);">Thanks & Regards,</span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);">Ignacio</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span>
---------------------------------------------<br/><p class="MsoNormal" style="margin-bottom: 12pt;"><span
style="font-size:12pt; font-family: "Times New Roman","serif";"><br /><br /></span></div></blockquote> --we need to
createa table so we keep track sequence number and when to reset the count<br /><br /> create table sequ_id ( id_number
int,sequ_name char(25), date_lastrun );<br /><br /> --insert a record ;<br /> insert into sequ_id  values (1, '<span
style="font-size:12pt; font-family: "Times New Roman","serif";">thuban_seq', current_date);<br /><br /><br />  Now for
thefunction to generate the ID with the date leading<br /><br />  CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()<br
/> RETURNS VARCHAR<br /><br />  AS $$<br /><br />  BEGIN<br /> --now update the sequ_id table so we know the value we
getmakes sense,<br /> Update sequ_id set id_number = 1 where sequ_name= </span> '<span style="font-size: 12pt;
font-family:"Times New Roman","serif";">thuban_seq' and date_lastrun</span> <> current_date;<br /><span
style="font-size:12pt; font-family: "Times New Roman","serif";"><br />  --now we get the next value from the thuban_seq
andadd the date to the  front. <br /><br />  return  to_char( current_date,   'YYYYMMDD')::varchar || ' ' || (Select
lpad(id_number::char, 7, '0' )::varchar from</span><span style="font-size: 12pt; font-family: "Times New
Roman","serif";">sequ_id</span><span style="font-size: 12pt; font-family: "Times New Roman","serif";"> </span><span
style="font-size:12pt; font-family: "Times New Roman","serif";">where sequ_name= </span> '<span style="font-size: 12pt;
font-family:"Times New Roman","serif";">thuban_seq' and date_lastrun</span>)<br /><br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";">Update sequ_id set id_number = (id_number + 1) where sequ_name= </span> '<span
style="font-size:12pt; font-family: "Times New Roman","serif";">thuban_seq';</span><br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";"><br /><br />  END;<br /> $$ LANGUAGE plpgsql;<br /><br /> this will do what you
want.<br/><br /> now i  have NOT  tested this but should get you closer, inside of the god awful code from before. <br
/><br/></span><br /><br /> All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a
uniqueID number generated by our proprietary quotation system. Quotations received via any other form of communication
willnot be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged,confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of
theindividual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the
readeris hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is
strictlyprohibited. If you have received this e-mail in error, please notify the sender by replying to this message and
destroyall occurrences of this e-mail immediately. <br /> Thank you. <br /> 

OOPS did not mean to click send <br /><br /> On 3/18/2010 12:53 PM, Ignacio Balcarce wrote: <blockquote
cite="mid:000b01cac6c3$f9f58520$ede08f60$@balcarce@vivatia.com"type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph,
div.MsoListParagraph{mso-style-priority:34;margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:36.0pt;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";color:black;}
span.EstiloCorreo18{mso-style-type:personal;font-family:"Calibri","sans-serif";color:windowtext;}
span.EstiloCorreo19{mso-style-type:personal-reply;font-family:"Calibri","sans-serif";color:#1F497D;}
.MsoChpDefault{mso-style-type:export-only;font-size:10.0pt;}
@page Section1{size:612.0pt 792.0pt;margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><span style="color: rgb(31, 73, 125);">Justin,</span><p
class="MsoNormal"><spanstyle="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);">Thanks in advance for your email. I forgot to tell than everyday IDs must start from 0. So… sequence
idwould look like: YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73,
125);">00000001,YYYYMMDD</span><span lang="EN-US"> </span><span lang="EN-US" style="color: rgb(31, 73, 125);">00000002,
etc.</span><pclass="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span
lang="EN-US"style="color: rgb(31, 73, 125);">Is there any way to make this sequence start from 0 every day?</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span><p class="MsoNormal"><span lang="EN-US"
style="color:rgb(31, 73, 125);">Thanks & Regards,</span><p class="MsoNormal"><span lang="EN-US" style="color:
rgb(31,73, 125);"> </span><p class="MsoNormal"><span lang="EN-US" style="color: rgb(31, 73, 125);">Ignacio</span><p
class="MsoNormal"><spanlang="EN-US" style="color: rgb(31, 73, 125);"> </span>
---------------------------------------------<br/><p class="MsoNormal" style="margin-bottom: 12pt;"><span
style="font-size:12pt; font-family: "Times New Roman","serif";"><br /><br /></span></div></blockquote> --we need to
createa table so we keep track sequence number and when to reset the count<br /><br /> create table sequ_id ( id_number
int,sequ_name char(25), date_lastrun date);<br /><br /> --insert a record ;<br /> insert into sequ_id  values (1,
'<spanstyle="font-size: 12pt; font-family: "Times New Roman","serif";">thuban_seq', current_date);<br /><br /><br />
---Now for the function to generate the ID with the date leading<br /><br />  CREATE OR REPLACE FUNCTION
THUBAN_SP_GENERATEID()<br/>  RETURNS VARCHAR<br /><br />  AS $$<br /> declare creturn varchar ;<br />     <br />
 BEGIN<br/> --now update the sequ_id table so we know the value we get makes sense,<br /> Update sequ_id set id_number
=1 where sequ_name= </span> '<span style="font-size: 12pt; font-family: "Times New Roman","serif";">thuban_seq' and
date_lastrun</span><> current_date;<br /><span style="font-size: 12pt; font-family: "Times New
Roman","serif";"><br/>  --now we get the next build the ID go to the table get the current value add some zeros in
frontand add the date to the  front. <br /><br />   </span><span style="font-size: 12pt; font-family: "Times New
Roman","serif";">creturn= </span><span style="font-size: 12pt; font-family: "Times New Roman","serif";"> to_char(
current_date,  'YYYYMMDD')::varchar || ' ' || (Select lpad( id_number::char, 7, '0' )::varchar from</span><span
style="font-size:12pt; font-family: "Times New Roman","serif";"> sequ_id</span><span style="font-size: 12pt;
font-family:"Times New Roman","serif";"> </span><span style="font-size: 12pt; font-family: "Times New
Roman","serif";">wheresequ_name= </span> '<span style="font-size: 12pt; font-family: "Times New
Roman","serif";">thuban_seq'</span>)<br /><br /> --update the sequence table<br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";">Update sequ_id set id_number = (id_number + 1) where sequ_name= </span> '<span
style="font-size:12pt; font-family: "Times New Roman","serif";">thuban_seq';</span><br /><span style="font-size: 12pt;
font-family:"Times New Roman","serif";">--return the value<br /> return creturn ;<br />  END;<br /> $$ LANGUAGE
plpgsql;<br/><br /> this will do what you want.<br /><br /> now i  have NOT  tested this but should get you closer,
insideof the god awful code from before. <br /><br /></span><br /><br /> All legitimate Magwerks Corporation quotations
aresent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations
receivedvia any other form of communication will not be honored. <br /><br /> CONFIDENTIALITY NOTICE: This e-mail,
includingattachments, may contain legally privileged, confidential or other information proprietary to Magwerks
Corporationand is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is
notthe intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination,distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error,
pleasenotify the sender by replying to this message and destroy all occurrences of this e-mail immediately. <br />
Thankyou. <br />