SQL statement of autoincremental column

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi<br>
We use PowerDesigner version 9.5.2.873.&nbsp; We have a&nbsp; physical model
where many tables have an incremental data type assigned to the primary
key. The physical model was generated for an IBM DB2 UDB ver 7
database. We do not want that the SQL statement regarding the primary
key reads <b><font color="#ff6600">generated by default</font></b>, as
shown below but we want it reads <b><font color="#3333ff">generated
always</font></b>.&nbsp; How can we make that PowerDesigner generate the
satetment the way we want?<br>
<br>
<tt>create table T232_UC<br>
(<br>
&nbsp;&nbsp; K232_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NUMERIC(5,0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not null <font
 color="#ff6600"><b>generated by default</b></font> as identity,<br>
&nbsp;&nbsp; T232_ST_REG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CHAR(1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not null default 'A'<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; constraint C_T232_ST_REG check (T232_ST_REG in ('A','N')),<br>
&nbsp;&nbsp; T232_COD_MINCOMEX&nbsp;&nbsp;&nbsp; CHAR(3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; not null<br>
);<br>
<br>
</tt>Regards<br>
Ramiro<br>
</body>
</html>
0
Ramiro
11/12/2004 8:06:09 PM
sybase.powerdesigner.general 9408 articles. 1 followers. Follow

5 Replies
339 Views

Similar Articles

[PageSpeed] 49

Go into the Property sheet for the column.
Select the Extended Attributes tab.
You should see 3 items there.
	If not, then please apply all maintenance patches.
1. ExtFieldProcName
2. ExtGeneratedAs - This is the one you want to change!
3. ExtSubtypeData

-- 
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike@nospam!datamodel.org (can you figure what to remove?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement



Ramiro Arias Amaya wrote:
> Hi
> We use PowerDesigner version 9.5.2.873.  We have a  physical model where 
> many tables have an incremental data type assigned to the primary key. 
> The physical model was generated for an IBM DB2 UDB ver 7 database. We 
> do not want that the SQL statement regarding the primary key reads 
> *generated by default*, as shown below but we want it reads *generated 
> always*.  How can we make that PowerDesigner generate the satetment the 
> way we want?
> 
> create table T232_UC
> (
>    K232_ID              NUMERIC(5,0)       not null *generated by 
> default* as identity,
>    T232_ST_REG          CHAR(1)            not null default 'A'
>       constraint C_T232_ST_REG check (T232_ST_REG in ('A','N')),
>    T232_COD_MINCOMEX    CHAR(3)            not null
> );
> 
> Regards
> Ramiro
0
Mike
11/13/2004 7:47:37 PM
Hi Mike. Thank you for the answer.
Is there a way to make the definition you mentioned at the model level 
instead at the column level?
Thanks and regards
Ramiro

Mike Nicewarner wrote:

> Go into the Property sheet for the column.
> Select the Extended Attributes tab.
> You should see 3 items there.
>     If not, then please apply all maintenance patches.
> 1. ExtFieldProcName
> 2. ExtGeneratedAs - This is the one you want to change!
> 3. ExtSubtypeData
>
0
Ramiro
11/13/2004 9:58:27 PM
You could look at your DB2 definition file.
Find the column extended attribute.
Change the default to the way you want it.
That should work.  Let me know if you have trouble.

-- 
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike@nospam!datamodel.org (can you figure what to remove?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement



Ramiro Arias Amaya wrote:
> Hi Mike. Thank you for the answer.
> Is there a way to make the definition you mentioned at the model level 
> instead at the column level?
> Thanks and regards
> Ramiro
> 
> Mike Nicewarner wrote:
> 
>> Go into the Property sheet for the column.
>> Select the Extended Attributes tab.
>> You should see 3 items there.
>>     If not, then please apply all maintenance patches.
>> 1. ExtFieldProcName
>> 2. ExtGeneratedAs - This is the one you want to change!
>> 3. ExtSubtypeData
>>
0
Mike
11/14/2004 9:46:02 PM
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Mike.<br>
I am sorry. I could not find for the IBM DB2 UDB ver 7
database definition file in PowerDesigner version 9.5.2.873, the column
extended attribute.&nbsp; I entered in the folder
DB2UDB7::Script\Objects\Column with no success.&nbsp; I would appreciate if
you can tell me specifically where I can make PD&nbsp; generates the SQL
statement&nbsp; <b>...generated always...</b> instead of <b>...generated
by default..</b>&nbsp; for an auto incremental data type assigned to a
primary key.<br>
<br>
Thanks and regards<br>
Ramiro<br>
<br>
<br>
&nbsp; We have a&nbsp; physical model
where many tables have an incremental data type assigned to the primary
key. The physical model was generated for an IBM DB2 UDB ver 7
database<br>
<br>
Mike Nicewarner wrote:
<blockquote cite="mid4197d218$1@forums-2-dub" type="cite">You could
look at your DB2 definition file.
  <br>
Find the column extended attribute.
  <br>
Change the default to the way you want it.
  <br>
That should work.&nbsp; Let me know if you have trouble.
  <br>
  <br>
</blockquote>
</body>
</html>
0
Ramiro
11/16/2004 2:13:18 PM
Its not under the Script folder.
DB2UDB7::Profile\Column\Extended Attributes\ExtGenAlways
The comment says:
When set to true (generated always), indicates that DB2 will always 
generate a value for the column when a row is inserted into the table or 
whenever the result value of the generation-expression may change.
When set to false (generated by default), indicates that DB2 will 
generate a value for the column when a row is inserted into the table, 
unless a value is specified.

-- 
Mike Nicewarner [TeamSybase]
http://www.datamodel.org
mike@nospam!datamodel.org (can you figure what to remove?)
Sybase product enhancement requests:
http://www.isug.com/cgi-bin/ISUG2/submit_enhancement



Ramiro Arias Amaya wrote:
> Hi Mike.
> I am sorry. I could not find for the IBM DB2 UDB ver 7 database 
> definition file in PowerDesigner version 9.5.2.873, the column extended 
> attribute.  I entered in the folder DB2UDB7::Script\Objects\Column with 
> no success.  I would appreciate if you can tell me specifically where I 
> can make PD  generates the SQL statement  *...generated always...* 
> instead of *...generated by default..*  for an auto incremental data 
> type assigned to a primary key.
> 
> Thanks and regards
> Ramiro
> 
> 
>   We have a  physical model where many tables have an incremental data 
> type assigned to the primary key. The physical model was generated for 
> an IBM DB2 UDB ver 7 database
> 
> Mike Nicewarner wrote:
> 
>> You could look at your DB2 definition file.
>> Find the column extended attribute.
>> Change the default to the way you want it.
>> That should work.  Let me know if you have trouble.
>>
0
Mike
11/17/2004 3:54:48 AM
Reply:

Similar Artilces:

Reuse derived columns in same SQL Select statement under SQL Server 2005
Hello, Our agency is in the process of migrating our Sybase Adaptive Server Anywhere 7.0 databases to Microsoft SQL Server 2005. We are currently using PowerBuilder 10.5. We have several datawindows that reuse SQL computed fields (derived columns) in the same SQL Select statement. Here is an example: select institution_code, incident_number, sequence, final_review_date, ts_status_code, idoc_number, (CASE WHEN coalesce(s.recommended_verbal,'N') <> coalesce(s.final_verbal,'N') then 1 else 0 end) as c_verbal, (CASE WHEN coalesce(s.recomme...

General SQL statement?
Once I have a table in SQLAnywhere 5.5.04, is there any way to generate a SQL statement that would have created it? Someone else needs to recreate the structure of my table and I'd like to send them a SQL statement that they could use to do so. One way to do it is to (for example, you need foo_table TABLE): 1) unload database (can be done from Sybase Central) into foo.sql 2) copy "CREATE TABLE foo_table" command where you need it... Petar Rick Charnes wrote: > > Once I have a table in SQLAnywhere 5.5.04, is there any way to generate > a SQL stateme...

Inserting BLOB Type column data with other columns data(normal Text)
In below code, SQL Statement is used to Insert BLOB Type data using Serialization. It is required to insert other (normal text) with BLOB Type data as well. Like to get below SQL statement amended so that all the columns(ID,name, blob) can be inserted. Just now below code working fine Inserting BLOB Type data. I think it is not stright forward way to insert all the columns(normal text) with BLOB?  1 protected void button2_Click(object sender, EventArgs e) 2 { 3 //Connection string 4 string oradb = datasource= mysource;userid="xyz" password="passwo...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

Parsing columns from a sql statement
Hi, How I could get the name of the columns from a string variable that has a sql statement? "select this, that, (to_date('02-02-04','mm-dd-yy')) from table" I want to obtain the column names "this", "that" and (to_date('02-02-04','mm-dd-yy')) Thank you I would suggest to use the POS and MID functions. Joseph wrote: > Hi, How I could get the name of the columns from a string > variable that has a sql statement? > > "select this, that, (to_date('02-02-04','mm-dd-yy')) from >...

datawindow not seeing aliased columns in SQL statement
Below is a stripped down version of our SQL statement. The problem is the 2 columns from aliased tables: "ST_FORGN_CNTRY_CD_TBL_A"."ABBR" and "ST_FORGN_CNTRY_CD_TBL_B"."ABBR". The SQL works absolutely fine when I execute within Oracle's SQL*Plus, so I know the SQL isn't the problem. The problem is that the datawindow does not seem to be distinguishing between the 2 columns (it replaces the table alias with the table's real name). By doing this, we are not getting the real value for one of the 2 columns. The columns we can select fr...

Help with totalling columns in SQL select statement
Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work! I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order. I must be doing something wrong, can anyone help? SELECT sum(Order_Item.Quantity) as Items, sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total FROM [Order] JOIN Order_Item ON Order_Item.[OrderID]...

dynamically adding columns to datagrid from sql statement
hi,i have seen some of the datagrid samples in internet. In most of the cases the datagrid is bind to some table. But if i want to display the output of an sql query(contains select statement with n number of columns) in Datagrid, i can simply use AutogenerateColumns=true and populate the datagrid without having any bound columns. That is fine.If i want to add some more extra columns like a checkbox column and an edit link button column i can do it using property builder. But,the problem is how can i order the columns in datagrid.for example: 1st col: checkboxes        &n...

SQL Logging SQL Statement
Hi at All!! I use ASA 7 so I'd like to know if there is a way to logging the sql instruction that database server dbsrv7, receive from clients ? I need this because a client that use odbc connection after performing an insert into a table, the client receive the Message: "SQL truncate". I need to know the field on the table that generate this warning!! Thanks in advance Massimo The tool that does this is called "request-level logging" and in V7 you have to enable it from the command line when you start the server, using the -zr (and optionally -zo) c...

If Statement combined with SQL statement.
Hi  This is going to sound like quite  a strange question. Basically what I am trying to do is use Membership.GetUser().UserName to get the current user who is logged in and I want to see if that user exists in a table on my database before adding some data to the labels. I figured it should go in the page load part. The name of the table is userteams. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If Membership.GetUser().UserName exists in Userteams Then goallie.Text = Select player FROM Userteams Where Username = Membership.getUser().Use...

update multiple columns in one sql statements
I am almost sure I can update variables columns in one select/case type statement, but having problems working out the syntax. I have a table with transactions - with tran types as the key. in this example, types = A,B,C ,D. in this first example I am updating the sum of QTY to value t_A based on tran types =A. can I perform sub query/case to update with the same where clause but for types B,C and D?? I also have to insert for specific lot numbers each sum values. Create table #t_reconcile( t_lot_number int not null, t_A ...

Adding an IF Statement to the SQL Statement
I have the following tables and SQL statment which works out a league table for me from the given results. I am now trying to add a bit more to this: If the Walkover column = 1 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the home team will recieve 3 points, and away team recieves 0 points, win column will be 1 and the played colum will be 1 as well.If the Walkover column = 2 then that row will be a home walkover meaning that the score will be 0(Home Score) - 0(Away Score) but the away team will recieve 3 po...

Viewing a SPIDs last SQL statement using sqltext()...though sometimes only partial SQL statements are shown
The dbcc sqltext() command seems to allow you to view the last SQL statement issued by an active SPID on the server. However I've noticed that sometimes only part of the SQL statement is returned - does anyone know why this might be, and if so whether it's due to a server configuration. Anyway, here's my notes on how to view the last SQL statement processed: ** How to see the last SQL a SPID has processed -- dbcc traceon(3604) Directs trace output to the client, rather than to the error log 1> dbcc traceon(3604) 2> go DBCC execution completed. If DBCC pr...

how to autogenerate value in a column using Update sql statement.
 have value like 0809000001 and would like to autoincrement this field starting with this value.Without reading each individual record. The value should be (last no +1) for each row.The column datatype is varchar. Seems like a perfect scenario for an int (or bigint for large numbers) identity field instead. You can seed the identity field to start at 809000001.If you really need the 0 at the beginning you could always accomadate this when selecting data by CASTing identity column to a varchar and padding with extra 0's as needed.An alternative would be to use a trigger on the tabl...

Web resources about - SQL statement of autoincremental column - sybase.powerdesigner.general

Resources last updated: 12/20/2015 7:47:39 AM