|
Home > Archive > MySQL ODBC Connector > January 2006 > MySql 5 replacement for computed default column value
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MySql 5 replacement for computed default column value
|
|
| Bryan Cantwell 2006-01-02, 8:23 pm |
| ------ =_NextPart_000_001C_
01C60FAB.AE2396B0
Content-Type: multipart/alternative;
boundary="---- =_NextPart_001_001D_
01C60FAB.AE2851A0"
------ =_NextPart_001_001D_
01C60FAB.AE2851A0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Trying to migrate to MySql 5 from Sybase asa. I have a tables that have
column values that are calculated based on other columns in the table. How
can I accomplish the same in MySql?
Here is sample of my asa table:
CREATE TABLE "DBA"."OpptyDetail"
(
"OpptyDetailID" numeric(40,0) NOT NULL,
"OpptyID" numeric(40,0) NOT NULL,
"ProductID" numeric(40,0) NULL,
"ServiceID" numeric(40,0) NULL,
"Quantity" numeric(40,0) NULL,
"Amount" CASH NULL DEFAULT 0.0,
"Description" text NULL,
"UserDefined1" varchar(100) NULL,
"UserDefined2" varchar(100) NULL,
"UserDefined3" varchar(100) NULL,
"UserDefined4" varchar(100) NULL,
"UserDefined5" varchar(100) NULL,
"UserDefined6" varchar(100) NULL,
"UserDefined7" varchar(100) NULL,
"UserDefined8" varchar(100) NULL,
"UserDefined9" varchar(100) NULL,
"UserDefined10" varchar(100) NULL,
"CreateDate" timestamp NOT NULL DEFAULT
current timestamp,
"LastModified" timestamp NOT NULL DEFAULT
current timestamp,
"TotalAmt" CASH NULL COMPUTE
(Amount*Quantity),
"Discount" numeric(5,2) NULL DEFAULT
0,
"MaterialSurcharge" numeric(4,2) NULL DEFAULT 0,
"SubTotal" CASH NULL COMPUTE (case
Discount when 100 then 0 else
isnull(amount,0)-(isnull((Discount/ 100),0)*isnull(amoun
t,0)) end),
"NetAmt" CASH NULL COMPUTE
(isnull(subtotal,0)*
isnull(Quantity,0)),
"MaterialAmount" CASH NULL COMPUTE
((isnull(amount,0)*i
snull(quantity,0))*(
isnull(MaterialSurch
arge,0)/100)),
PRIMARY KEY ("OpptyDetailID")
)
------ =_NextPart_001_001D_
01C60FAB.AE2851A0
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:Arial;
color:black;}
a:link, span.MsoHyperlink
{color:blue;
text- decoration:underline
;}
a:visited, span. MsoHyperlinkFollowed
{color:purple;
text- decoration:underline
;}
span.EmailStyle17
{mso-style-type:personal-compose;
color:black;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
& #123;page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor=3Dwhite background=3D"cid:image001.gif@01C60FAB.ADEF1930" =
lang=3DEN-US
link=3Dblue vlink=3Dpurple>
<img src=3D"cid:image001.gif@01C60FAB.ADEF1930"
v:src=3D"cid:image001.gif@01C60FAB.ADEF1930" v:shapes=3D"_x0000_Mail" =
width=3D0
height=3D0 class=3Dshape style=3D'display:non
e;width:0;height:0'>
<div class=3DSection1>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'>Trying to migrate to MySql 5 from Sybase asa. I have a tables =
that have
column values that are calculated based on other columns in the table. =
How can
I accomplish the same in MySql?<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'>Here is sample of my asa table:<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'>CREATE TABLE =
"DBA"."OpptyDetail"<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'>(<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "OpptyDetailID"
&=
nbsp; numeric(40,0)
NOT NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "OpptyID"
&=
nbsp; =
numeric(40,0)
NOT NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "ProductID"
&=
nbsp; numeric(40,0)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "ServiceID"
=
=
numeric(40,0)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "Quantity"
&=
nbsp; =
numeric(40,0)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "Amount"
&=
nbsp; CASH
NULL DEFAULT 0.0,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "Description"
&=
nbsp; text
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined1"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined2"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined3"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined4"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined5"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined6"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined7"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined8"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined9"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "UserDefined10"
&=
nbsp; varchar(100)
NULL,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "CreateDate"
&=
nbsp; timestamp
NOT NULL DEFAULT current timestamp,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "LastModified"
&=
nbsp; timestamp
NOT NULL DEFAULT current timestamp,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "TotalAmt"
&=
nbsp; CASH
NULL COMPUTE (Amount*Quantity),<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "Discount"
&=
nbsp; numeric(5,2)
NULL DEFAULT 0,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; & quot;MaterialSurchar
ge"
numeric(4,2) NULL
DEFAULT 0,<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "SubTotal"
&=
nbsp; CASH
NULL COMPUTE (case Discount when 100 then 0 else
isnull(amount,0)-(isnull((Discount/ 100),0)*isnull(amoun
t,0)) =
end),<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "NetAmt"
&=
nbsp; CASH
NULL COMPUTE =
(isnull(subtotal,0)*
isnull(Quantity,0)),
<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; "MaterialAmount"
CASH NULL COMPUTE
((isnull(amount,0)*i
snull(quantity,0))*(
isnull(MaterialSurch
arge,0)/100))=
, <o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> =
; PRIMARY
KEY ("OpptyDetailID")<o:p></o:p></span></font></p>
<p class=3DMsoNormal><font size=3D3 color=3Dblack face=3DArial><span =
style=3D'font-size:
12.0pt'> ) <o:p></o:p></span></font></p>
</div>
</body>
</html>
------ =_NextPart_001_001D_
01C60FAB.AE2851A0--
------ =_NextPart_000_001C_
01C60FAB.AE2396B0--
| |
| Daniel Kasak 2006-01-02, 8:23 pm |
| Bryan Cantwell wrote:
> Trying to migrate to MySql 5 from Sybase asa. I have a tables that
> have column values that are calculated based on other columns in the
> table. How can I accomplish the same in MySql?
>
This sounds a job for a trigger - an insert trigger.
I haven't used triggers in mysql, so I can't help you with exactly how
to do it.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting
.com.au
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Harald Fuchs 2006-01-03, 11:23 am |
| In article <43B99C1E. 8080401@nusconsultin
g.com.au>,
Daniel Kasak < dkasak@nusconsulting
.com.au> writes:
> Bryan Cantwell wrote:
> This sounds a job for a trigger - an insert trigger.
> I haven't used triggers in mysql, so I can't help you with exactly how
> to do it.
If SELECT performance is good enough, a VIEW would probably be easier.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Daniel Kasak 2006-01-03, 8:24 pm |
| Harald Fuchs wrote:
>In article <43B99C1E. 8080401@nusconsultin
g.com.au>,
>Daniel Kasak < dkasak@nusconsulting
.com.au> writes:
>
>
>
>
>If SELECT performance is good enough, a VIEW would probably be easier.
>
>
>
The advantage of a trigger is that it only runs when the data is
inserted ( and possibly updated ). Depending on the use of the database,
you may have a situation where the data is inserted and then selected
from multiple times - that's at least the usage pattern we have here. In
this case, you're better off with a stored procedure.
Also keep in mind that the title of the post said 'default', indicating
that the fields in question might take on a different value to the
calculated one.
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting
.com.au
website: http://www.nusconsulting.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|