• RELEVANCY SCORE 3.49

    DB:3.49:Find Primary Key Names - Where Column-Names Are Same 11




    Hello Gurus,

    Can you please help me in getting the output as below from Oracle DB (using SQL Query).
    I have two tables one for Primary-Key information and other for Primary-key columns. I need to find the Primary-keys whose Primary-key-columns are same.

    Example:

    TABLE1: PK

    PK_NAMEPK_TABLE_NAME
    --------------------------------------------
    A_PKA
    B_PKB
    C_PKC
    D_PKD
    E_PKE

    TABLE2: PK_COLS

    PK_NAMEPK_COL
    ------------------------------
    A_PKAA1

    B_PKBB1
    B_PKBB2

    C_PKAA1

    E_PKEE1

    D_PKBB1
    D_PK BB2

    OUTPUT REQUIRED:

    PK1_NAME PK1_TABLE_NAMEPK2_NAME PK2_TABLE_NAME PK_COL
    ---------------------------------------------------------------------------------------------------------------
    A_PKAC_PKCAA1
    B_PKBD_PKDBB1
    B_PKBD_PKDBB2

    Thank you in advance,

    DB:3.49:Find Primary Key Names - Where Column-Names Are Same 11

    Hi,

    I got it done. Thank you for the guidelines

    Thanks

  • RELEVANCY SCORE 3.39

    DB:3.39:User Names pk





    Where can I find my Primary user name?







    Solved
    Go to solution

    DB:3.39:User Names pk


    https://login.comcast.net/myaccount/lookup

    After you enter the requested information, the page will tell you the userids on your account and indicate which one is Primary.

  • RELEVANCY SCORE 3.18

    DB:3.18:Collectovermetrics Output Explanation xs




    I ran the CollectOverMetrics.ps1 script and the output has a lot of data that I have no idea what it means. Any ideas where I can find the explanation to the meaning of the column names? Here is a part of the output and the column names I am referring
    to.

    DB:3.18:Collectovermetrics Output Explanation xs

    Thanks for the link. I started on that site and was wondering if there is anything more out there. I sent an email to MS contact and he stated that there is no official doc on the output.

  • RELEVANCY SCORE 3.16

    DB:3.16:T-Sql Query To Find Primary Key Column Of A Table jx


    Hello All,

    I Struck in a Scenario in my test environment with AdventureWorks2012 Database.I want to get primary key column name from all the tables.I tested the below query to get primary key column for the table 'Person.BusinessEntityAddress
    It is showing 3 columns as primary key column but actually they are not primary key column .Can anyone please give me exact query to get primary key column name of a table.

    SELECT c.Name
    FROM [sys].[index_columns] ic
    INNER JOIN [sys].[columns] c
    ON ic.[object_id] = c.[object_id]
    AND ic.[column_id] = c.[column_id]
    INNER JOIN [sys].[indexes] i
    ON i.[object_id] = ic.[object_id]
    AND i.[index_id] = ic.[index_id]
    WHERE i.is_primary_key = 1
    AND ic.[object_id] = OBJECT_ID('Person.BusinessEntityAddress');

    DB:3.16:T-Sql Query To Find Primary Key Column Of A Table jx

    Ok I will go through .Thank You
    use this to return only tables with single columnas PK
    SELECT TableName,ColumnName
    FROM
    (
    SELECT OBJECT_NAME(ic.object_id) AS TableName,c.Name AS ColumnName,
    COUNT(1) OVER (PARTITION BY ic.object_id) AS Cnt
    FROM [sys].[index_columns] ic
    INNER JOIN [sys].[columns] c
    ON ic.[object_id] = c.[object_id]
    AND ic.[column_id] = c.[column_id]
    INNER JOIN [sys].[indexes] i
    ON i.[object_id] = ic.[object_id]
    AND i.[index_id] = ic.[index_id]
    WHERE i.is_primary_key = 1
    --AND ic.[object_id] = OBJECT_ID('Person.BusinessEntityAddress');
    )t
    WHERE Cnt = 1

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

  • RELEVANCY SCORE 3.15

    DB:3.15:How To Pass Column Names With Spaces To The Where Clause In Table.Where Statement as


    Greetz!

    I am trying to pass a column that contains spaces to my mobile services client as in:

    query = aTable.where({
    ThisSpaced OutColumn Name:
    someValues});

    I've tried to use square brackets, single tics and double quotes. Can't find the documentation. Any ideas?
    Thanks!

    DB:3.15:How To Pass Column Names With Spaces To The Where Clause In Table.Where Statement as

    Hi Richard,
    Column names (and table names) can only contain letters, numbers and underscore characters (and cannot start with numbers). Table/column names with spaces are not supported currently.

    Carlos Figueira

  • RELEVANCY SCORE 3.13

    DB:3.13:Using Primary Keys In Datasets Datatable c1


    Hi, I have a dataset that has one table and 5 columns in it and I have set 2 columns in the dataset table as primary key Columns. Now, how can I make use of these primary key columns ?? For eg: If I want to say that get me all rows where PrimaryKeyColumn1 = '11' and primaryKeyColumn2 = '44' then how would I say that ?? Would I have to use DataTable.Select method ?? Dataset.Datable(0).Select ("PrimaryKeyColumn1 = 11 and primaryKeyColumn2 = 44) ???? If so, then whats the benefit of adding primary key columns ?? I can still use the select statement as it is by specifying the same column names without adding the columns as primarykey columns in the dataset.Thanks

    DB:3.13:Using Primary Keys In Datasets Datatable c1

    I would just use the RowFilter property of the Dataview object.Dim dv as DataViewdv = Dataset.DefaultViewdv.RowFilter = "Column1Name=11 and Column2Name=44"HTH,Greg

  • RELEVANCY SCORE 3.05

    DB:3.05:Multiple Arrays zs


    i need to get average of two different colums of multiple arrays

    i do not know where i am going wrong?

    import java.awt.*;
    class Array_count
    {
    public static void main(String[] args)
    { double tina = 0;
    double names[][]= new double[4][2];
    names[0][0] = 34.6;
    names[0][1] = 56.8;
    names[1][0] = 43.4;
    names[1][1]= 67.9;
    names[2][0]= 55;
    names[2][1]= 33.6;
    names[3][0]= 66;
    names[3][1]=45.9;

    for (int row=0;row4 ;row++ )
    {
    System.out.println("");
    for (int column=1;column 2 ;column++ )
    double tina =(names[0][1]+names[1][1]+names[2][1]+names[3][1]);
    {
    System.out.print("\t"+names[row][column]);
    System.out.println(tina);
    }

    }
    }
    }
    Please help

  • RELEVANCY SCORE 3.03

    DB:3.03:How To Included Primary Id Column Using Ssis Package? a7


    Hi All,I have target table where columns are ID, Name DateofBirth where ID is PK and should be increment of 1(eg. 1, 2, 3, 4... and so on) also NOTE that ID column is NOT AN IDENTITY columnNow I have flat file which has data with only Names Dateofbirth.So using SSIS package how should I input datas from flatfile into target table, means how to insert ID column data manually?Any ideasThanksregards,Kumar

    DB:3.03:How To Included Primary Id Column Using Ssis Package? a7

    Hi, I had one question. Is the ID column supposed to be unique? If that is the case you need to check the max ID value in the table before doing the above just to insure data integrity.Hope this helps !! - Sudeep |
    Please mark the post(s) as “Answered” that answers your query.

  • RELEVANCY SCORE 2.99

    DB:2.99:Viewing List Of Column Names 3a


    pls tell me the table where i can view all the column names and their data types of a particular table

  • RELEVANCY SCORE 2.99

    DB:2.99:Ea2 - Xls Import, Guess Column Names From Header Row? fp


    It would be nice if XLS import could derive the column names from the header row of the spreadsheet, especially in the case where there is a direct 1-1 mapping of the table column names and the header column names.

    DB:2.99:Ea2 - Xls Import, Guess Column Names From Header Row? fp

    Barry, that sounds great... I won't mind doing some extra clicking to handle date fields, etc.

    thanks!

  • RELEVANCY SCORE 2.97

    DB:2.97:Where Do I Find The Hidden Value Column And Display Text Column? j1


    I have looked up several posts to find something on this but no explanations.br /br /Where do I get the names of 'hidden value column' and 'hidden text column' of a drop-down list?br /How do I know what these columns are named to I can type the names in the 2nd and 3rd lines of the following code.br /br /var sDataConnectionName = "DataConnection";br /var sColHiddenValue = "value";br /var sColDisplayText = "value";

    DB:2.97:Where Do I Find The Hidden Value Column And Display Text Column? j1

    In fact I'll be presenting a session at the April PDF Conference (http://www.pdf2006.com) on exactly this. Creating a Dynamic document that talks to an Access database. Attendees will get a working sample PDF with tons of code in it.

    Thom Parker
    WindJack Solutions

    www.windjack.com

  • RELEVANCY SCORE 2.96

    DB:2.96:Ora-02270 Problem 9s


    Hi Guys,

    I'm working through the create table statements for my database but have run in to this error : SQL Error:

    ORA-02270: no matching unique or primary key for this column-list
    +02270. 00000 - "no matching unique or primary key for this column-list"+
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement+
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS+
    +catalog view[+

    The error occurs when I'm trying to create the CONTACT table which has a composite key from PROPERTY and STAFF which are listed first:

    CREATE TABLE PROPERTY
    (
    PRID CHAR(8) CONSTRAINT PROPERTY_PK PRIMARY KEY,
    BRID CHAR(5) CONSTRAINT PROPERTY_FK_BRANCH REFERENCES BRANCH(BRID),
    OWNER_ID CHAR(8) CONSTRAINT PROPERTY_FK_OWNER REFERENCES PERSON(PEID),
    TYPE CHAR(10) NOT NULL
    CONSTRAINT TYPE_CHECK CHECK (UPPER (TYPE) IN ('HOUSE', 'UNIT', 'TOWN_HOUSE', 'COMMERCIAL')),
    FIRST_LISTED DATE NOT NULL,
    STREET VARCHAR2(30) NOT NULL,
    SUBURB VARCHAR2(30) NOT NULL,
    POST_CODE CHAR(4) NOT NULL

    );

    CREATE TABLE STAFF
    (
    PEID CHAR(8) CONSTRAINT FK_PEID_STAFF REFERENCES PERSON(PEID),
    BRID CHAR(5) CONSTRAINT FK_BRID_STAFF REFERENCES BRANCH(BRID),
    DOB DATE NOT NULL,
    GENDER CHAR(1) NOT NULL,
    PAYROLL NUMBER(6) NOT NULL,
    JOB VARCHAR2(50) NOT NULL,
    CONSTRAINT STAFF_PK PRIMARY KEY(PEID, BRID)
    );

    CREATE TABLE CONTACT
    (
    PEID CHAR(8) CONSTRAINT FK_PEID_CONTACT REFERENCES STAFF(PEID),
    PRID CHAR(8) CONSTRAINT FK_PRID_CONTACT REFERENCES PROPERTY(PRID),
    CONSTRAINT CONTACT_PK PRIMARY KEY(PEID, PRID)
    );Many thanks,

    Mike

    DB:2.96:Ora-02270 Problem 9s

    I redesigned it by using an assumption that each member of staff has one job so person to staff is now a one to one and no repeating staff IDs mean my references in contact now work :D

  • RELEVANCY SCORE 2.96

    DB:2.96:Changing Caller Id Names 9x



    At present all of our phones have the same caller ID (the primary name on the account) and we were told we could fix this online. But so far I can't find anything on where that would be.

    Any help would be appreciated!!

    DB:2.96:Changing Caller Id Names 9x

    Planet-Ed wrote:

    I've never liked porting as it always seems to cause other problems (caller ID, messaging, etc).

    Have you tried a CNAM lookup to verify what your caller ID is showing/reporting as?

    This is a trial and allows you to test a few numbers. If you try using it too often it will block you. But enough just to test your ID on the line your having problems with.

    http://www.cnam.info/tryit

    http://www.cnam.info (This is the full site of you needed more).

    If its showing the correct caller ID information then it could be on your mothers landline side that is having the issue.

    Great links Planet-Ed. Thanks.Can definitely come in handy.

    I only ported twice in my life. Once when switching over our company from Sprint to ATT, and another time when our company purchased a block of DID's and one of them was xxx-xxx-xyxy, I had to get that number for myself. In both times it went smooth.

  • RELEVANCY SCORE 2.95

    DB:2.95:Need To Find Tables That Have Hostnames And Collection Names In The Sql Database. 19


    Hi,
    I have a custom computer inventory database. I would like to be able to compare the hostnames in this inventory database to the hostnames in a user created collection in configman. Where are the table names and column names for the hostnames and collection
    names save in the database)? Please help. Thanks.

  • RELEVANCY SCORE 2.94

    DB:2.94:Finding Column References? kd


    My requirement is to find the list of all programs using a certain column of a certain table. but i am having trouble doing it.

    An example is, i have a specific column in a table, another table also has a column which uses the same name for one of its column. In the programs we refer to the tables using alias names. If i want to find out all programs using that column from a specific table, how will i do it. Since the same column name can be used by another table.

    Tables:
    table1.column1
    table2.column1

    procedure1:
    select * from table1 a
    Where table1.column1 = 'xyz'
    some join conditions)

    Result desired: name of this procedure1

    Please help me here..

    DB:2.94:Finding Column References? kd

    Try using :

    select distinct name from user_source where instr(upper(text),'string to find')0
    /

    Replace 'string to find' with value you want to search .

    Regards,
    Rajesh

  • RELEVANCY SCORE 2.93

    DB:2.93:How To Modify This Query To Get The Desired Output Format jk


    I hv written a Query to display all the parent table names and their primary key columns(relevant to this foreign key of the child table).The query is given below...

    SELECT DISTINCT(TABLE_NAME) AS PARENT_TABLE,COLUMN_NAME AS PARENT_COLUMN
    FROM ALL_CONS_COLUMNS
    WHERE CONSTRAINT_NAME IN (SELECT AC.R_CONSTRAINT_NAME
    FROM ALL_CONSTRAINTS AC
    WHERE AC.TABLE_NAME=TABLE_NAME
    AND AC.TABLE_NAME='TABLE'
    AND AC.R_CONSTRAINT_NAME IS NOT NULL);

    This query will display all the parent tables and their primary key columns.Now my problem is that how to modify this query to also display the foreign key column name of the child table.

    I want the query result in the following format.The query should display the following columns.1)child table's name,2)child table's foreign key column name,3)the corresponding parent table's name,4)the parent table's primary key column name(which is the foreign key in the child table).

    For Example I want the output as follows...

    TAKE THE CASE OF SCOTT.EMP(AS INPUT TO YOUR QUERY)

    CHILD_TABLE CHILD_COLUMN PARENT_TABLE PARENT_COLUMN
    ---------------------- -------------------------- ------------------------- -----------------------------
    EMP DEPTNO DEPT DEPTNO

    In this result I hv used alias name for the columns.The query should display this only for the foreign keys in the child table.In the query which I sent to you earlier will give the parent table and the parent column names,But I also want to append the child table and child column names there.

    any help on how to tackle would be appreciated.

    DB:2.93:How To Modify This Query To Get The Desired Output Format jk

    To retrieve all relations where your specified table is child add:

    AND c.table_name='p_table'

    To retrieve all relations where your specified table is master add:

    AND p.table_name='p_table'

    ----------
    Anwar

  • RELEVANCY SCORE 2.93

    DB:2.93:How To Map Cmr Fields To Columns On Jboss3.0 Rc1 7c



    Hi all,I have a question about CMR mapping that I could not find any answer in the forums!It is:How can I tell jboss to use a specific column name for CMR fields. I saw on many examples (look inhttp://main.jboss.org/forums/thread.jsp?forum=46thread=12702)that we can only inform the primary key column names for the referred relation-role. But how jboss knows where to store this value into the target FK_COLUMN? Id like to inform this value to jboss too. In other words I need to tell jboss were to store the fk_key! Where and how I can define this?Do I have to use a CMP field to manage this field and pass a reference to it into the cmr mapping? If so how can I accomplish this?Thanks,JH

    DB:2.93:How To Map Cmr Fields To Columns On Jboss3.0 Rc1 7c


    Hi all,I have a question about CMR mapping that I could not find any answer in the forums!It is:How can I tell jboss to use a specific column name for CMR fields. I saw on many examples (look inhttp://main.jboss.org/forums/thread.jsp?forum=46thread=12702)that we can only inform the primary key column names for the referred relation-role. But how jboss knows where to store this value into the target FK_COLUMN? Id like to inform this value to jboss too. In other words I need to tell jboss were to store the fk_key! Where and how I can define this?Do I have to use a CMP field to manage this field and pass a reference to it into the cmr mapping? If so how can I accomplish this?Thanks,JH

  • RELEVANCY SCORE 2.91

    DB:2.91:Finding Matching Rows. 8a


    I've a requirement where i've to find matching rows. For example, there is a student table.

    Table 1 Name : Student
    ----------------
    Column Names in Student table
    ------------------------------------------
    STUDENT_ID
    STUDENT_NAME

    Table 2 Name : Subjects
    ----------------------
    Column Names in Subjects table
    ----------------------
    SUBJECT_ID,
    SUBJECT_NAME

    Table 3 Name : marks
    ------------------

    Column Names in Marks table:
    ---------------------------------------
    STUDENT_ID,
    SUBJECT_ID,
    MARKS

    Now one student can take any number of subjects and can give any number of attempts. Now I would like to know that has any other student other than the given student id has taken that particular combination of subjects and got the same number of marks including all attempts.

    Any help highly appreciated.

    Regards,
    Venky

    DB:2.91:Finding Matching Rows. 8a

    Hi, Venky,

    Whenever you have a question like this, it helps if you post a little sample data (CREATE TABLE AS ... statements or CREATE TABLE ... and INSERT ... statements), and the results you want to get from that data.

    I think Sen and I have different ideas about exactly what you want, and both of us may be wrong. If you post the information above, there will be less confusion.

    Here's my guess:
    WITH marks_summary AS
    (
    SELECT student_id
    , subject_id
    , SUM (marks)AS total_marks
    FROM marks
    GROUP BY student_id
    , subject_id
    )
    SELECT a.student_id
    , a.student_name
    , b.student_id
    , b.student_name
    FROM studenta
    JOIN studentbON a.student_id!= b.student_id
    AND NOT EXISTS(
    SELECTNULL
    FROMmarks_summaryma
    ,marks_summarymb
    WHEREma.subject_id= mb.subject_id (+)
    ANDb.student_id= mb.student_id (+)
    ANDma.student_id= a.student_id
    ANDma.total_marks!= NVL (mb.total_marks, 0)
    )
    ;

  • RELEVANCY SCORE 2.91

    DB:2.91:Finding Source Database Table/Column Name For A Column In A View ad


    Hi i need to be able to identify the original database table/column name for a column in a view.

    e.g. say i have a view like this
    create v1 as select a.name fname, b.name bname, c.name cname,......
    from u1.names a, u2.names b. u3.names c
    where .....

    Now I want to find out that the database table/column name for the fname, bname and cname columns in the view v1, which in this instance is u1.name.name, u2.names.name, u3.names.name.

    But i need to be able to do it for any view. Short of parsing the SQL is there an easy way of doing this?

    Now obviusly I can't do this for virtual columns but I will know my column is not virtual as it has an index on it.

    DB:2.91:Finding Source Database Table/Column Name For A Column In A View ad

    But i need to be able to do it for any view. Short of
    parsing the SQL is there an easy way of doing this?No, parsing the SQL is the only way. Good luck it is not something I would want to attempt.

  • RELEVANCY SCORE 2.90

    DB:2.90:Tableadapter Configuration Wizard Update Procedure Parameters 1f


    I have a process where a user has to update a single column in a table. I created a TableAdapter that uses existing stored procedures. The Select procedure takes no parameters, and it successfully populates the DataTable.
     
    The update procedure takes two parameters: the primary key in the DataTable, and the column I wish to update.
     
    When I click on the Update procedure in the TableAdapter configuration wizard, it correctly lists the two parameters. But the Source Column column in the Set Update procedure parameters grid is empty. When I click in that column for either parameter, it gives me an empty list of columns to select, and it will not allow me to type a column name there.
     
    The question: how do I get the TableAdapter Configuration Wizard to list the column in the DataTable so that I may select them for the appropriate parameters?
     
    The parameter names match the column names in the DataTable (but have the @ prefix). The DataTable has all of its fields marked as ReadOnly, except the field I wish to update.
     
    I'm running Visual Studio 2005 SP1 (version 2.0.50727) and am connecting to SQL Server 2000.

    DB:2.90:Tableadapter Configuration Wizard Update Procedure Parameters 1f

    I have a process where a user has to update a single column in a table. I created a TableAdapter that uses existing stored procedures. The Select procedure takes no parameters, and it successfully populates the DataTable.
     
    The update procedure takes two parameters: the primary key in the DataTable, and the column I wish to update.
     
    When I click on the Update procedure in the TableAdapter configuration wizard, it correctly lists the two parameters. But the Source Column column in the Set Update procedure parameters grid is empty. When I click in that column for either parameter, it gives me an empty list of columns to select, and it will not allow me to type a column name there.
     
    The question: how do I get the TableAdapter Configuration Wizard to list the column in the DataTable so that I may select them for the appropriate parameters?
     
    The parameter names match the column names in the DataTable (but have the @ prefix). The DataTable has all of its fields marked as ReadOnly, except the field I wish to update.
     
    I'm running Visual Studio 2005 SP1 (version 2.0.50727) and am connecting to SQL Server 2000.

  • RELEVANCY SCORE 2.90

    DB:2.90:Foreign Key Constraints kd


    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE11.2.0.1.0Production
    TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

    Hi all.
    I am having a problem adding a foreign key constraint to a table and was wondering if anyone had any ideas.
    I've made my example just about as simple as you can get.

    CREATE TABLE tparent(
    pid NVARCHAR2(10) NOT NULL,
    pid2 NVARCHAR2(10) NULL);
    CREATE UNIQUE INDEX ui_tparent ON tparent(pid);

    CREATE TABLE tchild(
    cid NVARCHAR2(10) NOT NULL,
    cid2 NVARCHAR2(10));
    CREATE UNIQUE INDEX ui_tchild ON tchild(cid);
    ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_tp FOREIGN KEY (cid) REFERENCES tparent(pid);

    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    ??? What - as you can see I have a unique index - ui_tparent.

    This works though if I use a PRIMARY KEY and not a UNIQUE INDEX?
    i.e
    ALTER TABLE tparent ADD CONSTRAINT pk_tparent PRIMARY KEY (pid);

    Ora docs state that in order to create a FK the parent needs either a UI or PK.

    Any ideas?

    regards,

    DB:2.90:Foreign Key Constraints kd

    Ahh, I see...

    In SQL SERVER a UNIQUE INDEX is enough but in Oracle you need to create a UNIQUE CONSTRAINT against your UNIQUE INDEX.

    i.e.

    ALTER TABLE tparent ADD CONSTRAINT uc_tparent UNIQUE (pid) USING INDEX ui_tparent;

    C'est la vie...

  • RELEVANCY SCORE 2.90

    DB:2.90:Syntax Of Ddl Options And Related (Table) Column Names zk


    Hi,
    where can I find something like a mapping between DDL options and related table column names?
    For example I do have the table options PCTFREE, FREELISTS and NOCOMPRESS. The related table columns out of user_tables are PCT_FREE, FREELIST and COMPRESS.
    PCT(_)FREE wins an Underscore, FREELIST(S) wins an "S" and it is "NOCOMPRESS" if COMPRESS has a value "Y(es)".
    Hope somebody can help.

    DB:2.90:Syntax Of Ddl Options And Related (Table) Column Names zk

    If there are no more DDLOptions possible than that, it is fine for me.
    If not, but all kinds of options are to find in only one or two tables of the database ( user_tables for table options, dba_constraints and dba_segments for column options, primary and foreign keys, user_indexes and user_segments for indexes ...), that is fine for me, too.
    (As long as I get to know which tables are "sufficient" for that.) If it is different than both of that, that's bad.Problem is you can never be sure that you covered all the tables/views where to get this information from. Additional tables could come into place we new oracle version. Somebody could change the tables to something you didn't expecte (partitioning?).

    Note that there is more logic in the DBMS_METADATA package than just GET_DDL. I could be that GTE_DEPENDENT_DDL or FETCH_DDL offers more in the way what you are looking for.

    However some others views that come into my mind that hold information closely related to tables are:
    for comments: ALL_TAB_COMMENTS, ALL_COL_COMMENTS
    for sequences: ALL_SEQUENCES
    for triggers: ALL_TRIGGERS
    for grants: dba_sys_privs, dba_tab_privs, dba_col_privs

  • RELEVANCY SCORE 2.90

    DB:2.90:List Of Primary Keys And Foreigh Keys With Columns Names zf


    Dear Friends,
    I am looking for below kind of result set

    constraint_name
    constraint_type
    table_name
    column_names

    PK_SOME_NAME
    PRIMARY KEY
    TableA
    col1,col2,col3

    FK_SOME_NAME
    FOREIGH KEY
    TableB
    col1,col2,col3

    Please provide a query to obtain this result set, the column list shoujld be the same where the PK is applied

  • RELEVANCY SCORE 2.90

    DB:2.90:Firebirdjboss - Entity-Command An Pk Generation k3



    hi,I'm trying to find out how to declare autogeneration of primary keys in jbosscmp-jdbc.xml.I know how to do this with the databases where this option is supported in standardjboss-cmp.xml (like postgre..):jbosscmp-jdbc:

    entity
    ejb-nameItem/ejb-name
    table-nameitem/table-name

    cmp-field
    field-namepk/field-name
    column-namepk/column-name
    auto-increment/
    /cmp-field
    ...
    entity-command name="postgresql-fetch-seq"
    /entity-command
    /entity

  • RELEVANCY SCORE 2.90

    DB:2.90:How To Get Constraint Name And Table And Column Names pk


    Hi Team,

    I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint.

    I used dba_constraints view. Please find below details.

    SQL select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints
    2 where TABLE_NAME='DSET_PRODUCT_S';

    OWNER CONSTRAINT_NAME C TABLE_NAME
    ------------------------------ ------------------------------ - ----------------------------------------
    SCOTT SYS_C10202456 C EMPLOYEE
    SCOTT SYS_C234576 C DEPT

    Please suggest any one.

    Regards,

  • RELEVANCY SCORE 2.88

    DB:2.88:Get Tables Names For Table With Single Primary Key And The Primary Key Column Name pc


    Hi,
    I have a schema where some of my tables have single primary key and some have 2 colums set as primary keys. What i need is a query to get the list of table names(distinct) who have only single primary key and the name of the primary key column in that table.
    So for example table1 has T1Col1 as pk, table 2 has T2Col1,T2Col2 as primary key, table3 has T3Col1 as primary key. I need result like below,
    TableName ColumnName
    Table1 T1Col1
    Table3 T3Col1

    Hope i state my requirement clearly enough.
    Thanks
    ZepherZepher684

    DB:2.88:Get Tables Names For Table With Single Primary Key And The Primary Key Column Name pc

    select tc.TABLE_NAME, tc.TABLE_SCHEMA, COLUMN_NAME = max(ccu.COLUMN_NAME)
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    on ccu.TABLE_NAME = tc.TABLE_NAME
    and ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA
    where tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    group by tc.TABLE_NAME, tc.TABLE_SCHEMA
    having COUNT(*) = 1

  • RELEVANCY SCORE 2.87

    DB:2.87:Lengths Of Table And Column Names xf


    Hello!

    I'm seeking to find how long a table and column name can be in Oracle 7, Oracle 8, 8i, and 9i.

    Can anyone direct me to where I can find this information?

    Thanks very much!

    Bill Loggins
    bill@acm.org

    DB:2.87:Lengths Of Table And Column Names xf

    The limit of both table and column names are 30 characters, with the first character needing to be a letter(alpha) with the other characters permitted to be any combination of alphanumeric and underscores that does not match an ORACLE reserved word.

    Reference manual for create table sybntax at this link:
    http://otn.oracle.com/pls/db92/db92.tabbed?section=129973

    HTH
    Dave

  • RELEVANCY SCORE 2.86

    DB:2.86:How To Tell If Physical Standby Is Sync With Primary When Primary Fail fm


    When primary fails, we need to tell our client if standby is absolutely synchronized with primary or there is possible data loss.

    For our data guard configuration, we use max availability mode and use data guard broker to manage.

    The column synchronized of view v$archive_dest_status in primary database tells if the standby is synchronized with the primary or not. When primary become unavailable, it seems there is no easy way to tell if the standby is in sync with the primary.

    I can find transport lag and apply lag from v$dataguard _stats. 0 transports lag and apply lag does not mean the standby is in sync with primary. This is where the standby database thinks it is in relation to primary.

    In max availability mode, the only chance that standby is out of sync with primary is the network between primary and standby fails before the primary database fail. If after some analysis, we confirm this is not what happened and I get 0 transports lag from v$dataguard _stats in standby, can I tell our client that there is absolutely no data loss in standby database?

    DB:2.86:How To Tell If Physical Standby Is Sync With Primary When Primary Fail fm

    When primary is still accessable, we can use v$archive_dest_status or scn in primary and standby.

    But for most of time when primary fail, we lose access to the primary database, how do we tell if the standby will has no data loss when we failover?

    So we can get transport lag, last redo applied time from standby database. If we get 0 transport lag, and last redo applied time is when the primary database fails, can we say there will be no data loss if we failover? Im talking in max availability mode.

  • RELEVANCY SCORE 2.86

    DB:2.86:Oracle Names ka


    Hello all
    where can I find documention for oracle names
    configration step by step?

    DB:2.86:Oracle Names ka

    I am able to start names and Listener but when I start listener it doesn't have register after sid any Idea?
    Thanks.

    BLOCKQUOTEfont size="1" face="Verdana, Arial"quote:/fontHROriginally posted by Shyam Gadepalli (shyam.gadepalli@oracle.com):
    Sangeeta,

    Here is the URL
    http://technet.oracle.com/doc/network.804/a58230/ch6.htm

    Good luck.

    ShyamHR/BLOCKQUOTE

    null

  • RELEVANCY SCORE 2.86

    DB:2.86:Schema All Tables Primary Key Column Names List f3


    Hi Folks,

    I have one user for Example Scott, i want to retrieve all the tables primary key column names in Scott user.

    can any one help me out please.

    Thanks in advance,
    karmaya

    DB:2.86:Schema All Tables Primary Key Column Names List f3

    Oracle scans data as BLOCKS. It does not know about row or column. When you join two table the relevant blocks are already picked. So there not going to be any (at least not significant) performance difference on what column you select.

  • RELEVANCY SCORE 2.85

    DB:2.85:How To Get Conditional Column Names Alone From An Sql p9


    select * from table_name where abc='abc' and xyz='xyz' and pqr=1 where

    abc
    xyz and
    pqr
    are column names and i need to take only those column names from the sql statement which is stored in a variable.

    pls give a soluiton

    DB:2.85:How To Get Conditional Column Names Alone From An Sql p9

    hi priya,

    u have to use only the dynamic sql EXEC_SQL in a plsql block.

    rgds,
    Thiyagu

  • RELEVANCY SCORE 2.85

    DB:2.85:Error While Alter The Table mm


    HiPlease let me know reasons for this below errorError starting at line 32 in command:ALTER TABLE DEVELOPMENT.BRANDING_STRINGSADD CONSTRAINT frk_strings_brandingsdetailsFOREIGN KEY (CUSTOMIZATION_ID) REFERENCES BRANDING_DETAILS (CUSTOMIZATION_ID)Error report:SQL Error: ORA-02270: no matching unique or primary key for this column-list02270. 00000 - "no matching unique or primary key for this column-list"*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.*Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

    DB:2.85:Error While Alter The Table mm

    According to the above error ,It seems the column CUSTOMIZATION_ID does not exist in table BRANDING_DETAILS. Message was edited by: BluShadow

    Begging for points removed by moderator.

  • RELEVANCY SCORE 2.85

    DB:2.85:Help Me In Solving This m9


    i have two columns one column contains names and another customer nos, in name column names arerepeated.
    my task is to put the single names i.e. unique names in another table names column and customer nos of dat names in a comma delimited form
    names customer no
    sumith 12456,987456,148852,148524
    rasul 456879,45645,415646
    i have sorted the names and compared it ,if i find names are equal i have inserted it in target table but i am unable to insert nos in comma delimited form.
    plz help me

    DB:2.85:Help Me In Solving This m9

    Try
    select C.[Name], stuff((select ',' convert(varchar(10), [Customer No]) from Customers C2 where C2.[Name] = C.[Name]

    order by [Customer No] FOR XML PATH('')),1,1,'') as [Customer Nos]

    from Customers C GROUP BY [Name]

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

  • RELEVANCY SCORE 2.85

    DB:2.85:Occi: Primary Key Column Meta Data 9x


    Hi

    We do some generic operation on database using occi, and we need to find out if some column in Resultset is primary key column.
    Occi provide MetaData class but we didn't find the way how to read metadata about primary key column.

    Does anybody know how to find out if column in OCCI Resultset is primary key column in database?

    Thx in advanced

  • RELEVANCY SCORE 2.85

    DB:2.85:Multiway Relationship -- How To Structure Aka's mx



    I have a situation where there is a name which will be the "primary" name, but there are also AKAs for that primary name (other related names as the primary name may be sometimes referred). So let's say the primary name is Michael, but there are related akas of Mike, Mikey, Miguel, etc.

    I would like to setup a table/relationship where if someone is looking for one name, the related names can be found as well. But where it is confusing me is if I want the related names to be related to each other as well.

    So I can figure out how to have a record for Michael with related aka's of Mike, Mikey, Miguel, etc.

    But what about having Mike be related to Mikey, Miguel, etc., and not just Michael.

    Do I have to have separate records for all of the permutations and combinations?

    Is there a better way to think about/approach this?

    So to clarify the usage, I want to assign to a record one of the predefined Primary Names. But if someone is doing a search for a name and they put in an AKA, I want it to find the Pimary name as well as all of the related AKAs.

    Thoughts would be greatly appreciated.

    DB:2.85:Multiway Relationship -- How To Structure Aka's mx


    You can also get the same results, a value list of all name across all records, without the extra relationships by creating a text field, populating it with the data from the original calcuations field (tested) via a script trigger (not tested) after any changes, and basing the value list on that field. The new text field does not have to be visable, but a commit record will be necessary to add any new names or changes to the value list.

  • RELEVANCY SCORE 2.84

    DB:2.84:Data Loading From Flat File Using Pl/Sql jm


    Hi

    I need to insert,delete,update (DML) the data from flat file which has the all column names list and data.Also how to find out the primary key column alone from flat file then based on the primary keys and values I have to delete the data from database .

    Thanks in Advance...

    Nanda kumar

    Message was edited by:
    user536487

    DB:2.84:Data Loading From Flat File Using Pl/Sql jm

    Hi
    The following link has a simple example for using external tables to load csv data.

    http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11210

    ;-)

  • RELEVANCY SCORE 2.83

    DB:2.83:Retrieve Column (Field) Names From Table Using Dao ap


    OpenSchema for ADO has a nice way of returning all the column names, but I'm having trouble doing this with DAO.

    Right now I have
    tmp_Data2 = DB.OpenRecordset(['SELECT DISTINCT MSysObjects.Name FROM MSysObjects WHERE Type=-32766'])
    fieldsint = tmp_Data2.Fields
    and then fieldsint.Count shows me how column names are there, but I can't figure out how to list the names.

    Can anyone tell me how to find the names of the columns with DAO?

    DB:2.83:Retrieve Column (Field) Names From Table Using Dao ap

    Thanks for spending the time to clarify that. As much as I wish it weren't true, it looks like this is the solution to my problem.

  • RELEVANCY SCORE 2.83

    DB:2.83:Object Search aa


    In the SQL 2005 Query Analyzer there was a tool for Object Search to find where column names etc were used in a database. Where is this tool in SQL 2005 Management studio?

    DB:2.83:Object Search aa

    I don't think you can do the same thing as you did in SQL 2K. However there is a rich set of system views you could use to get that information easily. Try this for your scenario now.

    Code Snippet

  • RELEVANCY SCORE 2.82

    DB:2.82:Vb Find Rows Col B 50, Return Col A Names To A Cell List 8a


    hi, i am trying to return the values in Column A (single word names), to a work cell in header for all rows that have a value 50 in Column B.
    each word can be separated by 1 space. i will be making that part of a click button event. thanks.

    i cannot think of any other example to give except that it will be asimilar / opposite action of a macro recently received.
    http://answers.microsoft.com/en-us/office/forum/office_xp-excel/vb-select-cut-text-from-a-cell-review-record-open/442e69dc-761f-4926-a5f5-58dcee2490f7

    - where a single 'results' cell of names separated byspaces, are one at a time sent to a work cell so each name can be reviewed.

    in a word problem:

    place all values in column A (1 word names) into a single work cell in header where value in column B is 50.
    separate each name with a space
    place names in descending order of values from column B (if possible), ie, in the work cell, the highest value in column B will be the first name listed (from column A),in the work cell.

  • RELEVANCY SCORE 2.82

    DB:2.82:Get Column Names xj


    Hi all,
    How do I get column names other than primary keys in a table

    Regards n Thanks

    DB:2.82:Get Column Names xj

    please try:

    sp_columns tablename

  • RELEVANCY SCORE 2.82

    DB:2.82:Getoledbschema Versus Getschema And How To Get Key Column Names p3


    Hi

    I have the following problem. When I was using previously the microsoft System.Data.OleDb library and had a method call GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys)
    I could retrieve the foreign column names and a corresponding primary column name in one loop.

    DataTable foreignKeysTables =
    connection.GetOleDbSchema(OleDbSchemaGuid.Foreign_Keys, ...);
    foreach (DataRow dr in foreignKeysTables.Rows)
    {
    string foreignKeyColumnName = dr["FK_COLUMN_NAME"].ToString();
    string primaryKeyColumnName = dr["PK_COLUMN_NAME"].ToString();
    }

    If I use the ODP.NET library, and use
    DataTable foreignKeysTables =
    connection.GetSchema("ForeignKeys", ...);
    Those columns are removed. Why are they removed if that is what the method is asking for.
    You can get only FOREIGN_KEY_TABLE_NAME and similar tables, but NO columns.
    Is there a reason for that, and how can I retrieve columns, that previously in an old version of a method, existed?

    The same stands for Primary_Keys, where previously, I could retrieve primary key column name with a call to:
    DataTable primaryKeysTables =
    connection.GetOleDbSchema(OleDbSchemaGuid.Primary_Keys, ...);
    foreach (DataRow dr in foreignKeysTables.Rows)
    {
    string primaryKeyColumnName = dr["COLUMN_NAME"].ToString();
    }

    But now the COLUMN_NAME is gone and I can only get primary key TABLE_NAME, but no column name.
    Why are the columns removed and what is the alternative?

    Regards,
    Sebastijan P.

    DB:2.82:Getoledbschema Versus Getschema And How To Get Key Column Names p3

    Hi,

    I believe that the ForeignKeyColumns meta-data collection is the one you want for the foreign key column names. You are correct in that ODP.NET does not expose a Primary Key columns meta-data collection. (It does expose the PrimaryKeys collection for the names but not the columns as you already know).

    In the case of the Primary Key columns, a query such as this might be able to satisfy your requirements:

    select
    a.table_name,
    a.constraint_name,
    a.column_name
    from
    user_cons_columns a,
    user_constraints b
    where
    b.constraint_type = 'P'
    and
    a.constraint_name = b.constraint_name
    order by
    a.table_name,
    a.constraint_name,
    a.positionRegards,

    Mark

  • RELEVANCY SCORE 2.82

    DB:2.82:How Column Names In A Table (Table A) Should Appear In Rows Of Target Table (Table B)? zx



    I have an requirement where in the column names in a table (table a)

    should appear in rows of target table (table b)

    i.E. Converting column names of src to rows tgt. how can achieve this?

    Regards,

    Venkat

    DB:2.82:How Column Names In A Table (Table A) Should Appear In Rows Of Target Table (Table B)? zx


    Hi Venkat,

    If you are using ORACLE 11g you can use the following query in SQL Override in Source Qualifier.

    SELECT table1.col1, listagg(table2.col2,',') within group(order by table2.rowid)

    FROM table1,table2

    where table1.col1=table2.col1 group by table1.col1

    Regards,

    Arun

  • RELEVANCY SCORE 2.81

    DB:2.81:Query To Find " ' " 79


    hi,

    Can anyone please provide me a query to find out names having " ' ", quotation like sa'ad, actually I have a employee name column in a table where i need to find names with quotation " ' ".

    Thanks in advance for your cooperation

    Regards,
    ahon

    DB:2.81:Query To Find " ' " 79

    I did it by using your query, thank you very much

    Edited by: ni**** on Feb 20, 2012 11:45 PM

  • RELEVANCY SCORE 2.81

    DB:2.81:Excel Comparison 7p


    I have two lists of names formatted as LastName, FirstName. I need to output a third column where Column a names are also in column b then I need to output a fourth column where the names in column B are not in Column a. This seems like it should be
    a common issue, but I can't find a simple way to do this. A little help please!

  • RELEVANCY SCORE 2.81

    DB:2.81:We Can't Find Items When We Try To Query Via Primary Supplier In Ascp Wb p3


    Hi all
    We run the data collection. (Complete Refresh. All options set to Yes)
    Then we run the ASCP plan.
    When we query the items via Primary Supplier, no rows returned. This issue is only for Primary Supplier.
    I realize that, in our MSC_SYSTEM_ITEMS table, the "PRIMARY_SUPPLIER_ID" column is empty.
    I think the data colleciton process didn't update this column.

    Where should I check?
    Data collection tables
    Profiles
    Parameters
    Any suggestions?
    Thanks
    Engin

  • RELEVANCY SCORE 2.81

    DB:2.81:Ora-02329, Primary Key On Ref's 3d


    Hello,
    I am just playing little bit with object types, and find out problem that I
    cannot create primary key index on nested table with ref column.

    I have following construction
    CREATE TYPE t1 AS OBJECT(
    code1 VARCHAR2(10)
    )
    /

    CREATE TYPE t1_ref_list AS TABLE OF REF t1
    /

    CREATE TYPE t2 AS OBJECT (
    code2 VARCHAR2(10),
    code1_list t1_ref_list)
    /

    CREATE TABLE t_t1 OF t1(
    CONSTRAINT t1_pk PRIMARY KEY (code1)
    )
    /

    CREATE TABLE t_t2 OF t2(
    CONSTRAINT t2_pk PRIMARY KEY (code2 )
    )
    nested TABLE code1_list STORE AS t1_nt
    /

    SQL alter table t1_nt add constraint nt_pk primary key (column_value)
    2 /
    alter table t1_nt add constraint nt_pk primary key (column_value)
    *
    ERROR at line 1:
    ORA-02329: column of datatype REF cannot be unique or a primary key

    What I would like to achieve is to have list of references to objects of type t1
    in code1_list , without possibility to have duplicit values in the list.

    Also when I have a query to select those objects from t_t2 where code1_list
    contains particular code, query will use full scan on nested table t1_nt,
    something like :
    SELECT *
    FROM t_t2 t2, TABLE (t2.code1_list ) l
    WHERE l.column_value=(SELECT REF(t)
    FROM t_t1 t
    WHERE t.code1='A')

    1. What is the reason behind, that primary key on ref column is not allowed.

    2. Is there any workarround for this?

    3. How to eliminate full scans on such nested table.

    Thanks

    ferdo

    DB:2.81:Ora-02329, Primary Key On Ref's 3d

    Roland,

    I know about dangling ref, but this should not be a primary key on referenced objects but on pointer (reference) value itself.

    Ref has a value, pointer to the object, in case I have 2 refs to the same object, value of pointer is the same and this I meant by primary key, so I will not allow to store in my Table of refs duplicit pointers.

    According some documentation usage of Ref's is "appropriate" in case object itself has a meaning.

    So let say I have object Person and very stupid thing like object Family as table of ref to Person.

    But there is no way how to say that one Person object should be in particular family collection only once.

    Is my way of thinking completely wrong?

    One more question for the same example, how can I define foreign key constraint so I cannot delete Person if such person exists in some family collection?

    ferdo

  • RELEVANCY SCORE 2.80

    DB:2.80:Want To Change Choices In Column dm


    I have loaded the Help desk application and the Service requests are fine except for the names in the drop down list.
     
    Currently they are Intiated, Engaged, resolved,closed
     
    I wish to have tehm set to
     
    Open, Pending, Resolved,Closed
     
    I cannot find any where to do that. I could create my own column with teh values but the list has this column as required and I cannot seem to find out where to delete it the site columns cannot delete it.
     
    Any one have any help  or how to change these values?
     
    Thanks
     
     

    DB:2.80:Want To Change Choices In Column dm

    Afeter changing sealed to FALSE you must restart iis
     
    Then in the application you click on the columns you want to change
     
    R

  • RELEVANCY SCORE 2.80

    DB:2.80:Pk Column. Where To Find It ? d9


    Hi guys

    I need to know wich column is the primary key of my table. First I looked in user_tab_cols
    select * from user_tab_cols where table_name = 'MY_TABLE'
    but it retrieves all the columns... where can I find only the primary key column ?

    thanks,
    Felipe

  • RELEVANCY SCORE 2.80

    DB:2.80:How To Find Similar Matching Names In Column? Sql a3



    Hey guys,

    I have a column called Name:

    Name

    Accounts Payable

    Accounts Payable, Total

    Accrued Expenses

    Accrued Expenses, Total

    Like above there are multiple similar sounding names in column. I need to find them, is there a sql query that will show me similar sounding names in column (not exact match). If so what is it? Please help me out.

    Thanks.

    DB:2.80:How To Find Similar Matching Names In Column? Sql a3


    Hi Meet ,

    Try using soundex as suggested by Vani . You have same function in informatica as well

  • RELEVANCY SCORE 2.79

    DB:2.79:Omitting Column Names In Database Diagrams 1p


    I want to use Visio to create a database diagram where each database table is shown as a box with the table inside, and nothing more -- no field/column names. There are a lot of tables in this database and I want to keep the diagram manageably simple. The
    following is what I did using a different diagram editor, yEd:

    Visio does not seem to want to allow this. A typical Visio diagram looks like this:

    I can reduce the number of lines by going to Database - Display Options, then unchecking Annotations, then selecting Physical order (instead of Primary keys at top):

    But there is always a blank line underneath the name of the database table. But I'm still not getting what I want: a simple box with a simple label, nothing more.
    I could just use yEd, but I'll never get everyone in my office to switch diagram editors. If there's a way to do this with Visio, I'd like to find out.

    DB:2.79:Omitting Column Names In Database Diagrams 1p

    Using Visio Professional 2010, I am not able to right-click shapes and get a Format Layer option or a Format Protection option.
    I did manage to figure out how to do Step 1B:
    File Options Advanced General Run in developer mode
    Select shape
    Developer Shape Design Protection Uncheck any options
    ...but I couldn't see how thiswas going to getme closer to my goal. I also couldn't do 1A.
    Does removing the protections of an Entity shape give you the ability to remove the unwanted blank line(s) as I described?
    Or is there any other way to modify an Entity shape to remove the unwanted blank lines?
    I was hoping to use the Entity shape because this IS an ER diagram, after all, andI like other aspects of Visio database diagrams.
    If this is impossible using the Entity shape, just let me know.

  • RELEVANCY SCORE 2.79

    DB:2.79:Re: Foreign Keys j8


    Foreign key must refer unique or primary key from referenced table .

    ORA-02270: no matching unique or primary key for this column-list
    Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

    Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

    DB:2.79:Re: Foreign Keys j8

    So how would I go about dropping table 2 for example with a foreign key and primary key?

    when you say drop constaint constraint_name DBA is "constraint" an actual command? and what is the constraint_name?

    thanks

  • RELEVANCY SCORE 2.78

    DB:2.78:Special Characters And Spaces In Tablenames And Column Names Are Allowed ? 1j


    Hi

    I have created two tables in Oracle10g

    as follows
    SQL create table test(columna number,columnb varchar2(20),primary key(columna));

    Table created.

    SQL insert into test values(1,'test');

    1 row created.

    SQL commit;

    COMMIT r utfrt.

    SQL create table "test quote"("#" number,abc number,primary key("#"));

    Table created.

    SQL insert into "test quote" values(1,2);

    1 row created.

    SQL commit;

    COMMIT r utfrt.

    SQL select * from "test quote";

    # ABC
    ---------- ----------
    1 2

    SQL select * from test;

    COLUMNA COLUMNB
    ---------- --------------------
    1 test

    Now I want to cache the tables and their data into TimesTen

    The cache group for table test which is not having any spaces and special characters in table name and column names is created perfectly.

    But when I create the cache group for table "test quote" which is having special characters and spaces it gives error :
    ---------------------------------------------------------------------------------------------------------------
    Commandcreate readonly cache group w2 from testuser.test(columna number,columnb varchar2(20),primary key(columna));
    Commandselect * from test;
    1,test
    1 row found.
    Commandcreate readonly cache group w3 from testuser."test quote"("#" number,abc number,primary key("#"));
    5140: Could not find TESTUSER.TEST QUOTE in Oracle. May not have privileges.
    Command Failed.

    --------------------------------------------------------------------------------------

    Now there is contradiction that why cache group for table test is created successfully and why it is not being created for table "test quote".

    What I think is if special characters and spaces in column names and table names are possible in Oracle then it sould be possible in TimesTen.

    Any possible solution.

    Looking forward for your reply.

    Please help I am stuck badly.

    /Ahmad

    DB:2.78:Special Characters And Spaces In Tablenames And Column Names Are Allowed ? 1j

    Hi

    Problem Solved ....... Remeber only Capital letters work with special characters ......

    /Ahmad

  • RELEVANCY SCORE 2.78

    DB:2.78:User Accounts zk



    Where can I find my primary and secondary user names?

    I need to enter a new pin # for my primary account .







    Solved
    Go to solution

    DB:2.78:User Accounts zk


    Thank you.

    Proberm Solved!!!

  • RELEVANCY SCORE 2.78

    DB:2.78:Getting The Updated Value And Its Primary Key 1d


    I have two procedures, proc1 and proc2.
    In proc1 there is an update happening like
    CREATE OR REPLACE procedure proc1
    as
    TYPE NumList IS TABLE OF employees.employee_id%TYPE;
    enums NumList;

    TYPE NameList IS TABLE OF employees.last_name%TYPE;
    names NameList;

    BEGIN
    UPDATE emp set sal=900 WHERE department_id = 30
    RETURNING employee_id, last_name BULK COLLECT INTO enums, names;

    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows:');

    FOR i IN enums.FIRST .. enums.LAST
    LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
    END LOOP;
    END;
    /In proc2 which is getting executed just after proc2 i want to SELECT the sal column values and its primary keys(employee_id) which got updated in proc 1.

    How can i achieve this?

  • RELEVANCY SCORE 2.78

    DB:2.78:Find Out Foreign Key Column 99


    How to find out foreign key column for a primary key column..

    DB:2.78:Find Out Foreign Key Column 99

    You can try to use and adapt follwowig SQL*Plus script (v_tn must be assigned to the table name which has the primary key. The script assumes all related tables are in the current schema):

    SQL column column_name format a20
    SQL column table_name format a20
    SQL
    SQL alter session set nls_language=american;

    Session altered.

    SQL select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
    PL/SQL Release 10.2.0.1.0 - Production
    CORE10.2.0.1.0Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    SQL
    SQL
    SQL drop table c purge;

    Table dropped.

    SQL drop table p purge;

    Table dropped.

    SQL
    SQL create table p(px int, py int);

    Table created.

    SQL alter table p add primary key(px,py);

    Table altered.

    SQL
    SQL create table c(c0 int primary key, c1 int check(c10), cx int, cy int, constraint ri foreign key(cx,cy) references p);

    Table created.

    SQL
    SQL
    SQL var v_tn varchar2(30);
    SQL exec :v_tn := 'p';

    PL/SQL procedure successfully completed.

    SQL
    SQL select c.table_name, c.constraint_name, cc.column_name
    2 from user_constraints c, user_cons_columns cc
    3 where r_constraint_name in
    4 (select constraint_name
    5 from user_constraints uc
    6 where uc.constraint_type = 'P' and uc.table_name = upper(:v_tn))
    7 and c.table_name = cc.table_name
    8 and c.constraint_name = cc.constraint_name;

    TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
    -------------------- ------------------------------ --------------------
    C RI CX
    C RI CY

    SQL
    SQL
    SQL exitEdited by: P. Forstmann on 8 juil. 2010 09:57

  • RELEVANCY SCORE 2.78

    DB:2.78:Find Identical Records In A Huge Table m3


    Hi,

    I have a huge table with 50+ collumns. I need to select records that have been dublicated (they have different primary keys, but identical data in every other row).

    I could go like this:

    select col_1, col_2, col_3,....., col_n, count(*) from TABLE
    group by col_1, col_2, col_3,....., col_n
    having count(*)1

    but do I really have to write all the column names. isn't there as shorter, simpler way?

    DB:2.78:Find Identical Records In A Huge Table m3

    Or

    set linesize 180
    SELECT 'select '
    || stragg (column_name)
    || ', count(*) from EMP_TEST group by '
    || stragg (column_name) ||' having count(*)1 ;' req
    FROM all_tab_columns
    WHERE table_name = 'EMP_TEST';

    REQ
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO, count(*) from EMP_TEST group by EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO having count(*)1 ;

    1 row selected.How can you implement the function stragg, see the link below.

    http://www.sqlsnippets.com/en/topic-11591.html

    Edited by: Salim Chelabi on 2011-11-22 07:26

  • RELEVANCY SCORE 2.78

    DB:2.78:Finding Column Names mk


    Is there a way to find and select the column names for a database table programmatically or in a query in Access?

    DB:2.78:Finding Column Names mk

    Thanks a million.................

  • RELEVANCY SCORE 2.77

    DB:2.77:Database Crashed - Dropped Index On Primary Key And Cannot Re-Establish 87


    Had a catastrophic database failure. Eventually rebuilt it and deleted one corrupted record. Thought everthing was ok until the users complained about abysmal slowness. Then discovered primary key setting and index dropped from main table autonumber column.
    Tried to reset the column to primary key and got message 'would create duplicate values'. This was an autonumber column !!! How do I find out where the duplicate is and deal with it. There are tens of thousands of rows.

    DB:2.77:Database Crashed - Dropped Index On Primary Key And Cannot Re-Establish 87

    1. Most important: back up the back-end database file so that you can always go back to the starting point.
    2. Run the Query with the following SQL:
    SELECT T.AutoNumField
    FROM YourProblematicTable AS T
    GROUP BY T.AutoNumField
    HAVING Count(T.AutoNumField) 1
    which should give you the duplicate values (there may be more than 1).
    3. Use the values found in 2 to find records that have the same value and see if you candelete the records so thatthere will be no dup values. Be careful if this isthe One Table of the 1-to-M relationships since ifdeleting
    the One record may delete the related Many recordsalso.
    4. Re-run the above SQL until the Query returns an emptyRecordset. You can then set the AutoNumber Field as the PK in your Table Design.
    5. Once the PK is OK, runAllen's Browne's article on fixing AutoNumberto ensure the Seed Number is correct for new record(s) to be added in the future.

    Van Dinh

  • RELEVANCY SCORE 2.77

    DB:2.77:Date Error f7


    hi

    I have oracle 10g db

    I get a error
    "ora 1858 a non-numeric character was found where a numeric was expected "

    when

    select to_date(m.mydate,'yyyymmdd') from mytable

    There are thousand records,mypk column is the primary key

    How can I find the corrupt data ???

    rgds
    s

    DB:2.77:Date Error f7

    WIP wrote:
    All the mydate columns are dateThen why are using TO_DATE ???

  • RELEVANCY SCORE 2.77

    DB:2.77:How To Map C-M-Relationship Field To Table Column? 31



    Hi,Please help!My Database has two tables:"names"- PK (primary key column)- MEMBERS_PK (stores foreign key to "members" - Integer)"members" table with a 1:1 bi-directinal relationship.- PK (primary key column)- NAMES_PK (stores foreign key to "names" - Integer)I can make my Names Entity Bean work with the accessor fields, ejb-jar.xml and jbosscmp-jdbc.xml snipits shown below.Note I can map the "pk" ben field implied by get/set Pk () to the all caps "PK" table column.But I cannot find a way to map a differnt bean field namefor the relationship field associated with the MEMBERS_PK column.I had to use get/setMEMBERS_PK () to make it work.Can anyone tell me what I am missing?I'd really like to:- use get/SetMembersPk () in my bean and- then map "membersPk" to the "MEMBERS_PK" table column.using the cmp-field tag in jbosscmp-jdbc.xml file just gave me eroros or a giant bit blob being stored.
    Names ENTITY BEAN SOURCE CODE

    // abstract relationship fields
    public abstract MemberLocal getMEMBERS_PK ();
    public abstract void setMEMBERS_PK (MemberLocal local);

    // abstract persistence fields
    public abstract Integer getPk ();
    public abstract void setId (Integer newId);

    ejb-jar.xml
    ejb-jar
    enterprise-beans
    entity
    cmp-fieldfield-namepk/field-name/cmp-field
    /entity
    /enterprise-beans
    /ejb-jar

    jbosscmp-jdbc.xml
    jbosscmp-jdbc
    enterprise-beans
    entity
    cmp-field
    field-namepk/field-name
    column-namePK/column-name
    not-null/
    auto-increment/
    /cmp-field
    /entity
    /enterprise-beans
    /jbosscmp-jdbc

    DB:2.77:How To Map C-M-Relationship Field To Table Column? 31


    Hi,Please help!My Database has two tables:"names"- PK (primary key column)- MEMBERS_PK (stores foreign key to "members" - Integer)"members" table with a 1:1 bi-directinal relationship.- PK (primary key column)- NAMES_PK (stores foreign key to "names" - Integer)I can make my Names Entity Bean work with the accessor fields, ejb-jar.xml and jbosscmp-jdbc.xml snipits shown below.Note I can map the "pk" ben field implied by get/set Pk () to the all caps "PK" table column.But I cannot find a way to map a differnt bean field namefor the relationship field associated with the MEMBERS_PK column.I had to use get/setMEMBERS_PK () to make it work.Can anyone tell me what I am missing?I'd really like to:- use get/SetMembersPk () in my bean and- then map "membersPk" to the "MEMBERS_PK" table column.using the cmp-field tag in jbosscmp-jdbc.xml file just gave me eroros or a giant bit blob being stored.
    Names ENTITY BEAN SOURCE CODE

    // abstract relationship fields
    public abstract MemberLocal getMEMBERS_PK ();
    public abstract void setMEMBERS_PK (MemberLocal local);

    // abstract persistence fields
    public abstract Integer getPk ();
    public abstract void setId (Integer newId);

    ejb-jar.xml
    ejb-jar
    enterprise-beans
    entity
    cmp-fieldfield-namepk/field-name/cmp-field
    /entity
    /enterprise-beans
    /ejb-jar

    jbosscmp-jdbc.xml
    jbosscmp-jdbc
    enterprise-beans
    entity
    cmp-field
    field-namepk/field-name
    column-namePK/column-name
    not-null/
    auto-increment/
    /cmp-field
    /entity
    /enterprise-beans
    /jbosscmp-jdbc

  • RELEVANCY SCORE 2.77

    DB:2.77:How Can I Find Which Column Have Primary Key In A Table cp


    hi,

    i want to know which column is a primary key.
    i am using java and mysql.

    thanks with regards
    daya

    DB:2.77:How Can I Find Which Column Have Primary Key In A Table cp

    hi,
    thanks for replying

    i want to know which is primary key in a single
    table.
    not all the tables in DataBase.
    ex: i a student Table,and it have 9 fileds and 3 one
    is
    primary key.

    thanks and regards
    dayahave to look into the API ? you just need to specify the table name in the getPrimaryKeys method.

  • RELEVANCY SCORE 2.76

    DB:2.76:Demantra Tables 78


    Where can I find tables in demantra, the one like etrm for Oracle, which contains the list of table names and columns in the tables? Also which table contains Schema_id column?
    Thanks
    Ramkri

    DB:2.76:Demantra Tables 78

    Ok, I doubt there is any such documentation of tables, I had to figure it out by myself.

    To answer the other question, schema_id column is available in WF_PROCESS_LOG table and WF_SCHEMAS. These tables are related to the Demantra Workflows to capture a log of the runs and schema details resp.

    Let me know if you need any other information.

    Regds,
    Vinay

  • RELEVANCY SCORE 2.76

    DB:2.76:How To Find Primary Key? aj


    I am trying to impliment a load using JDBC wherein I read the table name, some column names, and associated
    column data from a file (delimited or XML). The primary key columns are in the column names / data BUT I don't
    know which columns they are (based on the file I'm reading). Even if this information was in the file, I would need to
    verify the table I'm targeting does in fact have that primary key. I can insert the records fine BUT to use the records
    to update I need to determine from the database which are the primary key columns (by name).

    Essentially I'm trying to do the following:

    {
    insert record into table

    if fails because of duplicate primary key
    {
    determine the primary key of the current table

    retrieve the already existing record

    modify the retrieved record using my load data

    update the already existing record in the database

    }

    }

    My primary concern, now, is Oracle although I'm hoping to impliment this in as portable a fashion as possible.

    Thanks.
    R.Parr
    TemporalArts
    608.833.4578

    null

    DB:2.76:How To Find Primary Key? aj

    I am trying to impliment a load using JDBC wherein I read the table name, some column names, and associated
    column data from a file (delimited or XML). The primary key columns are in the column names / data BUT I don't
    know which columns they are (based on the file I'm reading). Even if this information was in the file, I would need to
    verify the table I'm targeting does in fact have that primary key. I can insert the records fine BUT to use the records
    to update I need to determine from the database which are the primary key columns (by name).

    Essentially I'm trying to do the following:

    {
    insert record into table

    if fails because of duplicate primary key
    {
    determine the primary key of the current table

    retrieve the already existing record

    modify the retrieved record using my load data

    update the already existing record in the database

    }

    }

    My primary concern, now, is Oracle although I'm hoping to impliment this in as portable a fashion as possible.

    Thanks.
    R.Parr
    TemporalArts
    608.833.4578

    null

  • RELEVANCY SCORE 2.76

    DB:2.76:Long Column Field Names In Language File Do Not Relabel 3m



    I have a database view where I'm adding Language file entries to relabel some of the columns.

    I cannot relabel two columns, and my guess is that the column names are too long:

    sw_u_management_port_ip_address

    loc_u_customer_data_center_name

    I wasn't able to find any mention of field name length limitations in the KB, wiki or community.

    Has anyone experienced this and worked around it? The only workaround that I have is to deprecate the columns and create new ones with shorter names.

    DB:2.76:Long Column Field Names In Language File Do Not Relabel 3m


    Yes:
    sw_u_management_port_ip_address
    loc_u_customer_data_center_name
    where sw_ and loc_ are the prefixes of the joined tables. I have other fields from this view that are relabeled correctly.

  • RELEVANCY SCORE 2.76

    DB:2.76:Missing Entries In Primary Key Column k3


    I want to find out the missing entries in column which is primary key column. Please give me the query.

    Thanks

    DB:2.76:Missing Entries In Primary Key Column k3

    Are you referring to missing numbers (holes) in sequence in primary key column?

    If yes, does following thread solve your problem?

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12864646978683

  • RELEVANCY SCORE 2.75

    DB:2.75:Query To Find And Update xs


    hi,

    Can anyone please provide me a query to find out names having " , " commas like servi,ce actually I have a service name column in a table where i need to find names with commas "," and remove by update.

    I appreciate, if anyone provide me query to find those services having commas in between and a separate update statement to remove them

    Thanks in advance for your cooperation

    Regards,
    ahon

    DB:2.75:Query To Find And Update xs

    hello

    used the sql command REPLACE INSTR function

    example :

    Query :
    select service_name
    from YOURTABLE
    where instr(service_name,',') 0 -- This return those with ',' char

    to update :

    update YOURTABLE
    set service_name = replace(service_name,',','') -- to replace the ',' char to null values
    where instr(service_name,',') 0

    But before doing that back up your table or do it in a copy of that..

    Syntax
    -------------------------------------------------------------------------------

    REPLACE ( string_expression , string_pattern , string_replacement )

    Arguments
    -------------------------------------------------------------------------------

    string_expression
    Is the string expression to be searched. string_expression can be of a character or binary data type.
    string_pattern
    Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.
    string_replacement
    Is the replacement string. string_replacement can be of a character or binary data type.

    charles.

    if this find helpful or correct then mark it accordingly

  • RELEVANCY SCORE 2.75

    DB:2.75:Is It Possible To Get The Values Of Certain Columns Based On Datatype ? 18


    Hi,

    Oracle 11 g
    I have the following table

    create table test_table (
    rating number,
    mark number,
    age number,
    names varchar2(500));

    insert into test_table (rating,mark,age,names) values(1,200,20,'Smith');
    insert into test_table (rating,mark,age,names) values(2,200,30,'Blair');
    insert into test_table (rating,mark,age,names) values(3,200,40,'Will');

    I need to find the values of all columns and also the names of columns that have the datatype as number

    I am half way into it.
    select column_name as PROCESS_NAME from user_tab_columns where table_name = 'TEST_TABLE' and DATA_TYPE='NUMBER';

    above query will give me the column names, but not sure how to dynamically assign these column names to the same sql to get the values of those columns,

    result I am looking is
    Rating mark age
    1 200 20
    2 200 30
    3 200 40

    Thanks
    _pete

    DB:2.75:Is It Possible To Get The Values Of Certain Columns Based On Datatype ? 18

    Hi !

    With PlSql something like this is possible

    SQL
    SQL set serveroutput on
    SQL
    SQL DECLARE
    2 v_sql VARCHAR2(4000) := 'select ';
    3 BEGIN
    4 FOR cur IN (SELECT column_name
    5 FROM all_tab_columns
    6 WHERE table_name = 'EMP'
    7 AND data_type = 'NUMBER'
    8 AND owner = 'SCOTT') LOOP
    9 v_sql := v_sql || ' ' || cur.column_name || ',';
    10 END LOOP;
    11
    12 v_sql := rtrim(v_sql, ',') || ' from emp';
    13 dbms_output.put_line(v_sql);
    14 END;
    15 /
    select EMPNO, MGR, SAL, COMM, DEPTNO from emp

    PL/SQL procedure successfully completed.

    SQL After constructing the SQL stmt you can dynamicaly execute it
    I have no real idea how to do this with SQL only

    T

  • RELEVANCY SCORE 2.75

    DB:2.75:Ora-2270 Error While Adding Foreign Key Constraint On Top A View 3k


    While adding a constraint, referencing a foreign key column from a view raises error. However, it works fine over a table.

    Here's the sample script:
    create table t_temp (
    sample_id number,
    text varchar2(40))
    ;

    alter table t_temp add constraint temp_pk
    primary key (sample_id);

    create view tempvw as select sample_id,text from t_temp;

    create table t_sample (
    uniq_id number,
    sample_id number,
    sample_text varchar(40));

    =========
    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp
    FOREIGN KEY (sample_id) REFERENCES t_temp (sample_id);

    This works fine.
    =========

    ==============
    The following raises ERROR....

    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp1
    FOREIGN KEY (sample_id) REFERENCES tempvw (sample_id);

    Error starting at line 1 in command:
    ALTER TABLE t_sample ADD CONSTRAINT FK_sample_temp1
    FOREIGN KEY (sample_id) REFERENCES tempvw (sample_id)
    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    DB:2.75:Ora-2270 Error While Adding Foreign Key Constraint On Top A View 3k

    I didn't know about the limitation on Views. Thanks for your answers!

  • RELEVANCY SCORE 2.75

    DB:2.75:Column Names ≫ 20 Chars Not Handled? xm


    In my portal forms, I'm finding that get_value and set_value aren't working on any table/view columns where the column name is more than 20 characters long. I can't find a listed bug for this - is it a known problem?

    DB:2.75:Column Names ≫ 20 Chars Not Handled? xm

    Dmitry

    No error, just doesn't get the value back. My column name is pat_paymentaccounttype - I use get_value to retrieve this column along with several other base table columns. All the others work, but not this one. If I use a view to change the column name to a shorter name, the get_value works fine.

    Cheryl

  • RELEVANCY SCORE 2.75

    DB:2.75:View Of Tables With Double Column Names a7


    Hello,

    my problem is that in my database schema the name of the primary-key-column in one table has the same name as the corresponding foreign key-column in another table. Now i want to create a view of those two tables and get an error (double column names).

    Is there any chance to avoid this error without rename all my column names (cause this happens many many times)?

    Thanks for any help.
    Christoph

    DB:2.75:View Of Tables With Double Column Names a7

    You can do this way...

    Hint
    select 'a.'||COLUMN_NAME||',' from USER_TAB_COLUMNS where table_name = 'TABLE_1'
    output
    a.c1,
    a.c2,
    a.c3,
    select 'b.'||COLUMN_NAME||',' from USER_TAB_COLUMNS where table_name = 'TABLE_2'

    Later put all the outputs in one file and edit.. and write the join query...

  • RELEVANCY SCORE 2.75

    DB:2.75:Sql Query Differnce df


    HI All ,

    Could you help me the to find difference between the follwoing 2 queries .

    x and y are two tables. a and b are column names respec.
    This is for ex, the table has many coulmns and a and b are primary keys
    and this table has about 60000 rec, each .

    select x.a,y.b from x,y
    where x.a='david'
    and y.b='101';

    select y.b,x.a from x,y
    where y.b='101'
    and x.a='david';

    DB:2.75:Sql Query Differnce df

    what i wanna know is the time / performance of the query if you change the
    coulmn names in where clause It makes no difference to the performance.

  • RELEVANCY SCORE 2.75

    DB:2.75:Enhancements To Cdm Ruleframe kd


    Hi,

    I have two requests/suggestions for improvements in CDMRuleFrame.

    1. I find it hugely annoying when I am coding my rule (l_rule_ok:= .. etc.) that I do not know the exact names of the parameters (arguments) that I have available. I would like the Business Rule Design Transformer to add the names of the available parameters for example as follows:

    /****
    Purpose:
    Remarks:
    Available parameters: p_job, p_salary, p_old_salary

    Revision History:
    ...

    a suggestion for necessary code changes follows later in this post.

    2. Arguments for (surrogate) primary key columns
    We choose not to include the ID attributes in the Entity Definitions, but instead have the Database Design Transformer generate Surrogate Primary Keys. As a consequence, we cannot specify a Function Attribute usage of the Primary Key Attribute, as it does not exist. Now we very frequently need to create an Argument in the BRDD PL/SQL Definition for the Primary Key column.

    It would be convenient if you can specify in the FUNENT Comment property (just like you do for Relationships) that you want to make use of the Primary Key. For example by including a tag such as PK/. The Business Rule Design Transformer should pick up that tag can create the desired Argument based on the Surrogate Primary Key column.

    Again, a suggestion for the implementation follows.

    I am interested in your reaction,

    Lucas Jellema
    AMIS Services BV

    ======================================================
    1. Suggestion for implementing "adding parameter-names to PL/SQL Block Comment in package HSU_CRBR:

    -- add package variable to hold a list of argument-names
    g_arguments varchar2(1000);

    -- add procedures to collect and retrieve argument-names
    procedure reset_arguments
    is
    begin
    g_arguments:='';
    end;

    procedure add_argument( p_argument_name in varchar2)
    is
    begin
    g_arguments := g_arguments||', '||p_argument_name;
    end;

    function get_arguments
    return varchar2
    is
    begin
    return g_arguments;
    end;

    -- in procedure add_arguments, add the following line
    add_argument(l_arg_name);
    just before the line: end loop col;

    -- add optional parameter to procedure add_plsql_block:
    ...
    , p_arguments in varchar2 default ''
    )

    -- also add the following line :
    add('Input parameters available in the validation code: '||p_arguments);

    just below the line: add('Remarks ');

    -- modify procedure maintain_plm:
    -- add the following line just before the begin
    l_arguments varchar2(2000); -- LJ

    -- finally: add the lines marked with ** in the surrounding code

    add_arguments(l_plm_id, p_col_data_required, 'p_');
    ** l_arguments:= substr(get_arguments,2);
    add_arguments(l_plm_id, p_old_col_data_required, 'p_old_');
    ** l_arguments:= l_arguments||substr(get_arguments,2);
    add_plsql_block
    ( l_plm_id
    , l_plm_already_exists
    , p_description
    , p_utility_package
    , p_utility_revision
    , p_validation_code
    , p_br_name
    ** , l_arguments
    );

    ======================================================
    1. Suggestion for implementing "create argument for Surrogate Primary Key" in package HSU_BRTR:

    -- add private constant
    PRIMARY_KEY_TAG constant varchar2(30) := 'PK/';

    -- add cursor to procedure transform_br_fun:
    -- selects primary key required
    cursor c_pk_use
    ( b_fun_id in number
    , b_ent_id in number
    , b_tbl_id in number
    ) is
    select oky.column_reference col_id
    , fent.remark
    , col.name col_name
    from ci_function_entity_usages fent
    , ci_key_components oky
    , ci_primary_key_constraints pco
    , ci_columns col
    where fent.function_reference = b_fun_id
    and fent.entity_reference = b_ent_id
    and instr(fent.remark, PRIMARY_KEY_TAG) != 0
    and oky.constraint_reference = pco.id
    and pco.table_reference = b_tbl_id
    and oky.column_reference = col.id
    and col.source_attribute_reference is null -- only retrieve Surrogate Key Columns that are not available as attribute in the Entity Definition
    ;

    -- add to transform_br_fun (just after "end loop relend;" and before "end loop; -- r_tab_ent" inside the for r_trg_evt in c_trg_evt loop:

    -- added to have Argument created for (surrogate) Primary Key Column not defined as attribute
    for pk_use in c_pk_use
    ( b_fun_id = p_fun_id
    , b_ent_id = l_ent_id, b_tbl_id = r_tab_ent.table_reference
    ) loop
    add_col
    ( p_tbl_id = r_tab_ent.table_reference
    , p_col_id = pk_use.col_id
    , p_trg_or_use = 'U'
    );
    bllog.write
    ('An argument will be created'
    ||' from Business Rule '
    ||r_fun.fun_function_label
    ||' for primary key column '||pk_use.col_name
    , bllog.debug
    );
    end loop; -- pk_use

    DB:2.75:Enhancements To Cdm Ruleframe kd

    Hi,

    I have two requests/suggestions for improvements in CDMRuleFrame.

    1. I find it hugely annoying when I am coding my rule (l_rule_ok:= .. etc.) that I do not know the exact names of the parameters (arguments) that I have available. I would like the Business Rule Design Transformer to add the names of the available parameters for example as follows:

    /****
    Purpose:
    Remarks:
    Available parameters: p_job, p_salary, p_old_salary

    Revision History:
    ...

    a suggestion for necessary code changes follows later in this post.

    2. Arguments for (surrogate) primary key columns
    We choose not to include the ID attributes in the Entity Definitions, but instead have the Database Design Transformer generate Surrogate Primary Keys. As a consequence, we cannot specify a Function Attribute usage of the Primary Key Attribute, as it does not exist. Now we very frequently need to create an Argument in the BRDD PL/SQL Definition for the Primary Key column.

    It would be convenient if you can specify in the FUNENT Comment property (just like you do for Relationships) that you want to make use of the Primary Key. For example by including a tag such as PK/. The Business Rule Design Transformer should pick up that tag can create the desired Argument based on the Surrogate Primary Key column.

    Again, a suggestion for the implementation follows.

    I am interested in your reaction,

    Lucas Jellema
    AMIS Services BV

    ======================================================
    1. Suggestion for implementing "adding parameter-names to PL/SQL Block Comment in package HSU_CRBR:

    -- add package variable to hold a list of argument-names
    g_arguments varchar2(1000);

    -- add procedures to collect and retrieve argument-names
    procedure reset_arguments
    is
    begin
    g_arguments:='';
    end;

    procedure add_argument( p_argument_name in varchar2)
    is
    begin
    g_arguments := g_arguments||', '||p_argument_name;
    end;

    function get_arguments
    return varchar2
    is
    begin
    return g_arguments;
    end;

    -- in procedure add_arguments, add the following line
    add_argument(l_arg_name);
    just before the line: end loop col;

    -- add optional parameter to procedure add_plsql_block:
    ...
    , p_arguments in varchar2 default ''
    )

    -- also add the following line :
    add('Input parameters available in the validation code: '||p_arguments);

    just below the line: add('Remarks ');

    -- modify procedure maintain_plm:
    -- add the following line just before the begin
    l_arguments varchar2(2000); -- LJ

    -- finally: add the lines marked with ** in the surrounding code

    add_arguments(l_plm_id, p_col_data_required, 'p_');
    ** l_arguments:= substr(get_arguments,2);
    add_arguments(l_plm_id, p_old_col_data_required, 'p_old_');
    ** l_arguments:= l_arguments||substr(get_arguments,2);
    add_plsql_block
    ( l_plm_id
    , l_plm_already_exists
    , p_description
    , p_utility_package
    , p_utility_revision
    , p_validation_code
    , p_br_name
    ** , l_arguments
    );

    ======================================================
    1. Suggestion for implementing "create argument for Surrogate Primary Key" in package HSU_BRTR:

    -- add private constant
    PRIMARY_KEY_TAG constant varchar2(30) := 'PK/';

    -- add cursor to procedure transform_br_fun:
    -- selects primary key required
    cursor c_pk_use
    ( b_fun_id in number
    , b_ent_id in number
    , b_tbl_id in number
    ) is
    select oky.column_reference col_id
    , fent.remark
    , col.name col_name
    from ci_function_entity_usages fent
    , ci_key_components oky
    , ci_primary_key_constraints pco
    , ci_columns col
    where fent.function_reference = b_fun_id
    and fent.entity_reference = b_ent_id
    and instr(fent.remark, PRIMARY_KEY_TAG) != 0
    and oky.constraint_reference = pco.id
    and pco.table_reference = b_tbl_id
    and oky.column_reference = col.id
    and col.source_attribute_reference is null -- only retrieve Surrogate Key Columns that are not available as attribute in the Entity Definition
    ;

    -- add to transform_br_fun (just after "end loop relend;" and before "end loop; -- r_tab_ent" inside the for r_trg_evt in c_trg_evt loop:

    -- added to have Argument created for (surrogate) Primary Key Column not defined as attribute
    for pk_use in c_pk_use
    ( b_fun_id = p_fun_id
    , b_ent_id = l_ent_id, b_tbl_id = r_tab_ent.table_reference
    ) loop
    add_col
    ( p_tbl_id = r_tab_ent.table_reference
    , p_col_id = pk_use.col_id
    , p_trg_or_use = 'U'
    );
    bllog.write
    ('An argument will be created'
    ||' from Business Rule '
    ||r_fun.fun_function_label
    ||' for primary key column '||pk_use.col_name
    , bllog.debug
    );
    end loop; -- pk_use

  • RELEVANCY SCORE 2.75

    DB:2.75:Cte Queries Use View Column Names In Sql Statements To Data Source fk



    Hi,

    When my virtual view column names are different than the corresponding columns in the data source, I get errors using CTE queries.

    For example, I have a view:

    CREATE VIEW Task (

    TaskId integer NOT NULL PRIMARY KEY,

    TaskDescription String,

    ...

    )

    AS select

    DB1Task.Id AS TaskId,

    DB1Task.Description AS TaskDescription,

    ...

    As you can see above, the view "TaskId" column maps to the datasource "Id" column. If I run a query such as:

    WITH My_CTE

    AS (SELECT TaskId, TaskDescription, ... FROM Task)

    SELECT ..., COUNT(My_CTE.TaskId) TaskCount

    FROM ...

    I get the following error - where it looks like Teiid is passing "TaskId" to the data source DB instead of "Id":

    Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 UsersDB: 8156 TEIID11008:TEIID11004 Error executing statement(s):

    [Prepared Values: [] SQL: WITH My_CTE AS (SELECT g_0."TaskId", g_0."UserId", g_0."UserId", g_0."ProjectId" FROM "DB1"."dbo"."Task" g_0) SELECT TOP 100 g_0."Name" AS c_0, COUNT(g_1.TaskId) AS c_1 FROM "DB1"."dbo"."Person" g_0, My_CTE g_1 WHERE g_1.UserId = g_0."UserId" GROUP BY g_0."Name"]

    SQLState: TEIID30504

    ErrorCode: 0

    If I use the same column names in the view as they appear in the data source, the query works just fine. Is this a bug or did I miss something?

    Thanks

  • RELEVANCY SCORE 2.74

    DB:2.74:How Can We Find Out The Lookup Table Columns Using Repository Tables ap



    Hi All,

    I have 160 mappings in that we are using one lookup with different names

    I want to know one lookup table column name where is it using in all mappings

    Could you please let me know how can i find it

    Regards,

    LakshmiNarasimha.

    DB:2.74:How Can We Find Out The Lookup Table Columns Using Repository Tables ap


    You have to run this query against your informatica repository DB (so change the INFA_REPO2 to yours DB name). You can use toad for this.

  • RELEVANCY SCORE 2.74

    DB:2.74:Changing Column Names 78


    Hi all,

    I have a JTable where I set the column names as...

    String[] columnNames = {"Column 1", "Column 2"};and what i have been tring to do is, if the user clicks on a button i want to change the column Names to say "New Column 1" and "New Column 2"

    all help is much appreciated.

    DB:2.74:Changing Column Names 78

    JTableHeader th = table.getTableHeader();
    TableColumnModel tcm = th.getColumnModel();
    TableColumn tc = tcm.getColumn( int ); // the column to change
    tc.setHeaderValue( "New Value" );
    th.repaint();

  • RELEVANCY SCORE 2.73

    DB:2.73:Find Out The Table Names And Column Names fd


    Hi Dev's

    My requirement is to find out the all table names and columns names of whose datatype is 'DATE'.

    Here is the details :

    Schema name : USER1
    CREATE TABLE emp_1(empno NUMBER,ename VARCHAR2(30),hiredate DATE);

    CREATE TABLE emp_2(eid NUMBER,ename VARCHAR2(30),Joindate DATE);here i am creating views on the above tables.

    CREATE VIEW v_emp_1 AS SELECT empno,hiredate FROM emp_1;

    CREATE VIEW v_emp_2 AS SELECT ename,joindate FROM emp_2;Now i am try to finding out the all table names and columns names of whose datatype is 'DATE' under USER1 schema.

    SELECT table_name,
    column_name
    FROM user_tab_columns
    WHERE data_type='DATE'
    ORDER BY table_name,
    column_name;OUTPUT :
    V_EMP_1 HIREDATE
    V_EMP_2 JOINDATE
    EMP_1F HIREDATE
    EMP_2F JOINDATE In the above output it showing table names,columnname and view names,view column names.

    But what i need is only tablenames and according column names.

    Note: my schema having more than 1000 tables and more than 100 views are existed.

    Please help me on this issue...

    Thanks,

    DB:2.73:Find Out The Table Names And Column Names fd

    Hi All,

    This may helps you to find out

    select a.table_name, B.COLUMN_NAME from USER_TABLES a, user_tab_columns b where A.TABLE_NAME=B.TABLE_NAME and B.DATA_TYPE = 'DATE'Thanks
    Sami

  • RELEVANCY SCORE 2.73

    DB:2.73:Masking Column Names 7p


    Hi,

    Can anyone let me know how to mask column names in model?

    Problem: There are columns with not so user friendly names like Attribute1, so I need to mask that name in my model and give a more user friendly name which describes its functionality.
    Just like how we have a schema in BPEL where we can give custom names and mask the actual column names.

    Thanks,
    Vikram

    DB:2.73:Masking Column Names 7p

    Hi Vikram,

    You can give a description of the column name from ODI .

    In designer --- model--- datastore name ---- column name

    You can give a description the column name ie. For DEPTNO to Department_Number .
    In this way you can give a meaningful name to any column.

    Thanks,
    Sutirtha

  • RELEVANCY SCORE 2.73

    DB:2.73:Re: A Syntax Problem With An Insert Sql Request 7f


    I use a class which automate JDBC connection, executeQuery, update...

    And to create the obeject test, I use this line :
    JDBCAdapter test = new JDBCAdapter("jdbc:odbc:ElectricSGBD","sun.jdbc.odbc.JdbcOdbcDriver","","");

    --- Class JDBC ---------------------
    import java.util.Vector;
    import java.sql.*;
    import com.sun.java.swing.table.AbstractTableModel;
    import com.sun.java.swing.event.TableModelEvent;

    public class JDBCAdapter extends AbstractTableModel {
    Connection connection;
    Statement statement;
    ResultSet resultSet;
    String[] columnNames = {};
    Vector rows = new Vector();
    ResultSetMetaData metaData;

    public JDBCAdapter(String url, String driverName,
    String user, String passwd) {
    try {
    Class.forName(driverName);
    System.out.println("Ouverture de la connexion a la base de donnee...");

    connection = DriverManager.getConnection(url, user, passwd);
    statement = connection.createStatement();
    }
    catch (ClassNotFoundException ex) {
    System.err.println("Cannot find the database driver classes.");
    System.err.println(ex);
    }
    catch (SQLException ex) {
    System.err.println("Cannot connect to this database.");
    System.err.println(ex);
    }
    }

    public void executeQuery(String query) {
    if (connection == null || statement == null) {
    System.err.println("There is no database to execute the query.");
    return;
    }
    try {
    resultSet = statement.executeQuery(query);
    metaData = resultSet.getMetaData();

    int numberOfColumns = metaData.getColumnCount();
    columnNames = new String[numberOfColumns];
    // Get the column names and cache them.
    // Then we can close the connection.
    for(int column = 0; column numberOfColumns; column++) {
    columnNames[column] = metaData.getColumnLabel(column+1);
    }

    // Get all rows.
    rows = new Vector();
    while (resultSet.next()) {
    Vector newRow = new Vector();
    for (int i = 1; i = getColumnCount(); i++) {
    newRow.addElement(resultSet.getObject(i));
    }
    rows.addElement(newRow);
    }
    // close(); Need to copy the metaData, bug in jdbc:odbc driver.
    fireTableChanged(null); // Tell the listeners a new table has arrived.
    }
    catch (SQLException ex) {
    System.err.println(ex+" query = "+query);
    }
    }

    public void executeUpdate(String query) {
    if (connection == null || statement == null) {
    System.err.println("There is no database to execute the query.");
    return;
    }
    try {
    statement.executeUpdate(query);
    // close(); Need to copy the metaData, bug in jdbc:odbc driver.
    fireTableChanged(null); // Tell the listeners a new table has arrived.
    }
    catch (SQLException ex) {
    System.err.println(ex+" query = "+query);
    }
    }

    public void close() throws SQLException {
    System.out.println("Fermeture de la connection a la base de donnee... Bye !");
    resultSet.close();
    statement.close();
    connection.close();
    }

    protected void finalize() throws Throwable {
    close();
    super.finalize();
    }

    //////////////////////////////////////////////////////////////////////////
    //
    // Implementation of the TableModel Interface
    //
    //////////////////////////////////////////////////////////////////////////

    // MetaData

    public String getColumnName(int column) {
    if (columnNames[column] != null) {
    return columnNames[column];
    } else {
    return "";
    }
    }

    public Class getColumnClass(int column) {
    int type;
    try {
    type = metaData.getColumnType(column+1);
    }
    catch (SQLException e) {
    return super.getColumnClass(column);
    }

    switch(type) {
    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGVARCHAR:
    return String.class;

    case Types.BIT:
    return Boolean.class;

    case Types.TINYINT:
    case Types.SMALLINT:
    case Types.INTEGER:
    return Integer.class;

    case Types.BIGINT:
    return Long.class;

    case Types.FLOAT:
    case Types.DOUBLE:
    return Double.class;

    case Types.DATE:
    return java.sql.Date.class;

    default:
    return Object.class;
    }
    }

    public boolean isCellEditable(int row, int column) {
    try {
    return metaData.isWritable(column+1);
    }
    catch (SQLException e) {
    return false;
    }
    }

    public int getColumnCount() {
    return columnNames.length;
    }

    // Data methods

    public int getRowCount() {
    return rows.size();
    }

    public Object getValueAt(int aRow, int aColumn) {
    Vector row = (Vector)rows.elementAt(aRow);
    return row.elementAt(aColumn);
    }

    public String dbRepresentation(int column, Object value) {
    int type;

    if (value == null) {
    return "null";
    }

    try {
    type = metaData.getColumnType(column+1);
    }
    catch (SQLException e) {
    return value.toString();
    }

    switch(type) {
    case Types.INTEGER:
    case Types.DOUBLE:
    case Types.FLOAT:
    return value.toString();
    case Types.BIT:
    return ((Boolean)value).booleanValue() ? "1" : "0";
    case Types.DATE:
    return value.toString(); // This will need some conversion.
    default:
    return "\""+value.toString()+"\"";
    }

    }

    public void setValueAt(Object value, int row, int column) {
    try {
    String tableName = metaData.getTableName(column+1);
    // Some of the drivers seem buggy, tableName should not be null.
    if (tableName == null) {
    System.out.println("Table name returned null.");
    }
    String columnName = getColumnName(column);
    String query =
    "update "+tableName+
    " set "+columnName+" = "+dbRepresentation(column, value)+
    " where ";
    // We don't have a model of the schema so we don't know the
    // primary keys or which columns to lock on. To demonstrate
    // that editing is possible, we'll just lock on everything.
    for(int col = 0; colgetColumnCount(); col++) {
    String colName = getColumnName(col);
    if (colName.equals("")) {
    continue;
    }
    if (col != 0) {
    query = query + " and ";
    }
    query = query + colName +" = "+
    dbRepresentation(col, getValueAt(row, col));
    }
    System.out.println(query);
    System.out.println("Not sending update to database");
    // statement.executeQuery(query);
    }
    catch (SQLException e) {
    // e.printStackTrace();
    System.err.println("Update failed");
    }
    Vector dataRow = (Vector)rows.elementAt(row);
    dataRow.setElementAt(value, column);

    }
    }

  • RELEVANCY SCORE 2.73

    DB:2.73:Using A Primary Key Column Variable In Select Statement 91


    Hello there,
    I have a project where I am supposed to retrieve the primary key columns of a given table in a given database, server, Datawarehouse layer. All these are input parameters that are passed to the Adm.dbo.udf_Primary_Key_Column to return StudentId
    which I am supposed to use in the Select statement to retrieve the actual values.
    I declared a variable @pstring of type varchar(128) and assigned it the Contenation of Column names that are contentanated by the primary key function. this functiondetermines the primarykey column where Ind_Pk is 1.
    In this case, I have one Column name returned. Student_Id and this column is declared with the datatype bigint in the Students table

    Student_Id will be assigned to the variable @pkstring and then beused in the query.

    When I execute the query, the only records I see are multiple string Studentid which is not right.
    DECLARE @pkSTRING varchar(128);
    SET @pkSTRING = (SELECT DISTINCT
    Adm.dbo.udf_Primary_Key_Column(ColTable) AS PKColumns
    FROM ETL.dbo.uvw_Ctrl_Column
    WHERE ColTable = 'StudentUsers' AND ColSchema = 'CPN' and ColDatabase =
    'PS' AND ColDWLayer = 'PS');
    --select @pkSTRING;
    SELECT
    @pkSTRING
    from PS.cpn.StudentUsers

    I need to be able see some values from this column.
    How do i achieve this?
    Thanks,
    Ion

    DB:2.73:Using A Primary Key Column Variable In Select Statement 91


    Craze
    Thank you so much. It worked out for me.
    Best Regards,
    ION

  • RELEVANCY SCORE 2.73

    DB:2.73:Alteration Of Column Which Is Primary Key 1c


    Hi,
    I have a requirement where i need to alter the lenght of the column which is Primary key. This column is being referred as foreign key inmany tables.
    What will be best approach to alter the primary keyalong with the references.
    Regards,
    Deepali

    DB:2.73:Alteration Of Column Which Is Primary Key 1c

    You cannot alter a column used as a primary key. You will need to drop the PK, in addition the FKs in order to change the column.

  • RELEVANCY SCORE 2.73

    DB:2.73:Project Server Forum? cd


    Where is the best place to ask questions for Project Server?
    I need to find a resource that maps screen names with actual database tables and fields. For example, on the screen in Project Server, you see a column for FUNDING SOURCE. What table and field does that map to?
    Thaks

    DB:2.73:Project Server Forum? cd

    http://social.technet.microsoft.com/Forums/en-US/category/projectserver2010
    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Visual C MVP

  • RELEVANCY SCORE 2.73

    DB:2.73:Problem With Alias sf


    I've two tables.

    Table One contains 3 columns A,B,C where A is the Primary key.
    Table Two contains A,D,E where A is the foreign key.

    Select * from One,Two where One.A=Two.A

    So when I create a custom folder using above SQL ,it would say it is Not Valid. Since the column A is present in both the tables. In my case Both table contains 30columsn each, I want to know is there anyway other than using alias to tell the column names.

    DB:2.73:Problem With Alias sf

    Thanks for the suggestions. I thinnk for now I need to be specific on what fields I need. Its a small report with 4 fields and we are trying to reduce views as much as possible. For now Custom folder should solve our problem.

  • RELEVANCY SCORE 2.73

    DB:2.73:About Id Property In Repository df


    I have seen following definition of id property in docs -In order to obtain the data of a repository item from a datastore, you must supply the primary tables ID. The repository item descriptor can map to the tables ID column implicitly through the table tag attribute id-column-names; or it can explicitly map to the ID column through a property tag that is set as follows:property name="id" column-name="table-id-column"/For example:item-descriptor name="user" table name="user" type="primary" id-column-names="emp_id" property name="id" column-name="product_id"/ /table /item-descriptorI have a little confusion here. In property column name is product_id but the primary key is emp_id. How can we query on id property?Thanks

    DB:2.73:About Id Property In Repository df

    I also think that this probably may be some typo in the documentation as I found it mentioned this way in 9.4 documentation but if you refer the same in 10+ documentation then there is "emp_id" only and no "product_id".Yes. That's why I got confused and was thinking whether this is correct or not.

  • RELEVANCY SCORE 2.72

    DB:2.72:Eureka List kj



    We have an issue where column names in Eureka don't wrap as I believe they did in Dublin.

    Does anyone know a way I can retain the field description and shorten it in list view or get the text to wrap?

    I've tried searching but can't find it.

    TIA

    DB:2.72:Eureka List kj


    We have an issue where column names in Eureka don't wrap as I believe they did in Dublin.

    Does anyone know a way I can retain the field description and shorten it in list view or get the text to wrap?

    I've tried searching but can't find it.

    TIA

  • RELEVANCY SCORE 2.72

    DB:2.72:Case Insensitive Searches: How Is The Datawindow.Table.Select String Put Together? 8f



    I am using PowerBuilder 12.5. I am maintaining PowerBuilder programs written years ago.

    I have a datawindow where the user enters search criteria for queries. Right now we are using a Sybase database and for columns of type char, the user's string is transformed inside itemchanged() such that:

    User's String == Transformation

    ------------------- ----------------------------------

    farm like %[Ff][Aa][Rr][Mm]%

    The result is a case insensitive search. We are migrating to an Oracle database which doesn't recognize [ ].

    I could easily transform 'farm' to 'like %farm%' but I don't know how to wrap the column name inside the lower function. In other words, the datawindow.table.select looks like this (Table = Clients, Column = name):

    SELECT ... WHERE Clients.name like '%farm%'

    but I want it to look like:

    SELECT ... WHERE lower(Clients.name) like '%farm%'

    I don't appear to have a handle on the column name, just what appears after.

    datawindow.table.select is auto generated every time either accepttext() or a user's click (focus lost) puts data into the Primary Buffer.

    Sometimes the queries get rather long with several subqueries that use other tables. I would rather not try to do string manipulation with PosA() and ReplaceA() to "find" column names of a particular type and wrap them with a call to lower().

    Where in the PowerBuilder code is the datawindow.table.select string put together? There appears to be a good deal of parsing going on there looking for keywords such as 'and', 'like', '=', ... Maybe I can insert a call to lower() there????

    Or does someone have a better idea on how to do case insensitive searches using a datawindow?

    DB:2.72:Case Insensitive Searches: How Is The Datawindow.Table.Select String Put Together? 8f


    Hi Christine,

    I know you didn't want to do a lot of parsing, but this might be your best bet:

    Assuming that the WHEREs you grab always follow this format:

    - the LIKE clause is surrounded by ()

    - the expression to the right of the LIKE always contains each character in UPPER and lower case surrounded by []

    This is what you need to do:

    Create a function that receives the where clause as an argument and transform it into a non-case sensitive clause.

    Your function will receive this:
    WHERE (((dbo.Species.commoname like '%[Rr][Yy][Ee]%')))
    and will return this:
    WHERE (((UPPER(dbo.Species.commoname) like '%RYE%')))

    Here is the function code:

    //**************************************************************************

    // Name: of_non_case_sensitive

    //

    // Purpose: Transform a SYBASE specific LIKE clause into a

    // generic non sensitive SQL LIKE clause

    //

    // Argument Type Pass By Description

    // --------- ---------- --------- ----------------------------

    // as_clause string value SYBASE specific LIKE clause

    //

    // Return Type Description

    // ----------- -------------------------------------------------------

    // string Generic non sensitive SQL LIKE clause

    //**************************************************************************

    long ll_endpos = 0, ll_begpos = 0, ll_likepos

    ll_likepos = POS(UPPER(as_clause), 'LIKE')

    IF ll_likepos 0 THEN

    DO

    ll_endpos = POS(as_clause, ']')

    IF ll_endpos 0 THEN

    as_clause = LEFT(as_clause, ll_endpos - 2) + MID(as_clause, ll_endpos + 1)

    END IF

    LOOP WHILE ll_endpos 0

    DO

    ll_endpos = POS(as_clause, '[')

    IF ll_endpos 0 THEN

    as_clause = LEFT(as_clause, ll_endpos - 1) + MID(as_clause, ll_endpos + 1)

    END IF

    LOOP WHILE ll_endpos 0

    ll_endpos = ll_likepos - 2

    ll_begpos = ll_endpos

    DO

    ll_begpos --

    LOOP WHILE MID(as_clause, ll_begpos, 1) '('

    as_clause = LEFT(as_clause, ll_begpos) + 'UPPER(' + MID(as_clause, ll_begpos + 1, ll_endpos - ll_begpos) + ')' + MID(as_clause, ll_endpos + 1)

    END IF

    RETURN as_clause

    Now you just need to call this function to transform each one of the WHEREs you grabbed.

    HTH,

    Manuel.

  • RELEVANCY SCORE 2.71

    DB:2.71:Adding Custom Property In Catalog 11


    Hi all,

    trying to add a new property in the product and SKU level. I have added the below code in custom catalog.xml.

    And the below SQL script, i have got by invoking the following like in . In dyn/admin , atg/commerce/catalog/productcatalog , generate SQL link...

    but i am getting the following error when executing the SQL script.

    Pls let me know the best way to add a custom property in catalog, n generate SQL for same
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    item-descriptor name="product" id-space-name="product" xml-combine="append"
    table name="abc_product" type="auxiliary" id-column-name="product_id"
    property name="isABC" data-type="boolean" default="false" column-name="abc"
    category-resource="SIM" display-name-resource="abc"
    attribute name="propertySortPriority" value="-1"/
    /property
    /table

    /item-descriptor

    -------------------------------------------------------Error when executing script----------------------

    Error starting at line 1 in command:
    CREATE TABLE abc_product (
    product_id varchar2(254)NOT NULL REFERENCES dcs_product(product_id),
    abc number(1)NULL,
    CHECK (abc IN (0, 1)),
    PRIMARY KEY(product_id)
    )
    Error at Command Line:2 Column:61
    Error report:
    SQL Error: ORA-02270: no matching unique or primary key for this column-list
    02270. 00000 - "no matching unique or primary key for this column-list"
    *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement
    gives a column-list for which there is no matching unique or primary
    key constraint in the referenced table.
    *Action: Find the correct column names using the ALL_CONS_COLUMNS
    catalog view

    DB:2.71:Adding Custom Property In Catalog 11

    Bravo

    Your are confusing me a bit here.
    First of all, i m not sure why all primary key constraints on your non versioned schema are not there. This is a problem. Maybe with the way you generated your schema. You need to look back once.

    Second i m not sure why you are trying to create dcs_product table in publishing schema. its OOB table??

  • RELEVANCY SCORE 2.71

    DB:2.71:Change Name In Recipients' "From Column" f3



    I use the comcast siteto send and receivemy email. I have 2 email accounts, one for friends, the other (primary), for more official stuff. In the "primary" acc't,I want my real name to show in a recipient's Inbox "From" column but it displays my nickname. I can't find the nicknamein either acc'tin the comcast email management or address book. Where is the software picking up the nickname; where can I change it?







    Solved
    Go to solution

  • RELEVANCY SCORE 2.71

    DB:2.71:Retrieving Column Names Using Sql Query xk



    hi All
    I have A database in access2007
    I want retrieve column names with sql query in my project
    I write below code:
    select column_name from information_schema.columns where table_name ='table 1' ORDER BY ORDINAL_POSITION
    but display error :
    Could not find file 'D:\Projects\Tapco_ECM\Tapco_ECM\Tapco_ECM\bin\Debug\information_schema.mdb'.
    plz help me

    DB:2.71:Retrieving Column Names Using Sql Query xk

    Looks like it's not as easy in Access as in SQL Server as there are no meta tables as we have in SQL Server holding this info.
    I found this threadhttp://forums.asp.net/t/1143918.aspxshowing how to do this from ASP.NET code, which may be of help. I found also a few links showing how to do this in classical ASP.
    (One extra linkhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64553- not much help here either, though).

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

  • RELEVANCY SCORE 2.71

    DB:2.71:To Find Field Names From One Table And Apply Where Clause On Another Table m9


    Hi
    I have the following scenario
    1. Table Definition: CRITERIA TABLE
    Column Name Description
    CRID Primary Key
    FIELD1 char
    FIELD2 char
    FIELD3 char
    2. Table Definition: FIELD DEFINITION

    Column Name Description
    CRIDForeign key from CRITERIA TABLE
    FIELDNAMEchar
    FIELDValuechar
    The above two table has recods in following manner
    CRITERIA TABLE
    CRID FIELD1 FIELD2
    __________________________________
    CR1 803

    CR2 123 XYZ
    CR3 ABC

    2. FIELD DEFINITION

    CRID FIELDNAME FIELD VALUE
    _________________________________________
    CR1 FIELD1 BICCODE
    CR2 FIELD1 BICCODE
    CR2 FIELD2 BRANCHCODE
    CR3 FIELD2 BRANCHCODE

    If I query the database I have to get the result in the following way
    CRIDBICCODEBRANCHCODE
    __________________________________
    CR1 803

    CR2 123 XYZ
    CR3 ABC
    Can anyone help me to derive a sql query to get the result in the above explained way.

    Regards
    Smith

  • RELEVANCY SCORE 2.71

    DB:2.71:Getting Column Attributes - Substitution Api ks


    Dear All,

    I have a situation where I need to retrieve the column datatype, precision etc from a column name.

    I have the column names of joining columns of source tables extracted with the getJoin() method and Now I need to extract the metadata information of these columns. Is there anyway I can get this info from ODI using Substitution API or do I need to retrieve them from the work repository schema ? My primary objective is getting them via Substitution API or else I will extract them from the database.

    Please help here

    regards
    Atish

    DB:2.71:Getting Column Attributes - Substitution Api ks

    Dear Sutirtha,

    My apologies I used Java Beanshell..well Groovy is also usable

    I cannot reveal the exact code as I am not allowed to do that in office :) .. I Hope you understand that..
    I will share the pseudo logic so that anyone can do it .. It was easier for me since I could write it in Java using a procedural approach

    ?

    import java.util.*;
    import java.sql.*;

    String interfaceId = "%=odiRef.getPop( "I_POP" )%" ;

    // Created an Option alias Name . I Used this option to get the columns of the particular table involved in joining . in fact say SRC is the alias. I would use all table aliases as SRC1, SRC2 etc for source tables so that I can get their columns // // which were involved in joins.

    String alias_name = "*%=odiRef.getOption( "ALIAS_NAME" )%*";

    // Get the Join columns

    String join_cond =*%=odiRef.getQuotedString( odiRef.getJoin( ) )%*; /* this will give you a string separated by newlines. like src.col1 = t.col2 \n AND src2.col3 = t2.col4 etc */

    /* Now I separated this complex string into single join strings like src.col1 = t.col2 */

    here looped for each line

    // Now for each join line I sliced out two tokens based on the operator i.e. "=" in this case.. I have included logic so that I can handle any operator here

    // Since I have two tokens now .i.e.src.col1 t.col2 , I extracted the table aliases from them namely "src" and "t" and also columns namely "col1" and "col2" ..Stored all the distinct column names that have a table alias LIKE my variable //"alias_name" into a Hashmap collection along with the table alias for future reference*/

    loop ends here

    // Now I obtained the JDBC Connection to Work rep..
    Connection workRepoConnection = odiRef.getJDBCConnection("WORKREP");

    Ran a loop here for all items in My Hashmap

    // Using this connection I queried my workrep to find the join conditions for my interface using my variable "interfaceId", as will be able to find out the real table names behind the aliases and also the data type Information for the columns..In this SQL query I kept interfaceId, table alias name( item of the collection) and column names as variables( item of the collection) .
    // I got my required column names with source tables and their data type information.

    Loop ends here

    ?

    Please tell me if you need the query..Hope you find it useful

    Edited by: 922636 on Sep 13, 2012 2:57 AM

    Edited by: 922636 on Sep 13, 2012 2:57 AM

    Edited by: 922636 on Sep 13, 2012 2:58 AM

  • RELEVANCY SCORE 2.71

    DB:2.71:Table Box - Change Column Names da



    Is it possible to change the column names in a table box?

    Example: I have a table where the column names are CAPA.CAPA No, CAPA.Date Ope, etc. I want them to display without the CAPA prefix.

    DB:2.71:Table Box - Change Column Names da


    No problem. If possible I would suggest renaming the fields in the script to make it a little more straightforward.

  • RELEVANCY SCORE 2.71

    DB:2.71:Query The Columns From All Tables 79


    Hi ,
    I have tables called A,B,C........upto Z. Out of these 26 tables, 15 tables have the common column name "AAA". Now I want to find the names of the tables in which there is a column name "AAA". for example if this "AAA" column name is present in tables B,F,G,......Y(15 tables) . The query should display all the table names. Here in this example we donot know in which table the column name "AAA" is. We need to find the tables where the column name "AAA" is?

    Thanks in advance....

    Let me know If you have any confusion with my question.

    DB:2.71:Query The Columns From All Tables 79

    Here is an example:
    -- CREATE TABLES a...z
    DECLARE
    colname varchar2(20);
    BEGIN
    for tabname in (
    SELECT CHR( ASCii('A') + LEVEL - 1) name
    FROM DUAL
    CONNECT BY LEVEL = ASCii('Z') - ASCii('A') + 1
    )
    loop
    IF dbms_randoM.VALUE(1,10) 5
    THEN
    colname := 'AAA';
    ELSE
    colname := 'BBB';
    END IF;
    BEGIN
    EXECUTE IMMEDIATE ' DROP TABLE ' || TABNAME.NAME ;
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;
    EXECUTE IMMEDIATE ' CREATE TABLE ' || TABNAME.NAME ||
    ' ( ' || COLNAME || ' NUMBER )';
    END loop;
    END;
    /

    -- query
    SELECT TABLE_NAME
    FROM user_tab_columns
    where column_name = 'AAA';

    TABLE_NAME
    ------------------------------
    A
    E
    G
    I
    K
    L
    O
    Q
    R
    T
    V
    W
    X

    13 rows selected

  • RELEVANCY SCORE 2.71

    DB:2.71:Link Primary Key To Constraint s3


    I created the following table, index and primary key constraint:
    SQL create table A(f1 char(1), f2 number);

    Table created.

    SQL create unique index U_A_1 on A(f1);

    Index created.

    SQL alter table A add (constraint PK_A primary key(f1) using index);

    Table altered.

    When I query the dictionary tables I get:

    SQL select index_name
    2 from user_indexes where table_name = 'A';

    INDEX_NAME
    ------------------------------
    U_A_1

    SQL select constraint_name from user_constraints
    2 where table_name = 'A'
    3 and constraint_type = 'P';

    CONSTRAINT_NAME
    ------------------------------
    PK_A

    Is there a dictionary table that links the index name to the primary key constraint name other than joining user_cons_columns and user_ind_columns for the same table name and column names??

    Thanks

    DB:2.71:Link Primary Key To Constraint s3

    Glad to help. Actually reading my reply I should have said sys.cdef$.type# IN (2, 3) for primary key and unique key respectively but in any case it looks like you get the gist.

    Padders

  • RELEVANCY SCORE 2.70

    DB:2.70:Find Array E.G. F01 For Updatable Report Columns To Process The Column? k7


    I have an updatable report and I want Process one of the updatable column. In OTN I find, that there are arrays with names like f01, f02 for each updatable column and that I can refernce them with e.g. wwv_flow.g_f01. Where can I find the name of the array that belongs to one report column in my application.

    DB:2.70:Find Array E.G. F01 For Updatable Report Columns To Process The Column? k7

    user514182 -

    Take a look at this How-to from Oracle:
    http://www.oracle.com/technology/products/database/application_express/howtos/tabular_form.html

    It actually explains with examples, most (if not all) of your questions.

    You already have the name of the array (f01, f02, etc). Those are the columns in your report, in order (usually). You create them (with a manually created PL/SQL report as htmldb_item syntax. You reference them for validations or processing as htmldb_application.g_f01(i), etc. syntax.

    Also, searching this forum will return a lot similar questions and answers.

    Hope this helps.

    Bill Ferguson

  • RELEVANCY SCORE 2.70

    DB:2.70:Getcollist To Return Fields Enclosed In Double Quote? p3


    hi.. newbie question.. I searched but couldnt find an answer..

    I have a connection to progress tables, where the tablenames column names contain hyphen (ie: "gla-account"."account-no")..

    the getColList from LKM failed to enclosed the column names in double quotes, I tried inserting \" into the getColList codes but it kept crashing.. what's the correct syntax to add double quotes ?

    thanks..
    HT

  • RELEVANCY SCORE 2.70

    DB:2.70:Case Sensitive Oracle Column Names mm


    I am trying to map an Entity Bean into an existing table. The columns in this table are case sensitive.

    If I specify @Column(name=dispName) on the getter of the property, I get the following error:

    INFO [STDOUT] Hibernate:
    select
    affiliatio0.affiliateId as affiliat11,
    affiliatio0.addressId as addressId1,
    affiliatio0.DESCRIPTION as DESCRIPT31,
    affiliatio0.dispName as dispName1
    from
    WCMTAFFILIATION affiliatio0
    15:58:16,968 WARN [JDBCExceptionReporter] SQL Error: 904, SQLState: 42000

    15:58:16,968 ERROR [JDBCExceptionReporter] ORA-00904: "AFFILIATIO0_"."DISPNAME": invalid identifier

    couldn't find where to set the strategy to treat column names case sensitive (I thought this happens automatically if the name is specified)

    DB:2.70:Case Sensitive Oracle Column Names mm

    thank you. searched the hibernate forums. Answer:

    use backticks as in @column(name="`addressId`")

    works

  • RELEVANCY SCORE 2.70

    DB:2.70:Renaming Of Table Column Names In Adf kc


    Hi , I have one table called NET_TRANSACTIONS (NET_TRANSACTIONS_ID as PK),This table and provided column name is tightly used in our programming and in all jdeveloper generated page bindings as well. Now due to some reasons we needed to rename the table name to PO_TRANSACTIONS(PO_TRANSACTIONS_ID as pk), since previous primary column key is tightly used in many of our screens when we rename the column application stops working, when we refactor the column name in adf it does not update all references of previous column and tables... Can you please guide me how we can get over this problem...i.e. is there any way in Jdeveloper where we change column name in Entity object and all related references are updated automatically... Thanks

    DB:2.70:Renaming Of Table Column Names In Adf kc

    Hi , I have one table called NET_TRANSACTIONS (NET_TRANSACTIONS_ID as PK),This table and provided column name is tightly used in our programming and in all jdeveloper generated page bindings as well. Now due to some reasons we needed to rename the table name to PO_TRANSACTIONS(PO_TRANSACTIONS_ID as pk), since previous primary column key is tightly used in many of our screens when we rename the column application stops working, when we refactor the column name in adf it does not update all references of previous column and tables... Can you please guide me how we can get over this problem...i.e. is there any way in Jdeveloper where we change column name in Entity object and all related references are updated automatically... Thanks

  • RELEVANCY SCORE 2.70

    DB:2.70:Retreiving Data From Database With Query k1


    i am trying to write a query to fill a string array (names) with all the names from the column (first_name) of the table (contacts) where id is the primary key. I took this code from a form i had previously written where it was working fine and moved
    it to a new form where it now returns nothing. Or is there some easier way to return the data from a column into an array where each index is a a different row?

    Dim listName = From name In Me._Database1_02DataSet.Contacts
    Where name.ID 0
    Select name.First_Name

    For Each same In listName
    ReDim Preserve names(0 To UBound(names) + 1)
    names(UBound(names)) = same
    Next

    DB:2.70:Retreiving Data From Database With Query k1

    have you passed the table to the new form? You can do so by passing it to the Form's constructor when you instantiate the Form
    see Deborah's blog:
    http://msmvps.com/blogs/deborahk/archive/2009/09/21/passing-data-between-forms-constructor.aspx
    you can pass any object; the example shows a String, but a DataTable works just fine
    and you might want to use a List(Of T) instead of an array. Easier to work with, plus you don't have to worry about the ReDim stuff.

  • RELEVANCY SCORE 2.70

    DB:2.70:Enumerating Column Information From An Entity Framework Class z3


    Hi,I have an entity framework model of an sql database and I would like to programmatically (vb.net) list all columns and the column spec for each tables. I started with the code below butI am not sure if I am on the right track, also I cannotdetect'auto-number' columns and cannot find where the foreign key column names are stored. Dim s As String Dim spk As String Dim pge As PangeaEntities = New PangeaEntities Dim mdw As Data.Metadata.Edm.MetadataWorkspace = pge.MetadataWorkspace For Each entities In mdw.GetItemCollection(Data.Metadata.Edm.DataSpace.CSpace) If entities.BuiltInTypeKind = Metadata.Edm.BuiltInTypeKind.EntityType Then Dim entity As System.Data.Metadata.Edm.EntityType = DirectCast(entities, System.Data.Metadata.Edm.EntityType) 'Primary Keysmember spk = For Each pk In entity.KeyMembers spk = spk vbCrLf PK: pk.Name Next 'Coumns s = For Each member In entity.Members If member.BuiltInTypeKind = Metadata.Edm.BuiltInTypeKind.EdmProperty Then 'DB Column s = vbCrLf member.Name 'Datatype s = |Datatype= member.TypeUsage.EdmType.Name 'Column settings For Each facet In member.TypeUsage.Facets s = | facet.Name = facet.Value Next ElseIf member.BuiltInTypeKind = Metadata.Edm.BuiltInTypeKind.NavigationProperty Then 'Foreign key or other Else 'Not sure what else we can get End If Next MsgBox(entity.Name vbCrLf spk vbCrLf s) End If Next

    DB:2.70:Enumerating Column Information From An Entity Framework Class z3

    I tweaked some code I found on the net to read the info directly from the edmx file as below, unfortunately for me I could not find any indexinformation in the edmx so I can't use this to re-create my database. This is the code: Private Function ExtractEDMXContent(ByVal edmxFile As String, ByVal sModel As String) As XElement 'sModel: 'ConceptualModels 'StorageModels 'Mappings (C-S Mappings) Dim csdlContent As XElement Dim edmxns As XNamespace = http://schemas.microsoft.com/ado/2007/06/edmx Dim edmns As XNamespace = http://schemas.microsoft.com/ado/2006/04/edm Dim ssdl As XNamespace = http://schemas.microsoft.com/ado/2006/04/edm/ssdl Dim cs As XNamespace = urn:schemas-microsoft-com:windows:storage:mapping:CS Dim edmxDoc As XDocument = XDocument.Load(edmxFile) If (Not edmxDoc Is Nothing) Then Dim edmxNode As XElement = edmxDoc.Element(edmxns + Edmx) If (Not edmxNode Is Nothing) Then Dim runtimeNode As XElement = edmxNode.Element(edmxns + Runtime) If (Not runtimeNode Is Nothing) Then Dim ModelNode As XElement = runtimeNode.Element(edmxns + sModel) If (Not ModelNode Is Nothing) Then If sModel = ConceptualModels Then csdlContent = ModelNode.Element(edmns + Schema) ElseIf sModel = StorageModels Then csdlContent = ModelNode.Element(ssdl + Schema) ElseIf sModel = Mappings Then Dim MappingsNode As XElement = ModelNode.Element(cs + Mapping) If (Not MappingsNode Is Nothing) Then csdlContent = MappingsNode.Element(cs + EntityContainerMapping) End If End If End If End If End If End If Return csdlContent End Function

  • RELEVANCY SCORE 2.70

    DB:2.70:How Do I Alphabetically Order The List Of Records ? x8



    I have a column called user id where names are stored as strings, i want to query them so that i get the list of records arranged alphabetically (i.e. the alphbatical order of user names,the primary key also happens to b the userid).I tried modifying the jaws.xml by giving in as follows ,but no change any ideas? oraclepool type-mappingOracle8/type-mapping enterprise-beans ejb-nameIMFriendlistBean/ejb-name table-nameTANGO_IM_FRIENDLIST/table-name cmp-field field-namekeyfield/field-name column-nameKEYFIELD/column-name /cmp-field cmp-field field-nameTango_Userid/field-name column-nameTANGO_USERID/column-name /cmp-field cmp-field field-nameGroupid/field-name column-nameTANGO_GROUPID/column-name /cmp-field cmp-field field-nameFriendid/field-name column-nameTANGO_FRIENDID/column-name /cmp-field !-- JBoss 2.4 -- findByPrimaryKey ![CDATA[(= 1 1)]] Tango_Userid /enterprise-beansTango_Userid is the column name, iwant to it to b retrieved fro m db in alphabetical order

    DB:2.70:How Do I Alphabetically Order The List Of Records ? x8


    I haven't used JAWS in over a year, but I remember the code in the 2.4 documentation does work.http://www.jboss.org/online-manual/HTML/ch04s07.html

  • RELEVANCY SCORE 2.69

    DB:2.69:Need A Query To Find A Table By Two Of Its Column Names jf


    I work in software quality assurance. Oftentimes I am trying to figure out from which table data is being pulled, but the database has thousands of tables to look through. Instead of looking through all the tables, I just take a guess at one of the column names and execute a query like this:

    SELECT table_name FROM information_schema.columns WHERE column_name like '%pur%code%'

    That usually works, but occassionally it can return hundreds of table names which doesn't help me much. Instead, I'd like to form a query that could take two column names and return all the tables that have those columns.

    So, in English, I want to say:

    "Give me the names of all the tables that have a column named like %pur%code% and another column named like %client%loc%"

    How do I do this?

    DB:2.69:Need A Query To Find A Table By Two Of Its Column Names jf

    Ok, now..assuming your column names are ENAME, SDATE, FNAME, LNAME, JOB. Then use this query, it might help..

    SELECT table_name
    FROM user_tab_cols
    WHERE column_name in('ENAME','SDATE','FNAME',LNAME','JOB');