• RELEVANCY SCORE 3.67

    DB:3.67:Finding Common Values In Columns Of Data pk




    Using Excel 2007

    Assume I have 10 columns, each containing both unique and common alphnumeric data

    What I would like to be able to do is:

    Identify all the variables that are common to
    all 10 (or whatever number) of columns.

    Many thanks for your help.

    DB:3.67:Finding Common Values In Columns Of Data pk

    Thank you for all your help on this.

    I've tried this out and I can see it works but I'm experimenting with various other workarounds but you've been a great help in getting me to clarify my thinking and try out various options.

  • RELEVANCY SCORE 3.49

    DB:3.49:Finding Unique Number Columns d7




    Hi,

    I'm looking for the most optimal way of finding all numeric columns in a table without
    any duplicates. This query has to run on VERY large tables.

    So far I have come up with:
    SELECT COUNT(COLUMN_NAME), COUNT(DISTINCT COLUMN_NAME)
    FROM TABLE
    WHERE TRUNC(COLUMN_NAME) = COLUMN_NAME

    Then comparing these two results.

    But that's just too slow. I could check for numeric columns with unique indexes,
    but that's not guaranteed to find all possible solutions.

    Any ideas ?

    Cheers,
    Ro

    DB:3.49:Finding Unique Number Columns d7

    if you have stats, you could useI won't have statistics computed, so I think I'll go with Laurent's suggestionno you will not, both solutions are identical are required statistics

    Message was edited by:
    laurentschneider
    but count(*)/decode(count(distinct column_name),0,null) is possible

  • RELEVANCY SCORE 3.29

    DB:3.29:Dynamic Alv Layout z9





    Hi ,

    I have a requirement where the ALV report layout is needed to be Dynamic with Number of Columns and Different Column headers. For example, at one scenario, the ALV layout might have 10 Columns ( 5 Fixed Columns + 5 Dynamic Columns with unique column headers) and for another scenario the ALV layout might have 15 Columns ( 5 Fixed Columns + 10 Dynamic Columns with unique column headers). Here the column headers also may vary for different scenarios and also the number of columns in the layout also varies for different scenarios.

    I have never worked on such a requirement before. Please let me have your suggestions. Appreciate your help.

    Thanks,

    Kannan

    DB:3.29:Dynamic Alv Layout z9


    Hi Kannan,

    You will have to use dynamic internal tables with a normal ALV. Search the forum for how to create dynamic internal tables..

    Regards,

    Shravan

  • RELEVANCY SCORE 3.22

    DB:3.22:Finding Out Distinct Machine Type Serial Number. dc



    Hi,

    I have to create a report in CRM which comprises of fields like MACHINE TYPE , SERIAL NUMBER , CLAIM ID.A particular Machine Type Serial Number has multiple Claim ID.I have to find out the count of unique machine Type Serial number.Can u suggest some logic ?

    Thanks,

    Saswati.

    DB:3.22:Finding Out Distinct Machine Type Serial Number. dc


    Hi,

    I have to create a report in CRM which comprises of fields like MACHINE TYPE , SERIAL NUMBER , CLAIM ID.A particular Machine Type Serial Number has multiple Claim ID.I have to find out the count of unique machine Type Serial number.Can u suggest some logic ?

    Thanks,

    Saswati.

  • RELEVANCY SCORE 3.13

    DB:3.13:Unconditional Table Level Supplemental Logging x1


    Hi I want to enable unconditional table level supplemental logging on unique index columns.

    But Alter table tablename add supplemental log data (unique index) columns always;

    gives error.If i omit always it is accepting but while it unconditionally log the unique index columns.

    DB:3.13:Unconditional Table Level Supplemental Logging x1

    Hi,

    SQL Alter table DEPT add supplemental log data (unique index) columns

    Table altered.

    SQLAlter table DEPT add supplemental log data (all) columns; [This will log every column, Note that REDO generation will be more.]

    Table altered.

    SQL CONN / AS SYSDBA
    Connected.

    SQL SELECT OWNER, TABLE_NAME, LOG_GROUP_TYPE, ALWAYS FROM DBA_LOG_GROUPS WHERE TABLE_NAME = 'DEPT';

    OWNER TABLE_NAME LOG_GROUP_TYPE ALWAYS
    ------------------------------ ------------------------------ ------------------- -----------
    SCOTT DEPT ALL COLUMN LOGGING ALWAYS
    SCOTT DEPT UNIQUE KEY LOGGING CONDITIONAL

    First row is for ALWAYS, and second row is when index is involved.

    HTH..
    Sumit.

  • RELEVANCY SCORE 3.06

    DB:3.06:Finding The Number Of Site Hits In A Month Of Sharepint 2010 jm


    Hi ,
    I need to find the numbers of hits of a site to draw report on the same site, Any idea?
    This report should be able to show accesson yearly based,month based, daily, weekely based etc.
    Report like No of hits - 8000,
    NUmber of Unique hits - 700,
    The development platform is 2010

    DB:3.06:Finding The Number Of Site Hits In A Month Of Sharepint 2010 jm

    You can try:
    SPWeb.GetUsageData Method -
    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb.getusagedata.aspx

  • RELEVANCY SCORE 3.00

    DB:3.00:Calculate Actual Inventory cj



    Dear Qlikviewers,

    In the table below you will the data that i've to calculate the actual inventory each day.

    How to calculate the actual amount? It should sum the highest SerialNr per WarehouseNr. In this case SerialNr 4,5 and 3. Who can help me out? The explanation what the columns mean see below

    Artikelnr=Unique number
    Datum=date
    Serialnr=Unique number for every day
    WarehouseNr=Warehouse number for every record
    WH1/WH2/WH3=every warehouse has it own colomn

    DB:3.00:Calculate Actual Inventory cj


    Dear Qlikviewers,

    In the table below you will the data that i've to calculate the actual inventory each day.

    How to calculate the actual amount? It should sum the highest SerialNr per WarehouseNr. In this case SerialNr 4,5 and 3. Who can help me out? The explanation what the columns mean see below

    Artikelnr=Unique number
    Datum=date
    Serialnr=Unique number for every day
    WarehouseNr=Warehouse number for every record
    WH1/WH2/WH3=every warehouse has it own colomn

  • RELEVANCY SCORE 2.99

    DB:2.99:Index On Two Columns Becomes Function Index? 97


    Hello, I am creating a unique index with two columns, one number(9) and one date.

    This becomes a function based index with the number column and a hidden sys-column (the date).

    When I do queries that use this index the autotrace tells me it does things like this:

    sys_nc00001$SYS_OP_DESCEND(datevalue)
    sys_nc00001$ IS NOT NULL

    How come it didn't make a normal index?

    DB:2.99:Index On Two Columns Becomes Function Index? 97

    Is there some performance penalty in this? Should I make the index asc instead of desc?I'd say that depends why you are using descending index.

    Nicolas.

  • RELEVANCY SCORE 2.98

    DB:2.98:Unique Keys kf


    How can I modify unique keys that identify upper or low letter in varchar columns

  • RELEVANCY SCORE 2.95

    DB:2.95:Unable To Identify The Base Table For The Following Columns xm


    Can u help me in finding the ("*base*") TABLE in SOURCING
    which consists of the following columns

    NEGOTIATION NUMBER
    NEGOTIATION DESCRIPTION
    STATUS
    CLOSE DATE
    TENDER OPEN BOX DATE
    SUPLLIER NO.......
    ..........

    Edited by: 945038 on Jan 3, 2013 2:28 AM

  • RELEVANCY SCORE 2.87

    DB:2.87:Problem In Distributing Data In Various Columns 13



    hi all,

    I am facing a problem in distributing data in various columns,the problem is i have a column named matnr_v ,which is a unique number having multiple corresponding control points which is of 2 digits,for each control point with particular matnr_v ,we are getting multiple quantities.We have to distributed such records in various columns of control point with particular matnr_v.Please tell how to distribute data.

    Regards,

    Praveen

    DB:2.87:Problem In Distributing Data In Various Columns 13


    hi all,

    I am facing a problem in distributing data in various columns,the problem is i have a column named matnr_v ,which is a unique number having multiple corresponding control points which is of 2 digits,for each control point with particular matnr_v ,we are getting multiple quantities.We have to distributed such records in various columns of control point with particular matnr_v.Please tell how to distribute data.

    Regards,

    Praveen

  • RELEVANCY SCORE 2.86

    DB:2.86:Fetch Data From Multiple Sharepoint Lists d9


    I want to fetch data from multiple lists(3 or 4) based on Unique number (say RequestID).
    For eg. two columns from List1, two columns from List2, two columns from List 3 based on specific RequestID.
    Final result should be 1 single row with desired columns which i will bind to grid or some other control.
    Any help will be highly appreciated, i am badly stuck :(

    Thanks,
    Saurabh

    DB:2.86:Fetch Data From Multiple Sharepoint Lists d9

    Linq is the best choice to cusomize any type of complex query.VV

  • RELEVANCY SCORE 2.86

    DB:2.86:Bulk Exception Problem mj


    DECLARE
    TYPE empid_tab IS TABLE OF emp1%ROWTYPE;

    emp_sr empid_tab;
    -- CREATE AN EXCEPTION HANDLER FOR ORA-24381
    ERRORS NUMBER;
    dml_errors EXCEPTION;
    v_err_no VARCHAR2 (200);
    v_err_message VARCHAR2 (200);
    PRAGMA EXCEPTION_INIT (dml_errors, -24381);
    BEGIN
    SELECT *
    BULK COLLECT INTO emp_sr
    FROM emp1
    WHERE hiredate '30-MAY-80';

    FORALL i IN 1 .. emp_sr.COUNT SAVE EXCEPTIONS
    INSERT INTO emp_temp123
    VALUES emp_sr (i);
    --if any dupliacte records are inserting onto the emp_temp123 then it will give error as unique constraint violated

    -- IF ANY ERRORS OCCURRED DURING THE FORALL SAVE EXCEPTIONS,
    -- A SINGLE EXCEPTION IS RAISED WHEN THE STATEMENT COMPLETES.
    EXCEPTION
    WHEN dml_errors
    THEN -- NOW WE FIGURE OUT WHAT FAILED AND WHY.
    ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
    DBMS_OUTPUT.put_line ('Number of statements that failed: ' || ERRORS);

    FOR i IN 1 .. ERRORS
    LOOP
    DBMS_OUTPUT.put_line ( 'Error #'
    || i
    || ' occurred during '
    || 'iteration #'
    || SQL%BULK_EXCEPTIONS (i).ERROR_INDEX
    );
    DBMS_OUTPUT.put_line ( 'Error message is '
    || SQLERRM
    (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE)
    );
    v_err_no := SQL%BULK_EXCEPTIONS (i).ERROR_INDEX;
    v_err_message := SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE);

    INSERT INTO errorlog
    (ID, MESSAGE
    )
    VALUES (v_err_no, v_err_message
    );

    COMMIT;
    END LOOP;
    END;

    IDMESSAGE
    5ORA-00001: unique constraint (.) violated
    6ORA-00001: unique constraint (.) violated
    7ORA-00001: unique constraint (.) violated
    8ORA-00001: unique constraint (.) violated
    9ORA-00001: unique constraint (.) violated
    10ORA-00001: unique constraint (.) violated
    11ORA-00001: unique constraint (.) violated
    12ORA-00001: unique constraint (.) violated
    13ORA-00001: unique constraint (.) violated
    3ORA-00001: unique constraint (.) violated
    4ORA-00001: unique constraint (.) violated

    here its only inserting the erros only but i want to insert the error records ie all columns of error record into another table named emp_err .how to do it.

    DB:2.86:Bulk Exception Problem mj

    ORA-01555 Snapshot Too Old is dml realted error.is it possible to avoid this error using bulk save exception.
    actually we are inserting billions of records from onet able to another. at the time of inserting i want to avoid errors like unique constraint violated and snap shot too old.

  • RELEVANCY SCORE 2.85

    DB:2.85:Is There Any Way To Compare Index By Columns And Column Orders In Oracle? ps


    create table student_A( id number not null,name varchar2(50),lastname varchar2(50)); create unique index intstd_A on student (id);create table student_B( id number not null,name varchar2(50),lastname varchar2(50)); create unique index intstd_A on student_B (id , lastname );What would be the query to compare this two?

    DB:2.85:Is There Any Way To Compare Index By Columns And Column Orders In Oracle? ps

    Maybe (if column names might be different)select * from (select t.owner,t.table_name,i.index_name,i.column_name,i.column_position, i.descend,t.data_type,t.data_length,t.data_precision,t.data_scale,t.nullable from all_ind_columns i, all_tab_columns t where t.table_name = 'STUDENT_A' and t.table_name = i.table_name and t.owner = i.table_owner and t.column_name = i.column_name ) a full outer join (select t.owner,t.table_name,i.index_name,i.column_name,i.column_position, i.descend,t.data_type,t.data_length,t.data_precision,t.data_scale,t.nullable from all_ind_columns i, all_tab_columns t where t.table_name = 'STUDENT_B' and t.table_name = i.table_name and t.owner = i.table_owner and t.column_name = i.column_name ) b on a.column_position = b.column_position and a.data_type = b.data_type and a.data_length = b.data_length and lnnvl(a.data_precision != b.data_precision) and lnnvl(a.data_scale != b.data_scale) and a.nullable = b.nullable and a.descend = b.descendRegardsEtbin

  • RELEVANCY SCORE 2.85

    DB:2.85:Count Unique Values Based Upon Critieria pj


    Hi I am trying to calculate the number of unique values in a range of data, however I am doing this based upon a set of criteria in some corresponding columns
    I can get the number of rows that match the criteria by using this formula (entered as an array)

    =SUM(IF('Source Data'!$B$15:$B$220=Infra,IF('Source Data'!$F$15:$F$220=New,IF('Source Data'!$T$15:$T$220=Y,1,0))))
    So it counts how many rows have “Infra” and “New” in two separate columns, this gives me 30 rows, however in a 3suprd/sup column I have a set of names and I want to identify how many unique values are contained within the 30 rows that match above, there
    are 22 unique names within the 30 rows. Can identify the unique values by using either of the formulas below :
    =SUM(IF('Source Data'!$G$15:$G$2200,1/COUNTIF('Source Data'!$G$15:$G$220,'Source Data'!$G$15:$G$220),0),0)))
    But I cant combine them, any ideas how I can combine or identify the 22 unique values
    Thanks

    DB:2.85:Count Unique Values Based Upon Critieria pj

    Here is a different kind of formula that should work in all versions of Excel.
    It's an array formula too, confirmed with CtrlShiftEnter:
    =SUM(IF(FREQUENCY(IF('Source Data'!$C$15:$C$220=Infra,IF('Source Data'!$F$15:$F$220=New,IF('Source Data'!$T$15:$T$220=Y,IF('Source Data'!$G$15:$G$220,MATCH('Source Data'!$G$15:$G$220,'Source Data'!$G$15:$G$220,0))))),ROW('Source Data'!$G$15:$G$220)-ROW('Source
    Data'!$G$15)1),1))Regards, Hans Vogelaar

  • RELEVANCY SCORE 2.84

    DB:2.84:Unique Records And Performance With Combined Fields z9



    Hi all,

    Just wondering, I am trying to create a performant application for a 600 mln record dataset. We already reduced the number of columns, but now I want to speed up QV by reducing the number of unique records within a field. Problem is that the end user would like to see combined fields like 01/DDFFGG where I want to split 01 and DDFFGG. How would you solve this?

    a variable in the front which combines them so you don't have the unique values in the databaseother options?

    thank you very much

    DB:2.84:Unique Records And Performance With Combined Fields z9


    Hi all,

    Just wondering, I am trying to create a performant application for a 600 mln record dataset. We already reduced the number of columns, but now I want to speed up QV by reducing the number of unique records within a field. Problem is that the end user would like to see combined fields like 01/DDFFGG where I want to split 01 and DDFFGG. How would you solve this?

    a variable in the front which combines them so you don't have the unique values in the databaseother options?

    thank you very much

  • RELEVANCY SCORE 2.83

    DB:2.83:Finding The Rows And Columns With The Most Entries In A Spreadsheet ck


    I have this spreadsheet with a large number of rows and columns.
    I want to locate the longest row and the longest column in this sheet.
    What I mean by longest is the one containing with the most number of cells with data.
    Is it also possible to locate the 10 longest rows and the 10 longest columns in this sheet?
    Thanks
    crescent786

    DB:2.83:Finding The Rows And Columns With The Most Entries In A Spreadsheet ck

    Thanks Vijay, that does help.
    You have provided me the answers for the last 3 questions - thanks!
    I am really doing some research in to the Quran and as you may be aware the Quran has 6,237 Aayaths (commonly referred to as verses, but it means 'signs') and I am trying to sort together the words, the phrases, the concepts etc etc that is described in
    the Quran.
    I find the whole exercise to be fascinating.
    Thanks for your ready assistance.
    If you are curious you should visit my site www.imtiazmuhsin.com
    Thanks once more
    Imtiaz Muhsion

  • RELEVANCY SCORE 2.81

    DB:2.81:Finding What Column Caused Duplicate Entry Exception. 3d


    HI,
    Is there any way to find out what column caused SQLException (root cause duplicate entry) in a table where more than one columns are having unique key constraint? The excpetion message has the index of the column, but then parsing the exception message for getting column name does not seem to be a proper way.

    Thanks,
    -atul

    DB:2.81:Finding What Column Caused Duplicate Entry Exception. 3d

    Might I suggest using the unique keys you were given to query the database to see if the record already exists before inserting it? If so, dont insert, and you now know what the keys are. You'll need to put the query and the insert sql statements in the same transaction so its safe. I dont think it will impact performance much.

    If you want to get fancy, you can send to the log file the contents of the record you tried to insert and the already existing one in the database so you can examine them in detail.

  • RELEVANCY SCORE 2.81

    DB:2.81:Unique Index Vs Non-Unique Index sa


    Hi Gurus,

    I'm getting lots of "TABLE ACCESS FULL" for lots of columns which have non-unique indexes is some queries. So my question is does optimizer does not pick up non-unique index but only pickes up unique indexes for those columns.

    Thanks
    Amitava.

    DB:2.81:Unique Index Vs Non-Unique Index sa

    Hi Amitava,

    Optimizer analyze your query and compute the best access path to retrieve the data. It will not look in for Unique or Non-Unique index. Post your query, indexed column, the explain plan and Version of Oracle.

    [url https://wikis.oracle.com/display/Forums/Forums+FAQ] Posting Guidelines

    Thanks,
    Vijay

  • RELEVANCY SCORE 2.81

    DB:2.81:Best Way To Get Next Sequence Number pk


    I have a table called AppInfo with one row that holds application information. One of the columns is NextSequenceNumber. I plan to use this when creating unique names for images I will be uploading.

    I would like to execute a query from my domain service that insures that this number is unique. Essentially: the table/row is locked, value incremented, value retrieved, table/row unlocked, and return the value retrieved. What is the best way to implement
    this using RIA services?
    Thanks,
    Jim K.

    DB:2.81:Best Way To Get Next Sequence Number pk

    The Guid.NewGuid() function is a perfect solution! I have to admit I just wasn't even aware ofit before. This is a case where youfind something that worksand stop looking for new ways to do it. Learning Silverlight has been great since it also forces
    me to revisit a lot of the newer .NET stuff. That's why I come here...
    I'm already using identity fields for other purposes, but needed the ID beforeI evensaved, the Guid solution is really exactly what I needed.
    Thanks for your help,
    Jim K.

  • RELEVANCY SCORE 2.81

    DB:2.81:Finding The Number Of List Item Hits Of Sharepint 2010 8d


    I need to find the numbers of hitson listitem
    This report should be able to show accesson yearly based,month based, daily, weekely based etc.
    Report like No of hitsof ID 1= 8000,
    NUmber of Unique hits of ID 1 - 700,
    The development platform is 2010 .
    Is there any way using the web anaylatics feature or whats the way to go.

    DB:2.81:Finding The Number Of List Item Hits Of Sharepint 2010 8d

    The audit data is stored in a table of the sharepoint content database.
    So yes, if there is too much audit data stored,this canimpact the server overall performances.
    On the Site Collection audit settings page, thereis a Audit log trimming setting which allow :

    to clean the audit data older than x daysto store, in a document library, the audit report before the logs are trimmed.
    You could test this option to check if it can meet your needs.
    More information here:
    http://sharepointthomas.blogspot.fr/2011/07/how-to-enable-audit-functionality-in.html

  • RELEVANCY SCORE 2.81

    DB:2.81:I Have The Mdx, But Cant Figure Out How To Use The Tail Correctly cf


    Hello sirs
    the MDX:

    SELECT
    non empty
    {Measures.Measure} on columns,
    Non empty
    {[Time Accounting period].[Unique Month].[Unique Month].ALLMEMBERS
    }
    on rows
    from
    cube
    I would like to do a tail on the number of months, and I only want months with data in them.

    How to???

    br David

    david

    DB:2.81:I Have The Mdx, But Cant Figure Out How To Use The Tail Correctly cf

    Hello and thankyou so much for answering.
    The thing with the measure is that there can be future postings in it, so I cannot only filter on that criteria. But he FFE measure is to be used for that purpose.

    Do you know how to put the uniquemonth key in the output?
    I experimented alot and came up with this:
    SELECT
    --{Filter({[Time Accounting period].[Unique Month].[Unique Month].members}, [Measures].[FFE Loaded]0)}
    --NON EMPTY
    {
    [Measures].[Amount USD]
    -- ,[Measures].[Amount USD ShpLvL]
    } ON COLUMNS
    ,{
    Tail
    (
    Filter
    (
    {
    [Time Accounting period].[Unique Month].[Unique Month].MEMBERS
    }
    ,
    [Measures].[FFE Loaded] 0
    )
    ,13
    )
    ,[Time Accounting period].[Unique Month].[Unique Month].MEMBERS.Member_Key
    } ON ROWS
    FROM [PnL Cube];
    david

  • RELEVANCY SCORE 2.81

    DB:2.81:Decode Function Issue / Problem .. sc


    Dear Sir / Madam,

    Thanks for your understanding.

    Right now I am facing a unique problem.

    I am using decode function in a select statement. The columns are dynamic some columns are of type number and some are varchar datatypes. I want to apply decode function for every column irrespective of datatype.

    What i am finding difficult is if the column is a number datatype , the decode funciton is working good but if the column datatype is varchar or char datatype then i am getting ..the error ..

    report error:
    ORA-01722: invalid number

    here's the decode function..

    decode (CHAI.EVNDRIVE,
    -800000000000000,'.A',
    -700000000000000, '.B',
    -600000000000000 ,'.C',
    -500000000000000 , '.D',
    -400000000000000 , '.E',
    -300000000000000 , '.F',
    -200000000000000, '.G',
    -100000000000000 , '.H',
    -1000000000, '.R' ,CHAI.EVNDRIVE ) EVNDRIVE

    how to use the above decode function irrespective of datatype..

    pls let me know how to over come this kind of scenario.

    is their a way to find whether the column datatype is number or char , so that i can check the column datatype before the decode funciton is applied ..

    thanks ..

    kumar

    DB:2.81:Decode Function Issue / Problem .. sc

    You can try ,

    This is a "cheap and dirty" solution,
    but it should work (if CHAI.EVNDRIVE is varchar2):

    decode (CHAI.EVNDRIVE,
    '-800000000000000','.A',
    '-700000000000000', '.B',
    '-600000000000000' ,'.C',
    '-500000000000000' , '.D',
    '-400000000000000' , '.E',
    '-300000000000000' , '.F',
    '-200000000000000', '.G',
    '-100000000000000' , '.H',
    '-1000000000', '.R' ,CHAI.EVNDRIVE ) EVNDRIVELev

  • RELEVANCY SCORE 2.81

    DB:2.81:Re: Sequenced Primary Key Slowed Down My Database! 1k


    Thanks for your reply. The table consists of four columns: date, number, number, number and the date column is indexed. There are not that many unique values to the other three columns. maybe at most 200 values for 11 million rows. The date column is not unique either, but it is more diverse at least. I have just added another number column with sequence.
    I was looking at the data in the table itself in the object browser of application express and tried to sort it by date descending and that took me forever to come up, and then just going to the next page again takes forever.

    DB:2.81:Re: Sequenced Primary Key Slowed Down My Database! 1k


    INDEX FAST FULL SCAN

    That shows it is using the index.

  • RELEVANCY SCORE 2.78

    DB:2.78:Re: Creating Multiple Unique Key s9


    Unique constraints are perfectly acceptable. Unique indexes might be useful if you plan on querying the table specifying all the columns in one of your unique constraints.

    Can you post the error number error code you're getting. The SQL you're executing at the time would also be helpful.

    Justin

    DB:2.78:Re: Creating Multiple Unique Key s9

    Hi

    If you execute the statements you posted you get the following error:

    ORA-00001: unique constraint (NF9I.RT_UNQ_LOCAL_11) violated

    If you drop this unique index and re-execute the second insert it works. Then the following select displays the values contained in this index:

    select ROAD_NUMBER,
    MNPLTY_GEO_LOCN_SEQNUM,
    EFFECTIVE_DATE,
    ROUTE_SIGNING,
    RT_SIGNING_CDE_EFF_DTE
    from ROUTES_TEST2

    Result is:

    ROAD MNPLTY_GEO_LOCN_SEQNUM EFFECTIVE RO RT_SIGNIN
    ---- ---------------------- --------- -- ---------
    85187 10-JAN-02 1 10-JAN-02
    85187 10-JAN-02 1 10-JAN-02

    i.e. you didn't change any of these values!

    Chris

  • RELEVANCY SCORE 2.78

    DB:2.78:Owb Dataprofiling - Unique Key Analysis On Non-Number Columns ja


    Does anyone know an easy way to enable unique key analysis on non-Number columns (OWB 10.2.0.3).
    It seems that the profiler by default disables the
    'Use in relationsship discovery' when the documented datatype is non-Number.
    Is this a setting which can be configured for OWB, or is there a smart way to set this property for all columns?

    thks in advance

    DB:2.78:Owb Dataprofiling - Unique Key Analysis On Non-Number Columns ja

    Hi don't think there is a way to to automatically switch this on/off, a small script can be created to set the option on/off for all columns in the table.

    Cheers
    David

  • RELEVANCY SCORE 2.78

    DB:2.78:Performance Impact Of Unique Constraint On A Date Column x1


    In a table I have a compound unique constraint which extends over 3 columns. As a part of functionality I need to add another DATE column to this unique constraint.

    I would like to know the performance implications of adding a DATE column to the unique constraint. Would the DATE column behave like another VARCHAR2 or NUMBER column, or would it degrade the performance significantly?

    Thanks

    Message was edited by:
    user627808

    DB:2.78:Performance Impact Of Unique Constraint On A Date Column x1

    What performance are you concerned about degrading? Inserts? Or queries? If you're talking about queries, what sort of access path are you concerned about?

    Are you concerned that merely changing the definition of the unique constraint would impact performance? Or are you worried that whatever functional change you are making would impact performance (i.e. if you are now retaining historical data in the table rather than just updating it)?

    Regardless of the performance impact, unique indexes (and unique constraints) need to be correct. If you need to allow duplicates on the 3 current columns with different dates, then you would need to change the unique constraint definition regardless of the performance impact. Fast and wrong generally isn't going to be preferrable to slow and right.

    Generally, though, there probably is no reason to be terribly concerned about performance here. Indexing a date is no different than indexing any other primitive data type.

    Justin

  • RELEVANCY SCORE 2.78

    DB:2.78:Index Creation: Unique Vs Nonunique jp


    Looking for a white paper on the timing for creation and for performance of nonunique vs unique indexes on columns that are indeed unique.

    Thanks
    Bill

    DB:2.78:Index Creation: Unique Vs Nonunique jp

    Well, Tom provided the answer.

    For more information see the "Ask Tom" response to this question.

    Regards
    Bill

  • RELEVANCY SCORE 2.78

    DB:2.78:Bitmap Join Index cf


    Is it a requirement for unique constraints to be defined on both DIM and FACT tables when using bitmap join indexes? I have unique constraints on the DIM columns that I'm joining, but those columns are naturally not unique in the FACT, but I still get an error that the columns aren't unique.

    DB:2.78:Bitmap Join Index cf

    I had very good (i.e. super) results using bitmaps in star configuration DWs.

    Never used bitmap join index so far, so no much personal experience.

    did you check snowflake vs. star configuration ?

    The missing primary key error probably tells you the truth, check your indexes.

    --
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#23588
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#25750
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_510a.htm#2077036

    I always found Tom Kyte a source of valuable information :
    http://asktom.oracle.com

    on bitmapped indexes :
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_510a.htm#2077036
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:32633204077527

    select count(*)
    from fact a11, dim a12
    where a11.fact_sk=a12.dim_sk;

    doen't this mean just counting all entries in table FACT ?

    hopefully it helps a bit ..

  • RELEVANCY SCORE 2.78

    DB:2.78:Finding The Number Of Com Ports In A Pc Using Visa Resource cj


    Hi all,
    I need to find the number of COM ports in a PCand their names programmaticaly.
    Is there any direct method for this?

    Presenty I am using the following method,
    - find the VISA resources (ASRL?*::INSTR)
    -Find COM port name by string processing the inteface Information ::Interface description property
    Is inteface Information ::Interface description property format is unique? Is this a correct method?
    VI is attached.
    Please suggest..










    Attachments:







    Finding the COM ports in PC.vi ‏22 KB

    DB:2.78:Finding The Number Of Com Ports In A Pc Using Visa Resource cj

    One suggestion I have is to set the search mode of the VISA find resource to only return the aliases. Then you don't have to parse the return strings and just convert it to an array of resourse strings.

    Also, I found a bug either in the online help or the VI itself. I am using the evaluation version of 8.2 so I don't know if it exists on earlier or non-eval versions.

    The bug is that if you use create (constant,control,indicator) for the search mode, the ring that in creates doesn't match what the online help indicates. I'll attach a picture so show what I found. Hopefully someone at NI can indicate the correct one and fix the bug (either the ring or the documentation).










    Attachments:







    VISAFindResourceBug.JPG ‏169 KB

  • RELEVANCY SCORE 2.77

    DB:2.77:How To Copy The Right Amount Of Cells mm


    My spreadsheet has col A with a unique part number for each row. Each part number has its details that fill up the columns on its row from Col B: CX. The details vary so the number of columns filled vary. ex. partnumer 1, has data in 2 columns, while
    partnumber 2 has data in 13 columns, etc. I have the number of cells filled in its own column, collected in column DA.

    Is there a formula to use with concatenation or after concatenation, that will look to the number I collected (DA) and only copy that number of cells for that partnumber?

    I've tried using a broad concatenation and get to much extra space with no way to delete it, so im hoping you can help me.

    DB:2.77:How To Copy The Right Amount Of Cells mm

    Hi again.
    I'm surprised no-one has suggested a UDF for this. Here's one that will save you typing those long concatenate formulae. Press Alt+F11, do Insert Module then paste the code below on the right.
    Then in a cell, do =ConcatNonBlanksInRange(B2:CX2)
    The code below will put the text from each non-blank cell on a new line in the result. Edit the strDelim string per your requirements.
    You'll need to save your file as *.xlsm (macro enabled).
    Hope that helps.
    Cheers
    Rich

    Function ConcatNonBlanksInRange(rng As Range)
    Dim str As String, strDelim As String
    Dim c As Range

    ' strDelim = ", " 'Take your pick...
    strDelim = vbCrLf 'Use this if you want each cell to appear on a new line in the cell.
    For Each c In rng
    If c.Value vbNullString Then str = str c.Value strDelim
    Next c
    ConcatNonBlanksInRange = Left(str, Len(str) - Len(strDelim))
    End Function

  • RELEVANCY SCORE 2.77

    DB:2.77:Defining Primary Keys kp


    How do you define Primary Key with multiple columns (say two) where the values of the first column such as extract date is not unique and the values of the second column such as sequence number is unique. The combined value of the two columns will be unique. For example

    Extract Date Seq Number
    20070701 001
    20070702 002
    20070703 003

    If you can please send my the syntax of the table definition.
    Thanks.

  • RELEVANCY SCORE 2.77

    DB:2.77:Datamodeler 4 - Unique Index (Error: Index Name Length Exceeds Maximum Allowed Length(30)) fp


    Hi,When modeling a table with 3 columns as PK, engineering from Logical Model to Oracle 10g Relational Model, then visualizing the DDL script there is a CREATE UNIQUE INDEX section being created automatically. I could not find anywhere to replace the Unique Index name, since I get the following ERROR message:-- ERROR: Index name length exceeds maximum allowed length(30)Is there a way to fix this problem? Thanks in advance,RegardsCREATE TABLE L011GPLA ( CODGPR NUMBER NOT NULL , CODANR NUMBER (4) NOT NULL , CODPAN NUMBER (4) NOT NULL , DCRANL TIMESTAMP (2) , UCRANL VARCHAR2 (50) , DALANL TIMESTAMP (2) , UALANL VARCHAR2 (50) ) ;-- ERROR: Index name length exceeds maximum allowed length(30)CREATE UNIQUE INDEX L011GPLA_CODGPR_CODANR_CODPAN_IDX ON L011GPLA ( CODGPR ASC , CODANR ASC , CODPAN ASC ) ;

    DB:2.77:Datamodeler 4 - Unique Index (Error: Index Name Length Exceeds Maximum Allowed Length(30)) fp

    The Data Modeler created that with 33 character. Maybe I am missing some configuration rules in somewhere.Then, IMHO, that is a bug. An Oracle tool should not be creating objects with names that violate Oracle's naming conventions.

  • RELEVANCY SCORE 2.77

    DB:2.77:Number/Integer Vs Varchar2 As Primary Key jm


    Suppose for EMPLOYEE table we have two columns,
    EMPLOYEE_ID NUMBER(5) and EMPLOYEE_NAME VARCHAR2(200),

    now suppose both are unique, no two "DEBALINA ROY" are present in the EMPLOYEE_NAME column, every name is unique,
    In spite of the uniqueness of EMPLOYEE_NAME we create the primary key genrally on the EMPLOYEE_ID column, why?

    Is it for any performance issue?

    DB:2.77:Number/Integer Vs Varchar2 As Primary Key jm

    hi,

    In spite of the uniqueness of EMPLOYEE_NAME we create the primary key genrally on the EMPLOYEE_ID column, why?

    While any of them can be used to identify the row, there is a good chance the employee name (eg. spouse name after marriage/ for another reason..) might change over time. Though at design time, you may say that the name never changes, in the future such a requirement might come up.

    Hence, It is optimal to use immutable values (such as empid (which will remain constant over time )) as the primary keys.

    Other than that, the basic rule is that you shuld use a key that uniquely identifies your row and is immutable as the primary key. if that is the employee name , then so be it. However, emp name might change over time.. Changeable primary keys are , I believe , a case of bad relational database design.

    Thanks,
    Rajesh.

  • RELEVANCY SCORE 2.75

    DB:2.75:How To Select Distinct/Unique Values md



    Hi All,

    I created a report where I fetched data from oracle DB, here data which is coming is having duplicate values and I dont want to restrict it at data base level, here I need your help.

    Suppose I am having 3 columns, contact_Name , Email_Address , Phone_Number. Now the phone number field is having 4 unique values for single records, e.g. Name ABC is having Mobile number, Office Number , Landline Number and Fax Number. The records looks unique but at the same time Email_Addresses are getting repeated and if I dont keep filter on Phone_Number then I will have duplicate Email Addresses which I want to avoid. How can I apply unique/Distinct filter on Email_Address coulmn in QlikView so that I can get unique values for email address in this case it doesnt matter if it ignore any of the value of Phone_Number column and use any one out of Mobile number, Office Number , Landline Number and Fax Number.

    Thanks in advance.

    Regards,

    Abhishek

    DB:2.75:How To Select Distinct/Unique Values md


    Try to load your data with a WHERE clause:

    LOAD

    Contact_Name,

    Email_Address,

    Phone_Number

    FROM .... WHERE not EXISTS(Email_Address);

  • RELEVANCY SCORE 2.75

    DB:2.75:Counting Unique Records 8p


    Below is some data. My goal is to count the unique number of records.
    I know the below formula counts how many unique neighborhoods are in California:

    Can someone tweak this formula that provides me the number of unique States, Cities, and Neighborhoods? In other words I want a formula that will provide me the number of unique records of columns A through C. As always, I appreciate your help

    DB:2.75:Counting Unique Records 8p

    State

    Cities

    Neighborhoods

    Washington

    Seattle

    Space Needle

    California

    San Francisco

    Downtown

    California

    San Francisco

    Downtown

    California

    San Francisco

    Sunset

    California

    San Francisco

    Sunset

    California

    Boulder Creek

    Big Basin

    California

    Woodside

    Kings Mountain

    Florida

    Miami

    Beach

    Florida

    Orlando

    Disney

    Try this array formula**:
    =SUM(IF(FREQUENCY(MATCH(A2:A10B2:B10C2:C10,A2:A10B2:B10C2:C10,0),ROW(A2:A10)-ROW(A2)+1),1))
    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  • RELEVANCY SCORE 2.75

    DB:2.75:Unique Index pk


    TimesTen 11.2.1.4.0

    There is one cache table:

    Table ORACLE.GUY:
    Columns:
    *ID NUMBER NOT NULL
    NAME VARCHAR2 (50) INLINE NOT NULL
    CITY VARCHAR2 (50) INLINE
    Aging: LRU on

    1 table found.

    Add unique index
    create unique index name_index on oracle.guy(name);

    Execute sql query
    select * from oracle.guy where name = 'nesta'

    Error Message
    2609: Incompatible types found in expression
    Execution time (SQLPrepare) = 0.000559 seconds.
    The command failed.

    After I drop the unique index, it works well. And then I create a non-unique index, and it works too.

    Regards,
    Nesta

    DB:2.75:Unique Index pk

    Hi jspalmer,

    Yes, nothing missed.
    Thanks very much.

    Regards,
    Nesta

  • RELEVANCY SCORE 2.75

    DB:2.75:Distinct With Compound Key 3d


    Hi all,I have a compound key dimension with, say, unique id and data load date as the keys.  the unique id will appear on multiple load dates, you get the idea!  The unique id is the name attribute for members of the dimension.If i do a simple select for the count of rows by the unique ids, i get duplicate unique ids appearing, as should be expected since they're not strictly the same member.How do i tweak the query to count only distinct member names instead of distinct members?The current query is
    select

     

    [Measures].[Row Count] on columns,
    [Work Info].[Work Number].

    CHILDREN on rows
    from

     

    [Work Orders Cube]

    DB:2.75:Distinct With Compound Key 3d

    It sounds like you need to setup a distinct count measure using the unique_id column.http://geekswithblogs.net/darrengosbell - please mark correct answers

  • RELEVANCY SCORE 2.75

    DB:2.75:Is There A Limit To The Number Of Site Columns Which May Be Created? Sharepoint Foundation 1j


    Hello
    This site has 75 custom site columns and 15 list content types. The default values of the 75 columns must be different foreach content type. So the total number of site columns needed is 1125. The 15 content types are then added to a custom list.
    Are there any issues with creating this number of site columns? What about adding them to a list?
    Previously 75 columns have been added to 15 content types no problem. It is the requirement for unique column default values for eachcontent type which is driving this.

    DB:2.75:Is There A Limit To The Number Of Site Columns Which May Be Created? Sharepoint Foundation 1j

    Many thanks Chris
    I have read the boundaries and limits and as far as I can see the limit is 276 single line of text columns per list itembecause the limit per SharePoint list item is 8000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the
    actual limit is 276 Single line of text columns.. For other data types
    the number of columns per list item can be as low as 48 for Date/Time or Calculated Columns.
    I may need to re-design this SharePoint application.

  • RELEVANCY SCORE 2.75

    DB:2.75:Sql Statement For Composite Primary Key xz


    Hi,

    I need a SQL statement for finding the Distinct values for combination of 2 Columns( Composite Primary key). I dont want to use Data Dictionary views.

    Table A
    c1 c2
    1 1
    1 2
    2 3

    Now for the above table combination of c1 and c2 is unique.

    Please advice.

    Cheers
    Ramkannan.A

  • RELEVANCY SCORE 2.74

    DB:2.74:Unique Order Number kz



    Hi:

    I have got Four Columns :

    Item_Number

    Vendor_Number

    Order_Date

    Order_Number

    There could be several Vedors for each Item_Number and Several Order_Dates related to each vendor and item,

    my report requires picking up the latest order number for each vedor related to a part based on the latest order date.

    Appreciate your help.

    thanks

    Item_NumberVendor_NumberOrder_DateOrder_NumberA666201201019A6662013010110A7772012010111A7772013010112B6662012010180B6662013010144B9992012010122B9992013010169RequiredItem_NumberVendorMax DateLatest PO (Based on Max Date Vedor Item Number)A6662013010110A7772013010112B6662013010144B9992013010169

    DB:2.74:Unique Order Number kz


    Hi,

    The firstsoretedvalue function should work for this. Try something like:

    firstsortedvalue(Order_Number, - Order_Date)

    Best,

    Matt

  • RELEVANCY SCORE 2.74

    DB:2.74:Confirming The Correct Use Of Distinct On Multiple Columns. 1a


    Hi,

    I have a table that consists of 6 child keys to other tables. I want each row to have a unique combination of values. To check this I have formed the following query.

    SELECT DISTINCT(col_1), (col_2), (col_3), (col_4), (col_5), (col_6) FROM tableName

    Is this the correct syntax for finding unique combinations, it seems to be returning the correct answer for me.

    Now I would like to find out if there are any offending records so that I can remove them, I was thinking of using an outer join to do this, but the only problem is I would have to introduce the primary key for the table which then makes it automatically a unique combination.

    Would anyone have some ideas.

    Thank You
    Ben

    DB:2.74:Confirming The Correct Use Of Distinct On Multiple Columns. 1a

    Thank You for your response and appreciate the solution you provided.
    Ben

  • RELEVANCY SCORE 2.74

    DB:2.74:Finding Number Of Columns Count In Flat File dj


    Hi,
    I've two flat files those files having relation (Primary and Forregin key relation ) at finally I want to find out the number of columns count in both the flat files.
    can any one help about this requirement.

    DB:2.74:Finding Number Of Columns Count In Flat File dj


    1)There is an option to loop through the columns in the source using script component using script which I dont have it handy now but can get it sooner or later.
    2)A counter can be incremented for each iteration in the columns loop which can generate the column number.

    Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

    You can use reflection in a Script component to loop through the columns.... but that's for each row...

    Please mark the post as answered if it answers your question | My SSIS Blog:
    http://microsoft-ssis.blogspot.com

  • RELEVANCY SCORE 2.73

    DB:2.73:Proc Report Across Columns 1p



    I have code which produces a report where the number of columns may vary based upon the dataset using the ACROSS specification. Column grand totals are stored in unique macro variables GT_1 .... GT_x for as many columns as exist in the data. My question is, with a varying number of columns, is there any way to place the GT_x values above those columns (e.g., (N = 234), etc.)?

    DB:2.73:Proc Report Across Columns 1p


    Hi:

    It is possible to do you you want because you can nest ACROSS variables under other ACROSS variables in order to get another level of variable -- which gives you another level of header. The fact that you are creating macro variables suggests that you have some degree of macro knowledge. Possibly something like a user-defined format might work, using CNTLIN, as already suggested.

    Without more information from you, like seeing your existing code or getting an idea of your data in more detail, it's hard to provide specific advice.

    Here's a very simple example, that uses a DATA step program to make the "extra" header variable from a macro variable and then uses the NOCOMPLETECOLS option of PROC REPORT to get only 1 header above every unique value of AGE. See attached screenshot.

    cynthia

  • RELEVANCY SCORE 2.73

    DB:2.73:Normalization Doubt d9


    Hi ,

    I have table with columns as below,

    MfgId (number),MfgName (char(10),MfgPhone (number),MfgType (char(10), PartTypeId (number),
    PartNumber (number), PartName (char(10)),Price (number),Qty (number);

    As per the above table, data is duplicating for the first 5 fields and data in all other columns are unique.

    But the Query on this table is only using the MfgId and PartTypeId from the first 5 columns. with this keys and PartNumber each records can be uniquely identified on this table.

    So do you think any normalization required for this table, whether i have keep the first 5 fields in a seperate table and create a relationship to the table wich has the other columns.

    Then i need to include join in the query. I feel that will make the query slow. Could anybody suggest the usual procedure in this scenario.

    thanks a lot

    DB:2.73:Normalization Doubt d9

    Thanks to all for the opinions and suggestions, i would do that.

  • RELEVANCY SCORE 2.73

    DB:2.73:Pl/Sql 7f


    hi can anybody advise me how to start this problem

    - insert into the RESULTS table each customer id, and the number of unique products purchased by that customer.
    - update the recently_purchased column of the customer table to 'Y' (yes if they have purchased a product in the

    last 12 months) or 'N' (if they have not purchased a product in the last 12 months).

    Listed below are the tables definitions:

    table: CUSTOMER
    columns:customer_id NUMBER,customer_name VARCHAR2(100),recently_purchased VARCHAR2(1) -- 'Y' or 'N'

    table: CUST_PRODUCTS
    columns:product_id NUMBER,
    customer_id NUMBER,
    date_purchased DATE

    table: RESULTS
    columns: customer_id NUMBER, product_count NUMBER

    table: PRODUCTS
    column: product_id NUMBER,product_name VARCHAR2(100),

    DB:2.73:Pl/Sql 7f

    Yes Venky.. :)
    When I was typing, then he has posted just some times b'fore. So I was not aware of his reply... :)

  • RELEVANCY SCORE 2.73

    DB:2.73:Convert A Text String Into Number 1j


    I have two queries:
    1. I have two col. namely folion_no and scheme name. But there are more than one schemes registered under one folio. A scheme name is as long as 25 characters. I want to convert these scheme names which are text into numbers for some purpose. The conversion
    should be unique. The literally same scheme should be have unique value. If I change a single character of the scheme name, the value should change.

    2. I have two sheets. Sheet1 is having col. 1,2,3,4,5,6 and Sheet2 is having 3,2,4,1,6,5,7,8. Actually there are 30 columns. How can I re-arrange the columns of sheet2 as 1,2,3,4,5,6,7,8..... AT ONE GO. In some applications, we canre-arrange the columns where
    we want to by holding Column Header. Is there any such facility in Excel, 2003 or Excel, 2007?

    DB:2.73:Convert A Text String Into Number 1j

    Either of the two other replies answer your question 1.
    For part 2, I am assuming that you headers are in row 1.
    Insert a new Row 1 on Sheet2, and in cell A1, enter the formula
    =MATCH(A2,Shhet1!A:A,False)
    And copy across row 1 to match the headers on Sheet2.
    These formulas will return the column number for the header on Sheet1. Then sort Sheet2 horizontally based on row 1 ascending, and delete row 1 when you are done.
    Bernie

  • RELEVANCY SCORE 2.73

    DB:2.73:Question About Unique Index fp


    Given a table with 2 columns. They are not setup as UNIQUE.

    TABLE_A (
    column1 NUMBER NOT NULL,
    column2 NUMBER
    )

    Then, a UNIQUE INDEX is created for both of the columns

    CREATE UNIQUE INDEX XX.TABLEA_U ON TABLE_A
    (column1, column2)

    My question is does the above UNIQUE INDEX somehow act as a UNIQUE Constraint to "column1"?

    Thanks in Advance

    DB:2.73:Question About Unique Index fp

    It acts as a unique constraint on both the columns.If duplicate row is inserted you will get error like

    05:40:30 sqlcreate unique index u_a on a (column1,column2);

    05:41:07 sqlinsert into a values (1,2);

    1 row created.

    Elapsed: 00:00:00.00
    05:41:12 sqlinsert into a values (1,2);
    insert into a values (1,2)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SYSADM.U_A) violated
    05:41:15 sqlinsert into a values (1,3);

    1 row created.

    Elapsed: 00:00:00.00Anand

    Edited by: Anand... on Nov 5, 2009 5:41 AM

  • RELEVANCY SCORE 2.72

    DB:2.72:How To Create An Ordered List With Unique Priority Numbers sj


    Our Engineering groups wants to be able to edit and re-order a list of tasks based on priority. However, we need each item to have a unique priority number and preferably be chosen from a drop-down menu. It would look just like the method used to edit an existing list where you pick the number of each column and it automatically resorts other affected columns (i.e. you move column # 5 to # 3 and that affects # 3 on down as well).

  • RELEVANCY SCORE 2.72

    DB:2.72:Sql Help? 87


    I have 2 columns in my source reference number and guid.guid column data type is unique identifier.
    Suppose the user have same unique identifier with same reference number or different reference number I need to load the only any of one reference number and unique id please help me in that select statement.
    Data in table like below.
    15314205
    B8CC6110-DD98-4DD1-8567-000E000F7DF0
    15294957
    B8CC6110-DD98-4DD1-8567-000E000F7DF0
    15295125
    B8CC6110-DD98-4DD1-8567-000E000F7DF0
    15372381
    0C7EDF0B-C268-419D-9250-000A21FAF03B
    15428331
    0C7EDF0B-C268-419D-9250-000A21FAF03B
    Desire Output
    15314205
    B8CC6110-DD98-4DD1-8567-000E000F7DF0
    15372381
    0C7EDF0B-C268-419D-9250-000A21FAF03B
    Or
    15294957
    B8CC6110-DD98-4DD1-8567-000E000F7DF0
    15372381
    0C7EDF0B-C268-419D-9250-000A21FAF03B

    DB:2.72:Sql Help? 87

    Try
    with set1 as
    (select guid, [reference_number], row_number() over (partition by [reference_number] order by guid) rn
    from tab1)
    select guid,[reference_number]
    from set1
    where rn=1;

    or
    with set1 as
    (select guid, [reference_number], row_number() over (partition by [reference_number] order by guid desc) rn
    from tab1)
    select guid,[reference_number]
    from set1
    where rn=1;

    Many Thanks Best Regards, Hua Min

  • RELEVANCY SCORE 2.72

    DB:2.72:Excel 2007 - Trying To Make 2 Unique Columns Into 1, All Values Preserved mj


    Hi All

    What I'm trying to do feels really simple, but I haven't found a way of doing it yet! I have 2 columns each containing a number of unique values, and I want to have a way of combining these into 1 column. I realise from my research that I'm probably not using
    the correct term for what I want to do; I'm certainly not merging the data, possibly I'm combining or consolidating it. Basically:

    Column A Column B
    1 abc def
    2 xyz ghi

    goes into

    Column C
    1 abc
    2 xyz
    3 def
    4 ghi

    Does this make sense?

    Many thanks,
    Paul.

    DB:2.72:Excel 2007 - Trying To Make 2 Unique Columns Into 1, All Values Preserved mj

    That's perfect as it allows for C and D to be empty, which is how the report will be. Once again thanks for your help.

  • RELEVANCY SCORE 2.72

    DB:2.72:Finding Unique Combination Of Fields? xa


    My table, say A has 200 columns. I am doing some permutation and combination and using some random columns with distinct clause, but not able to find uniqure combination. Is there any way, I can find unique combination?

    DB:2.72:Finding Unique Combination Of Fields? xa

    Hi,
    A skeletonn code which will reduce your 200! less by 200 which is 2,666,600.00 -200= 2,666,400.00 :)

    You may want to generalize it and work on it.
    Remember one big challenge which you will face is NULL values.
    every NULL will be treated as a distinct value.
    Also with DBMS_OUTPUT.PUT_LINE the max number of characters is 255, so you may ahve to write a fuinction to
    slice it nicely :)
    You got to do the major work now ;)

    -- Created on 4/8/2010 by USER
    declare
    i integer :=1;
    cnt number;
    var_cnt number;
    tab_row_cnt number;
    var_table_name varchar2(10):=' from EMP';
    cursor tab_col is
    select column_name from all_tab_columns where table_name='EMP' and owner='SCOTT';
    qur varchar2(1000) := 'select distinct ';
    nvl_clause_start varchar2(5):=' nvl(';
    nvl_clause_end varchar2(10) :=',), ';
    begin
    select count(0) into cnt from all_tab_columns where table_name='EMP' and owner='SCOTT';
    execute immediate 'select count(0) '||var_table_name into var_cnt;
    dbms_output.put_line (var_cnt);
    for cur_tab_col in tab_col loop
    qur := qur||cur_tab_col.column_name||', ';
    execute immediate 'select count(0) from ( '||substr(qur,1,instr(qur,',',-1)-1)||' '||var_table_name||' )' into tab_row_cnt;
    i:=i+1;
    if var_cnt =tab_row_cnt then
    dbms_output.put_line(replace(substr(qur,1,instr(qur,',',-1)-1),'select distinct ',' ') ||' IS UNIQUE');
    end if;
    end loop;

    end;Cheers!!!
    Bhushan

  • RELEVANCY SCORE 2.72

    DB:2.72:Need Help Please Obtaining Unique Values And Corresponding Totals From Multiple Sheets. kf


    Hello,
    I have an Excel 2007 workbook containing 12 sheets called Jan-11 to Dec-11.
    Each sheet has two columns, Sender e-mail address (column A) and Number of sent messages (column B).
    I've added a sheet called Totals and I want to insert a list of the unique Sender e-mail address values from the 12 sheets along with the sum of their corresponding Number of sent messages.
    Can anyone please suggest how to do that?
    Thanks,
    - Alan.

  • RELEVANCY SCORE 2.72

    DB:2.72:How To Assign A Unique Number To The Name Column Of A Document Set? ca


    How can I assign a unique number to the name column of a document setpreferablywith workflows? (Perhaps this number can increase by one each time a new document set is created)
    When a user attempts to create a new document set, this unique number should be already there as the name of the document set.
    (However, It seems that access and edit the Name column is more tricky than the other columns)

    DB:2.72:How To Assign A Unique Number To The Name Column Of A Document Set? ca

    I have similar request regarding document set SP2013
    Doc set is created to keep personal files.
    How to configure doc set to hold following attributes:
    PersonalID: Unique
    Name: not showing when new doc set is created or not need to be unique
    So when new doc set is created user will be prompted for 2 values:
    PersonalID(unique) and First Last name(Not Uniqe, required)

    Ulee

  • RELEVANCY SCORE 2.72

    DB:2.72:Projects-Outline Number c8


    Can anybody help me find the table and column from which the
    outline number of each task is populated from in projects search screen.

    I need to print a similar report using those columns.

    Else guide me in finding the record history in any JSP page.

    DB:2.72:Projects-Outline Number c8

    Can anybody help me find the table and column from which the
    outline number of each task is populated from in projects search screen.

    I need to print a similar report using those columns.

    Else guide me in finding the record history in any JSP page.

  • RELEVANCY SCORE 2.72

    DB:2.72:Unique Key 3m


    I have a table like emp with columns (sl_no, emp_id, emp_name,dept_id).Serial number should be unique. And all the other values can be repeated. But I shouldn't have same values again for all the three columns.

    i.e. this can be allowed
    1, 5000, Emp1, Dept1
    2, 5000, Emp1, Dept2this shouldn't be allowed
    1, 5000, Emp1, Dept1
    2, 5000, Emp1, Dept1Do I have any other option other than the below code.
    ALTER TABLE emp
    add CONSTRAINT emp_unq UNIQUE (emp_id,emp_name,dept_id);If No, then Can I use this for 30 columns..?

  • RELEVANCY SCORE 2.72

    DB:2.72:Combination Of List Columns As Unique Values- List Settings 18


    Hi,
    I'll find that there is option in list column settings as Enforce unique values, but its applicable for single column only. I need to make combinations of list columns as unique.
    Howmake two or more columns as unique in sharepoint 2010 list?
    Anandhan.S

    DB:2.72:Combination Of List Columns As Unique Values- List Settings 18

    also please take a look at my reply at
    http://social.msdn.microsoft.com/Forums/en/sharepoint2010general/thread/394a345f-a44b-421d-8e7f-1366d396216c

  • RELEVANCY SCORE 2.72

    DB:2.72:Finding Unique Values In Only One Column xs


    I am using Access 2007 and I have 10 columns of data.I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.I have used this SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_LoanFROM [SFR Rates All];But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.  I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.I know that I am missing something basic but well, I can't figure it out.ThanksBob

    DB:2.72:Finding Unique Values In Only One Column xs

    Hi Carol,
    If you are on SQL Server 2005 or above, you can use this:
    WITH NumberedRows
    AS (SELECT *, rn AS ROW_NUMBER() OVER (PARTITION BY Sorter
    ORDER BY SProgram)
    FROM YourTable)
    DELETE FROM NumberedRows
    WHERE rn = 2;
    This will keep the row with the alphabetically first value of
    SProgram, and in case of ties it will just pick one at random.
    Do try in a test environment first!
    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

  • RELEVANCY SCORE 2.71

    DB:2.71:Random Unique Number cf


    i want to generate random unique number of lenghth.how to create it

    DB:2.71:Random Unique Number cf

    Duplicate Thread:
    random number generation

  • RELEVANCY SCORE 2.71

    DB:2.71:How Do I Sort A Column And Retain The Relationship To Other Data In The Spreadsheet? cz


    Maybe that's not the best way to express the problem ...I have a spredsheet with two columns and 2390 rows. Column 1 is a "record number" and Column 2 is a unique data pointWhen I sort by column 2 Column one stays static (e.g., the record number doesn't "follow" the unique data point.I've tried sorting by by the "entire table" ... but it won't unless I pick a column ... and then we end up with the problem I've described.Any suggestions?Thanks for your assistance!Bill ArnoldEmail Edited by Host

    DB:2.71:How Do I Sort A Column And Retain The Relationship To Other Data In The Spreadsheet? cz

    John:Please forgive the ignorance of those of us that use spreadsheets to do simple equations (and substitue for a dead simple database). I'm sure that to you the answer to my question was on the order of "DUH" ... You saved me a ton of hassel and a couple of hours of time .. Thank you for that!!Bill Arnoldtucsonbil@cox.net

  • RELEVANCY SCORE 2.71

    DB:2.71:Table Break Down With Unique Id Using Scd pm


    Hello All,I have one problem to find the solution for. As i have mentioned in this threadhttp://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/823825cd-1a2b-4527-a9bb-e804103fa92fI have one source file in which i have 1102 columns. I have divided this into four destination tables because of SQL server table size limit is 1024 columns per table.Table 1 = have columns from 0 t0 365 (unique id from source file is there in all four tables.)Table 2 = have columns from 366 - 732Table 3 = have columns from 733 - 892Table 4 = have columns from 893 - 1102Now let suppose there are 10 records in the source file, all columns are set to type 2 change by using SCD in each destination table.From column0 - 365 table12 recordshave changed against unique ID. so in table 1 after transformation total records will be 12. in which 10 records will be with active status and2 will be with expired statusFrom column 365 - 732 table 2, there is no change in these columns, so total number of records will remain 10 with active statusFrom column 733 - 892. 5 records have changed, so there will be total 15 records in which 5 records will be expired and 10 records will be there with active statusFrom column 892 - 1102 there is no change so the number of records will remain 10 with active status.Now all four tables will be join with Unique ID and status is Active from all four table.My question is would it be a right approach. I am just wondering in some situation the number of active and expired records might change. when we will combine all tables with unique ID,is there any possiblity to get wrong records?Any suggestions for this please or any better approach?

    DB:2.71:Table Break Down With Unique Id Using Scd pm

    Thank you so much Todd for your suggestions it is really helpful. I will do it a thorough study and divide it accordingly. I will give a shout again if i will face any problem. Thanks Alot.

  • RELEVANCY SCORE 2.71

    DB:2.71:Finding Frequency Of Visits By Customers dc


    Hi,
    I have a transactions tables which contains a lot of columns, but CUSTOMER_ID (ID of unique customer), RECEIPT_NO (distinct for each visit of each customer. It means that each distinct visit of a customer will have a distinct receipt number),TRANSACTION_DATE
    (date when transaction was made),ITEM_CODE (item code) are the columns which are to be used for the scenario I want to discus here. Form this table, the criteria to fetch the data is for past 3 months

    1. I want to find out the customer who bought an item (suppose item_code 'AAA') and then he again bought the same item within next 15 days of his first purchase of this item. (During last 3 months)
    Intention is to find out customer who is frequent buyer of this item.
    2. I also want to know what are other items purchased by the customer in same visit (RECEIPT_NO) when he bough this 'AAA' item. During last 3 months
    This will help us to find out the items which customers buy usually with this item 'AAA'.
    Thanks in advance

    Salman

    Salman

    DB:2.71:Finding Frequency Of Visits By Customers dc

    Hi Salman,

    Please refer to the following codes. Additionally, please provide your table definition codes and some sample codes here for analysis.
    create table Test1
    (
    customerid int,
    item varchar(20),
    purcdate datetime
    )

    --truncate table test1;
    insert into Test1 values (1,'aaa',GETDATE());
    insert into Test1 values (1,'aaa',DATEADD(day,-14,GETDATE()));
    insert into Test1 values (2,'bbb',GETDATE());
    insert into Test1 values (1,'bbb',DATEADD(day,-14,GETDATE()));

    select t1.customerid from
    test1 t1 inner join test1 t2
    on t1.customerid = t2.customerid
    and t1.item = t2.item
    and DATEADD(month,3,t1.purcdate)GETDATE()
    and
    (
    (DATEADD(day,15,t1.purcdate)t2.purcdate
    and t2.purcdate t1.purcdate
    )
    or
    (DATEADD(day,15,t1.purcdate)t2.purcdate
    and t2.purcdate t1.purcdate
    )
    )

    select distinct item
    from test1
    where customerid in
    (

    select t1.customerid from
    test1 t1 inner join test1 t2
    on t1.customerid = t2.customerid
    and t1.item = t2.item
    and DATEADD(month,3,t1.purcdate)GETDATE()
    and
    (
    (DATEADD(day,15,t1.purcdate)t2.purcdate
    and t2.purcdate t1.purcdate
    )
    or
    (DATEADD(day,15,t1.purcdate)t2.purcdate
    and t2.purcdate t1.purcdate
    )
    )

    )

    Allen Li
    TechNet Community Support

  • RELEVANCY SCORE 2.71

    DB:2.71:Counting Unique Values Based On 2 Columns 7d


    I have been struggling all day to find the correct formula to use when trying to count unique values using different columns. I have a multiple column spreadsheet and i would like to identify the number of unique values (text alpha) in column D (job
    #) that has a specific status which is in column F. For example, see below, the working status has 3 unique job#'s. Can someone help please? Thank you

    Job# Status
    100 Initial
    201 Working
    634 Working
    201 Working
    978 Completed
    978 Working

  • RELEVANCY SCORE 2.70

    DB:2.70:Create Number Generation System In Sharepoint 2010 pa


    Hi,

    I need help with generating sequential numbers in a sharepoint list.
    The list has three custom columns implemented as drop down menus.
    If the user selects all options within the three columns above, a unique number is generated.

    I have little programming experience thus i need to do this in SPD 2010

    DB:2.70:Create Number Generation System In Sharepoint 2010 pa

    Hi Lily,

    I found a free wsp file that creates a custom autogen column in sharepoint lists and libraries.

    With this tool i've been able to concatentatedata from other ist columnswith theincrementing ID column thussolvingmy problem.

    Thanks for the response.Regards Rotimi Fagbenro

  • RELEVANCY SCORE 2.70

    DB:2.70:Ajax Display Problem - Number Widths x7



    Hi Guys,

    I'm finding AJAX a pain with regard to number widths in expression columns. It seems to display a max width of 6 characters before giving up and displaying '....'. Please see the picture below.

    I'm sure there is a straight forward setting somewhere, can you please advise me which one?

    Thank you!

    DB:2.70:Ajax Display Problem - Number Widths x7


    I reported this bug a while ago and they told me it would be fixed in SR3, but I haven't confirmed it yet.

  • RELEVANCY SCORE 2.70

    DB:2.70:Need Help On Finding Uniquq Key(Sim Number / Geo Info / Emei Number )For Device Or Simulator fp



    HelloI am trying to get a Unique key for device and wanted to fetch " Geo location , SIM number. and EMEI number from device simulator"I am using 9800 BlackBerry Simulator.Please let me know how to fetch those inforamtion thorugh code.Even If I get How to fetch any one of these , Please let me know ..I appreciate your quick respose .Thank you.

    DB:2.70:Need Help On Finding Uniquq Key(Sim Number / Geo Info / Emei Number )For Device Or Simulator fp


    I think EMEI = IMEI... if so den..

    String imei = new String(GPRSInfo.imeiToString(GPRSInfo.getIMEI()));

  • RELEVANCY SCORE 2.70

    DB:2.70:2 Columns, 3 Tables, Semi-Conguent Data 3j


    i have 3 tables (weekly reports),they have employee names (vary by week) in one column, and a column of times (in minutes).i would like to have a formula finding unique names in the first column and listing them in a new table as well as bring the correlated data from the time column.see attached for referenceany help is much appreciated.

    DB:2.70:2 Columns, 3 Tables, Semi-Conguent Data 3j

    Barry,I see you resisted the temptation to inform the OP that while there are nine distinct names in the data, Name 9 us the only Unique one.Jerry

  • RELEVANCY SCORE 2.70

    DB:2.70:Count Unique Values Among Duplicates In A Table 38


    The formulas in "Count the number of unique values by using functions" work great as long as I only use them on a single column.
    My problem is that my dupliacte values apear in different columns and I want to know how many unique values I have in theentier table.
    For example:

    111-58
    987-52
    456-89

    658-82
    695-88
    111-58

    998-11
    658-82
    258-44

    This is a simplified table, mine is much larger and more complecated. I can not sote the data as numbers as I need them in this specific format. I also can't use the filter option (and it didn't really work for me, either).
    How can I count the number of unique values in this table (7)?

    Any ideas?

    Thanks!

    DB:2.70:Count Unique Values Among Duplicates In A Table 38

    Count the number of values with COUNTA() like this,

  • RELEVANCY SCORE 2.70

    DB:2.70:Alternate Way Of Finding The Windows Reference Number jf


    Hello, I am using some of the Windows Utilities VIs and I have run into a snag. In my application I lauch multiple template VIs and hence have many Windows that have the same name. This causes a bit of confusion when using a VI like "Flash Title Bar" which takes in a window's name to determine it's reference number. MY question is, is there an alternative method of finding out a window's reference number without having to supply the window's name? Something that determines each window's unique number. I checked various Application and VI Properties without much luck. Perhaps is there some way of converting VI reference to Windows Reference. I dunno. Any ideas???Thanks fellow LabVIEWers,Glen D.

    DB:2.70:Alternate Way Of Finding The Windows Reference Number jf

    Here is an unlocked version of the Get Windows Refnum vi along with the Window Refnum control.



    - tbobInventor of the WORM Global

  • RELEVANCY SCORE 2.70

    DB:2.70:Search In Multiple Tabs For Unique Combination Of 2 Cells p9


    Hi,

    I would like to write a code that returnsvalue in the cell that is a few colums next to a unique combination of 2 cells.
    For example you have 3 columns in tab1
    NUMBER |NAME| PRICE

    and you have multiple columns in tabs 1 to 50 with
    ORDERNR. | NUMBER | NAME | QUANTITY | PRICE

    In tab 1, number and name are filled in but we need to find the price in the tabs.
    Nor number, nor name are unique, only the combination is.
    So I need to do a search in the tabs to get the price, it's like a vertical search but with two values, multiple tabs.

    DB:2.70:Search In Multiple Tabs For Unique Combination Of 2 Cells p9

    Hi Piri10,
    Since the orignal issue was resolved, I marked the helpful replies to close this thread. If you have other questions, please feel free to reopen a new thread. It will be helpful for other communities who have the same issue to find the answer quickly.
    Best regards
    FeiWe are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

    Click
    HERE to participate the survey.

  • RELEVANCY SCORE 2.69

    DB:2.69:Select Distinct From Vo To Populate Lovs For Query sp


    I have one table that has columns including buyer number, buyer name, supplier number, supplier name, and PO number. I want to have a query panel that uses multi-select dropdowns populated with the unique values for each of these columns from the table, with the additional constraint of only showing data that matches the user's allowed supplier numbers. The supplier numbers are stored in the session when the user goes through single sign on.What is the best way to implement this?

    DB:2.69:Select Distinct From Vo To Populate Lovs For Query sp

    I am using JDeveloper 12c.How should the values be passed?Currently I have an entity based VO for the table, and a query based VO for the other information, using SELECT DISTINCT info FROM thetable.Are you saying that I should only have 2 VOs, the entity based table VO and another VO that filters by supplier?

  • RELEVANCY SCORE 2.69

    DB:2.69:Re: Generating Unique Number On Columns. fm


    Narendar.Vishwanatham wrote:
    Its not workingShow the output

    DB:2.69:Re: Generating Unique Number On Columns. fm

    hi

    please check this query,

    pre

    SELECT DISTINCT A.LKP_IDAS ID
    ,D.PART_CNDTN_CDas CONDITION
    , D.PART_DISPOSTN_CD AS DISPOSITION
    , C.LKP_NM AS DESCRIPTION
    FROMFWOWNER.CLM_LKP_VALUEA
    ,FWOWNER.CLM_LKP_USAGEB
    ,FWOWNER.CLM_LKP_VALUE_LANG C
    ,IWOWNER.WC_CLM_SRVC_PARTD
    WHEREB.LKP_USED_ASIN ( 'RR_CAUSAL_PART_CNDTN_CODE', 'rr_parts_disposition')
    ANDA.LKP_REFBETWEENB.LKP_REFANDB.LKP_REF + '.99999'
    andA.LKP_ID= C.LKP_ID
    ANDC.LANG_CD= 'ENG_USA'
    ANDA.LKP_LVL= 3
    and A.LKP_VALUE= CASE
    WHEN B.LKP_USED_ASIN ( 'RR_CAUSAL_PART_CNDTN_CODE')
    THEN D.PART_CNDTN_CD
    WHEN B.LKP_USED_ASIN ('rr_parts_disposition')
    THEN D.PART_DISPOSTN_CD
    END

    /pre

    above query letting me the outputs as,

    pre
    ID CODE DISPO DESCRIPTION
    ----- ------- ---------- ---------------------------------
    25774 CP07 OVERHAULED Calibration Bad
    25789 CP22 (null) Leaking
    25795 CP28 Scarred/Spalled
    25796 CP29 (null) Warped/Distorted/Buckled
    25768 CP01 OVERHAULED Bent/Creased/Fold/Kinked/Crinkled
    25777 CP10 Chipped
    25779 CP12 Contamination
    25786 CP19 (null) Grooved/Furrowed
    25793 CP26 (null) Pitted
    25768 CP01 (null) Bent/Creased/Fold/Kinked/Crinkled
    25777 CP10 (null) Chipped
    25778 CP11 (null) Circuit Bad
    25783 CP16 (null) Dented
    25790 CP23 (null) Obsolete/Supercede
    25797 CP30 (null) Worn Excessively
    25798 CP31 (null) Wrong Part
    *25799 CP32 (null) Other*
    *25799 CP32 Other*
    28225 CP07 OVERHAULED OVERHAULED
    25771 CP04 (null) Broken/Fractured
    25779 CP12 (null) Contamination
    25780 CP13 (null) Corroded/Eroded
    25781 CP14 (null) Cracked
    25785 CP18 (null) Galled/Fretted
    25788 CP21 (null) Item Defect
    25795 CP28 (null) Scarred/Spalled
    25797 CP30 Worn Excessively
    28225 CP01 OVERHAULED OVERHAULED

    28 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

    /pre

    please check bold part i am getting two duplicate values with difference of NULL and blank space i would like to generate a unique number on CONDITION and Dispostion column how to do that.

    (please check my previous thread for reference
    MERGE TWO COLUMNS ?
    )

  • RELEVANCY SCORE 2.69

    DB:2.69:Unique Constraint 11


    I have 10 parallel process running and would insert to a table with two columns, one column (Random number) having a unique constraint.
    There are chances of getting same random number in all eight processes. What if more than one (to my bad luck might be all eight instances) process tries to insert the values ?
    I assume only one would be successful? Could anyone help me confirm this...

    Thanks in advance...
    Raghu

    DB:2.69:Unique Constraint 11

    Thanks for the quick response.

    We are executing the same process in different environments parallely. Two machines and five instances each. Based on the timestamp, we would get duplicate values. And several processes might try to insert at the same second or even milli second. hence wanted to confirm.

  • RELEVANCY SCORE 2.68

    DB:2.68:Does Oracle 10g Use Multiple Indexes For Query 71


    using Oracle 10g EE

    When I'm querying a table, as described below, using COLUMN_1 and COLUMN_2 in where clause, does Oracle somehow use both indexes efficiently for lookup? Or must I define a composite index including both columns; e.g., 'CREATE UNIQUE INDEX EXAMPLE_UK2 ON EXAMPLE (COLUMN_1, COLUMN_2);' ?

    Thank you.

    CREATE TABLE EXAMPLE (
    COLUMN_1 NUMBER(10,0) NOT NULL,
    COLUMN_2 NUMBER(10,0) NOT NULL,
    COLUMN_3 VARCHAR(20),
    CONSTRAINT EXAMPLE_PK PRIMARY KEY (COLUMN_1)
    );

    CREATE UNIQUE INDEX EXAMPLE_UK2 ON EXAMPLE (COLUMN_2);

    DB:2.68:Does Oracle 10g Use Multiple Indexes For Query 71

    The answer is not as simple as one could imagine because there are many possibilities regarding data distribution and what kind of queries will be performed. Oracle can do an index join operation on less selective indexes. Consider this plan:

    SQL explain plan for
    2 select count(*) from test_small
    3 where n1=1
    4 and n3=2;

    ---------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ---------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 4 | 42 (5)|
    | 1 | SORT AGGREGATE | | 1 | 4 | |
    |* 2 | VIEW | index$_join$_001 | 333 | 1332 | 42 (5)|
    |* 3 | HASH JOIN | | | | |
    |* 4 | INDEX RANGE SCAN | TEST_SMALL_I1 | 333 | 1332 | 11 (19)|
    |* 5 | INDEX FAST FULL SCAN| TEST_SMALL_12 | 333 | 1332 | 32 (4)|
    ---------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - filter("N3"=2 AND "N1"=1)
    3 - access(ROWID=ROWID)
    4 - access("N1"=1)
    5 - filter("N3"=2)The index join operation in this case is done under step3 by performing a hash join of ROWIDs. One can spot this kind of operation by typical name index$_join$_nnn - like in this case index$_join$_001 what means the first index join operation in this plan
    The CBO decides to use index join when the predicates are not very selective and using only one index followed by table access by ROWID would cost more than first joining another index and only then access table data or as in the above case just perform the SORT AGGREGATE operation which really is the count operation.
    But in your case if you would frequently query data using bost columns in the "where clause" then you should create and index with concatenated key (COLUMN_1, COLUMN_2).
    I would suggest you to visit Richard Foote's blog to get more insight about this topic - http://richardfoote.wordpress.com/

    HTH, Joze
    Co-author of the forthcoming book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680

    Oracle related blog: http://joze-senegacnik.blogspot.com/
    Blog about flying: http://jsenegacnik.blogspot.com/
    Blog about Building Ovens, Baking and Cooking: http://senegacnik.blogspot.com

  • RELEVANCY SCORE 2.68

    DB:2.68:Calculating The Percentage Of Active Records Using An Unique Select - Sql Server 2012 1f


    Hi,
    I need to determine the occurrence of active records fe in a Customer table respect to the total number of customers.
    So, I've an Active flag in Customer table with these columns: CustID, CustomerName, Active. I need to determine the rate between the number of active customers and the total number of customers.
    Any ideas, please?
    Thanks

    DB:2.68:Calculating The Percentage Of Active Records Using An Unique Select - Sql Server 2012 1f


    So, I've an Active flag in Customer table with these columns: CustID, CustomerName, Active. I need to determine the rate between the number of active customers and the total number of customers.
    Any ideas, please?

    One method:
    SELECT
    SUM(CASE WHEN Active = 1 THEN 0 ELSE 1.0 END)/ COUNT(*) AS PercentActive
    ,SUM(CASE WHEN Active = 0 THEN 0 ELSE 1.0 END)/ COUNT(*) AS PercentInactive
    FROM dbo.Customer;
    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

  • RELEVANCY SCORE 2.68

    DB:2.68:Max Primary Columns cm


    Hi,

    I'm trying to increase the number of columns for primary keys since the max number is 32 but I have no luck finding documents about what change needs to be made from the database setting to achieve this goal. Can someone point me to some possible solution.

    Thanks so much.
    PNT

    DB:2.68:Max Primary Columns cm

    user10693802 wrote:
    Hi,

    I'm trying to increase the number of columns for primary keys since the max number is 32 but I have no luck finding documents about what change needs to be made from the database setting to achieve this goal. Can someone point me to some possible solution.

    Thanks so much.
    PNTIf you are talking about a multiple-column PK, and you are even getting anywhere near the current max, I'd suggest that this alone is a huge red flag that you need to do some serious data normalization.

  • RELEVANCY SCORE 2.68

    DB:2.68:Unique Problem In Extended Data Table! a7



    HI ,

    I am facing a unique problem with Extended data table related to Rows and column.

    I have 30 to 35 columns (aprox) and when I starts adding rows after 10th row when I add next row , some crash happened inside and nothing works after that. app needs to close . only java script functionality works.

    The peculiar thing is when i decrease the number of columns to some extent , I am able to add more rows and it crashed after 13th or 14th.

    Is there any memory issue , i can't understand the relation ???

    Any help?

    Thanks in advance.

    Waquas Ahmad

    DB:2.68:Unique Problem In Extended Data Table! a7


    Can you post a simplified code sample demonstrating your problem?

  • RELEVANCY SCORE 2.67

    DB:2.67:How Do I Display The Column And Line Number Using Textbox? js


    I want to keep a label below the TextBox. I couldn't find any commands for finding out the columns number and the line number in the TextBox class. Any ideas how I can display the column and line number of the position of the cursor in the TextBox?

    DB:2.67:How Do I Display The Column And Line Number Using Textbox? js

    Actually...I found that out myself. With a little bit of patience and perseverance, I was able to do the same using RichTextBox in the same way as you guys have done - using the SelectStart property etc. Amazing how intuitive the .NET library is, don't you think? Thnx for replying anyway.

  • RELEVANCY SCORE 2.67

    DB:2.67:Scatter Chart Dot Colors Dimension Setup Not Working f7



    I have some data that I want to display in scatter chart.I have 2 dimensions - dimension1 - this is each record in the table - say unique id for each row. So the number of dots should be equal to number of records. - dimension1 - this is each record in the table - say unique id for each row. So the number of dots should be equal to number of records. - dimension2 - this is a combination of 2 columns. tp and vc. Colors of each dot is based on these 2 columns

    tp vc a 1 b 2 c 1

    So there will be dots of 3 colors based on the above tp and vc combinations

    Then there are 3 expressions representing X and Y and Size of dot

    I am not sure how to configure the dimensions to achieve the goal.

    Thanks

    DB:2.67:Scatter Chart Dot Colors Dimension Setup Not Working f7


    Thanks for your help Gysbert, I think this works for me, have to test with my data.

  • RELEVANCY SCORE 2.67

    DB:2.67:Maximum Number Of Columns In An Index a9


    I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?
     
    Thanks for any help on this.

    DB:2.67:Maximum Number Of Columns In An Index a9

    From Books Online, Topic: 'Create Index'
     
    Up to 16 columns can be combined into a single composite index key.
     
     

  • RELEVANCY SCORE 2.67

    DB:2.67:Fillschema Picks Too Many Primary Key Columns sf


    I don't know whether this is an ODP.NET error or a Microsoft error.

    Oracle9i Release 9.2.0.1.0
    ODP.NET 9.2.0.4
    .NET Framework 1.1

    Create a table with one primary key column and one unique column and name the primary key column like the unique column with the suffix "_ID":

    CREATE TABLE t_bib_uebertrag_kap(
    nbsp kapazitaet_id NUMBER(8) NOT NULL
    nbspnbsp CONSTRAINT pk_kapa primary key,
    nbsp kapazitaet VARCHAR2(15) NOT NULL
    nbspnbsp CONSTRAINT a_un_kapa unique,
    nbsp geschwindigkeit NUMBER(12) NOT NULL,
    nbsp bemerkung VARCHAR2(255)
    );

    After calling FillSchema on this table the PrimaryKey property of the DataTable contains 2 columns: KAPAZITAET_ID and KAPAZITAET.

    The same happens with other tables and similar column names

    R. Lthke

    DB:2.67:Fillschema Picks Too Many Primary Key Columns sf

    I don't know whether this is an ODP.NET error or a Microsoft error.

    Oracle9i Release 9.2.0.1.0
    ODP.NET 9.2.0.4
    .NET Framework 1.1

    Create a table with one primary key column and one unique column and name the primary key column like the unique column with the suffix "_ID":

    CREATE TABLE t_bib_uebertrag_kap(
    nbsp kapazitaet_id NUMBER(8) NOT NULL
    nbspnbsp CONSTRAINT pk_kapa primary key,
    nbsp kapazitaet VARCHAR2(15) NOT NULL
    nbspnbsp CONSTRAINT a_un_kapa unique,
    nbsp geschwindigkeit NUMBER(12) NOT NULL,
    nbsp bemerkung VARCHAR2(255)
    );

    After calling FillSchema on this table the PrimaryKey property of the DataTable contains 2 columns: KAPAZITAET_ID and KAPAZITAET.

    The same happens with other tables and similar column names

    R. Lthke

  • RELEVANCY SCORE 2.67

    DB:2.67:Create A Unique Project Id Number 1m


    I need to generate unique project numbers for projects and proposals in the following format; 2010-001, so fiscal year and - and a 3 character number. My idea was to build
    a lookup table and have 3 columns. [UniqueProjectID], [ReservedOn] and [ReservedBy]. Then I would insert 50 or so numbers in sequence and let PM select the unreserved numbers.

    I was hoping to get a little constructive criticism on my approach, or maybe a different method.
    Thanks for any input,
    Rick

    DB:2.67:Create A Unique Project Id Number 1m

    You might also want to look at this post:
    http://epmxperts.wordpress.com/2012/05/21/generate-unique-id-for-project-using-a-webpart/
    This technique enables you to do the generation in a different fashion and may be a better fit for your needs.
    Treb Gatte | @tgatte |
    http://AboutMSProject.com

  • RELEVANCY SCORE 2.67

    DB:2.67:Hp Laptop Chargers k8



    Each laptop computer requires a unique voltage, current and connector. Your first step in finding the correct HP laptop charger is to select your model or adapter part number below; or by using the Charger Select Technology� search tool.

    DB:2.67:Hp Laptop Chargers k8

    Jessie-Adapter wrote:
    Each laptop computer requires a unique voltage, current and connector. ...

    Hi,

    I don't think so, many models can share one PSU. Vendors may give different part number(s) but same volatge, same wattage, same tip/plug/connector. Some vendors use same model of PSU for their whole series and I can tell you who.

    Regards.

  • RELEVANCY SCORE 2.67

    DB:2.67:Different Surrogate Keys For Same Record? 7a


    Hello All,I have divided a huge table in two tables. In each table I have had a Primary Key (Set as identity) Incrementing by one each table. In the source file i have a unique identifier for each record.I have created one package, in which i am using the lookup transformation to check the duplicates. In lookup transformation i have used the following Available Input column = Unique IdentifierLookup operation = add a new columnOutput Alias = Unique_IdentifierThere are 100,000 records in source file. each records has unique identifier as a primary key. After all processing it is generating different Primary Key which i have set as Identity for the same unique identifier.for example if there are 10 columns in one table, and it has 100 records. I have divided this table into two tables. Table 1 = 5 columns, Table 2 = 5 columnsIn each table there is Primary key and unique identifier coming from source file. Against some records it is generating different ids. It should be same id against one records in two tables. I am not sure why it is happening any idea?Table1ID Unique IdentifierEmp.No Dept.No WeekID103352 RJZ01P7713409927873 992787 4724680024 5103403 RJZ01P7713409927872 992787 4724680024 5 94484 RJZ01P7713409927871 992787 4724680024 5Table2 Unique Identifer WeekID103352 RJZ01P7713409927873 5103403 RJZ01P7713409927872594483 RJZ01P7713409927871 5In this example, for record 3 it has generated different id in table 2. it is auto number int type increment by 1. Any idea why

    DB:2.67:Different Surrogate Keys For Same Record? 7a

    Thanks Jamie for your reply. I will use the unique identifier instead of IDs. Yes I am using SQL Server 2008. I have no idea about sparse column. I have done some research about it i will open a separte thread for that.Thank you so much for your help.

  • RELEVANCY SCORE 2.67

    DB:2.67:How To Get The Percentage For Each Unique Combinations Of Variables x3



    data work.data;

    input x $ y $ gender $;

    cards;

    1 1 F

    1 2 M

    2 1 F

    2 2 F

    1 2 M

    ;

    run;

    I want to use PROC SQL to create a table which has 4 columns: x, y, gender and percentage. The percentage represents: number of cases for each unique combination of x, y and gender, divided by the number of observations. So the result should look like:

    1 1 F 0.2

    1 2 M 0.4

    2 1 F 0.2

    2 2 F 0.2

    Thanks a lot.

    DB:2.67:How To Get The Percentage For Each Unique Combinations Of Variables x3


    Thanks, Cynthia, for the good examples of using the reporting procedures.

  • RELEVANCY SCORE 2.67

    DB:2.67:Manually Indexing Fks? aj


    According to "Maintaining Data Integrity Through Constraint":
    All enabled unique and primary keys require corresponding indexes. You should create these indexes by hand, rather than letting the database create them for you. Note that:

    Constraints use existing indexes where possible, rather than creating new ones.
    Unique and primary keys can use non-unique as well as unique indexes. They can even use just the first few columns of non-unique indexes.
    At most one unique or primary key can use each non-unique index.
    The column orders in the index and the constraint do not need to match.
    If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any catalog view.
    You should almost always index foreign keys, and the database does not do this for you.
    You should almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.

    What are the benefits of creating FK indexes menually?.

    DB:2.67:Manually Indexing Fks? aj

    Not clear whether you are asking about PKs/UKs or FKs here.

    If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED for that constraint. It is not shown in any catalog view. Documentation bug here I think that is still in 10g docs. Index used by a constraint has been shown in NNN_CONSTRAINTS view since at least 9.2, possibly 9i.

    If you are talking about FKs, see http://osi.oracle.com/~tkyte/unindex/index.html

  • RELEVANCY SCORE 2.66

    DB:2.66:Finding A Match az



    Hello,

    I'm looking for some suggestion on how to solve a problem with QlikView. I have a table (tblLookup) with two columns (Item Number and UPC Number). I need to look in another table (tblProduct) to see if either of these columns match the Vendor Product Number column in tblProduct.

    In other words I am looking to see if the Item Number exists in the Vendor Product Number field, if not, does the UPC Number exist in the Vendor Product Number field.

    Any suggestions on how to accomplish this?

    Thank you in advance!

  • RELEVANCY SCORE 2.66

    DB:2.66:How To Insert Random Unique Values In 2 Columns ? d3


    I have table with 2 columns

    Create Table code_for_code (
    first_code varchar2(10) unique not null,
    second_code varchar2(10) unique not null );

    and i want to make PL/SQL code to insert random unique values in both columns , for instance 20 records ........ how can i do something like that ?

    DB:2.66:How To Insert Random Unique Values In 2 Columns ? d3

    John Spencer wrote:
    Blu:

    "Needless to say, I left that company to work for someone who actually listens to the developers when they point out that something is stupid or impossible."

    I don't believe you. There is no such company :-)There is! Took a long time finding one, but I'm here! :D

  • RELEVANCY SCORE 2.66

    DB:2.66:Unique Values With Multiple Links x3


    I have two columns of numbers, say columns A B similar to below
    10456 2345
    10456 2346
    10456 2347
    10678 2456
    10743 2518
    10743 2519
    10897 2620
    10897 2621
    10897 2622
    Basically there can be duplicates in column A of up to 5 numbers the same value but each of these values has a unique value in Column B. Ideally what I would like is to extract the unique values in Column A which I can do with the advanced filter but alongside
    this number I would like to put all the values from Column B that are associated with that number. This can either be across several columns in the same row as the unique number, down several rows below the number in the same column or perhaps ideally which
    I know is asking a lot, as a concatenate in one cell in the column next to the unique value. I hope this is understandable and if so I would value any help or advice.

    DB:2.66:Unique Values With Multiple Links x3


    Thanks to both for your help for which I am very grateful. I think that your solution Biff fits in best with my situaion and allows me them to handle the data as I want after the process. Your time and effort is much appreciated.

    Graham

    You're welcome. Thanks for the feedback!
    --
    Biff
    Microsoft Excel MVP

  • RELEVANCY SCORE 2.66

    DB:2.66:Distributing Data In Various Columns a8



    hi all,

    I am facing a problem in distributing data in various columns,the problem is i have a column named matnr_v ,which is a unique number having multiple corresponding control points which is of 2 digits,for each control point with particular matnr_v ,we are getting multiple quantities.We have to distributed such records in various columns of control point with particular matnr_v.Please tell how to distribute data.

    Regards,

    Praveen

    DB:2.66:Distributing Data In Various Columns a8


    hi all,

    I am facing a problem in distributing data in various columns,the problem is i have a column named matnr_v ,which is a unique number having multiple corresponding control points which is of 2 digits,for each control point with particular matnr_v ,we are getting multiple quantities.We have to distributed such records in various columns of control point with particular matnr_v.Please tell how to distribute data.

    Regards,

    Praveen

  • RELEVANCY SCORE 2.66

    DB:2.66:Unique Set Of Columns 3z


     Is there a way to constrain a combination of columns to be unique, without either column being unique?

    DB:2.66:Unique Set Of Columns 3z

    Sure, here is a sample.create table #t(c1 int,c2 int)alter table #tADD CONSTRAINT con1 unique(c1,c2) ;

  • RELEVANCY SCORE 2.66

    DB:2.66:Finding The Differences In Two Tables That Are The Same, But One With Additional Rows 3p


    DB version 7.3.x

    I have two tables, one is a backup copy of the original. The original has additional rows inserted into it. I need to find the rows that are in the original, but not in the backup. The original has about 240k rows and the backup has about 170k rows. There are 3 columns in eahc and the 3 columns combined define a unique record, thought there is no index on either table. I thought indexing the two tables first is a start, but do not know how to define the query to retrieve the unique rows. Any ideas ?

    TIA

  • RELEVANCY SCORE 2.66

    DB:2.66:Finding The Latest Date In A List kd


    I have two columns in my workbook, column A has dates and column B has customer numbers (6 numbers). What I want to return on a separate worksheet is the latest (highest) date for each customer number.
    I have tried playing with VLOOKUP and MAX but cant seem to get it right...
    Any help much appreciated!

  • RELEVANCY SCORE 2.66

    DB:2.66:Partition Table?? 7z


    Hi,
    I have read chapter 11 "Partitioned Tables and Indexes" of Concepts but i still have doubts.
    I have one table of 8.500.000 milion records and some dba's with more experience than me suggested to partition it.
    I can't partition it by range so i will partition it by hash. But the question is why do i have to partition it?
    What are the benefits?
    How can i decide the number of partitions? On what columns do i have to parition?
    As i read the columns values should be "unique or near unique".
    And then a partitioned table needs more maintainance, right?
    Please advice me.
    Thanks

    DB:2.66:Partition Table?? 7z

    Re: Partitioned tables or views

  • RELEVANCY SCORE 2.66

    DB:2.66:Datagridview/Query - Finding Highest Number And Assigning It To A Variable jj


    Hello all;I don't know if I worded this properly, but I have a mdb query that I display in a Datagrid on my form. The first column is TransactionNumber, which is the primary key and a running total (ex. RackingTransactionKey=RackingTransactionKey + 1). What I need to do is when the form is opened, find the highest (last) TransactionNumber from the query or Datagrid and assign it to a variable (RackingTransactionKey), so I can can update it (+1), therefore generating a unique number so I can add data to the query. Example;TransactionNumber (column 1) Contents (Col. 2) Etc. (Rest of Columns)1038Cell ContentsData1039 Cell Contents Data1040 Cell Contents Dataetc., etc.This is an example of the data that is displayed in my DataGrid. I need to take the highest number from TransactionNumber, and assign it to a variable in my form so I can create a unique value (ex. next TransactionNumber will be 1041).

    DB:2.66:Datagridview/Query - Finding Highest Number And Assigning It To A Variable jj

    You should use parametrized query, porting CInt(Val(TextBox1.Text)) to its only one parametersql = .... Where POLN = ?myQuery.Parameters.Add(POLN, Integer, CInt(Val......or like that.hthbest regards, Matt
    If a b Then MsgBox(a): MsgBox(b) Else MsgBox(a) : End

    See also:
    1,
    2,
    3,
    4,
    5,
    :)

  • RELEVANCY SCORE 2.66

    DB:2.66:Finding Parts From Outside Vendors(Contact Resellers) f8



    why am i not able to find 403884-001 part number on reseller sites.. and why is it not part of HP list of parts.

    this hinge is not unique for hp laptops .... Where can I find a set at reasonable price?

    DB:2.66:Finding Parts From Outside Vendors(Contact Resellers) f8


    The hinges are unique to the dv8000 series. I struck out finding any for less than about $60. The unit never sold in high volume and is getting some age on it I am afraid. Parts are scarce. HP only stocks parts for a limited time and I suspect this laptop has gone past that time.

  • RELEVANCY SCORE 2.66

    DB:2.66:Re: How To Compare Index Columns From Different Users With Same Table Name.... If The Index Columns Are Same Or Not? cm


    I am using 11gR2...UserA:create table TBL_A ( FIELD_A1 number not null,FIELD_A2 varchar2(50),FIELD_A3 date,FIELD_A4 number(5,2) default 0,FIELD_A5 varchar2(10) not null ); create unique index XPKTBL_A on TBL_A (FIELD_A1);UserB:create table TBL_A ( FIELD_A1 number not null,FIELD_A2 varchar2(20),FIELD_A4 number(5,2) not null,FIELD_A5 varchar2(10),FIELD_A6 number(2) not null ); create unique index XPKTBL_A on TBL_A (FIELD_A1, FIELD_A6);I change index_owner to table_name still doesnt show the differences:WITH t AS (SELECT COUNT (DISTINCT table_name || index_name || indexed_cols) cnt FROM ( SELECT table_name, index_name, listagg (column_name, ',') WITHIN GROUP (ORDER BY column_position) indexed_cols FROM dba_ind_columns WHERE index_name='XPKTBL_A' and table_name= 'TBL_A' GROUP BY table_name, index_name))SELECT CASE WHEN cnt 1 THEN 'Indexes are different' WHEN cnt = 0 THEN 'Indexes dont exist' WHEN cnt = 1 THEN 'Indexes are identical' END commnt FROM tResult:Indexes are identical

  • RELEVANCY SCORE 2.66

    DB:2.66:Html 5 App In The Intermec Browser. How To Get Some Unique Identifier From Terminal ? (Js, Jq,Ajax) ma



    So I would need help in finding out somehow the serial number / mac adress or some other unique identifier of an Intermec device, in Javascript or Jquery. I'll be using Intermec Browser.

    Any help would be appriciated, didn't find anything related in google.







    Solved!
    Go to Solution.

    DB:2.66:Html 5 App In The Intermec Browser. How To Get Some Unique Identifier From Terminal ? (Js, Jq,Ajax) ma


    You may control the SIP using two of my tools: iHook3 and iSIP2

    http://www.hjgode.de/wp/2009/05/16/hooking-into-the-keyboard-message-queue/

    http://www.hjgode.de/dev/iLock/index.html#iSIP2

    Assign a key of your choice to launch iSIP2 which will toggle the SIP.




    .....................Don't be lazy, give KUDOS........................-------------==========================--------------See all my tips and tools at hxxp://www.hjgode.de/devand the NEW http://www.hjgode.de/wpcode at google com: http://code.google.com/p/itc-keyboard/http://code.google.com/p/rdesktop-ce/http://code.google.com/p/win-mobile-code/source/browse/#svn%2Ftrunk

  • RELEVANCY SCORE 2.66

    DB:2.66:How To Set Running Number To Column If Column Is Null? d8


    I have SQL Server 2012.
    I have columns CustomerName, RowId, Sales. My SSIS import Customer and Sales columns.
    Now I would like to create SQL Task, which gives RowId.(not unique value)
    How to set running number if RowId is null?

    Kenny_I

    DB:2.66:How To Set Running Number To Column If Column Is Null? d8

    Basically you are looking which is defined as
    Surrogate Key concept in
    Data warehouse
    If you are using SSIS to import your data from SQL to SQL or Legacy file to SQL etc.. you can follow below tutorials to do the same
    How to add surrogate key to rows SSIS
    Generate Surrogate key in SSIS

    However, if you want to achieve the same via T-SQL then
    I would recommend you to either create RowId as a
    primary Key in your table
    the other not cleaner way is to take the max of RowId and
    ISNULL(MAX(RowID),0)
    then, update your table by increasing this row id by 1 each time..
    Providing Guid can be a option instead of keeping RowId is integer.. but why to consumer more space? ;)
    Hope this will help

  • RELEVANCY SCORE 2.66

    DB:2.66:Add Columns To Account Activity Statements sm



    Hi All

    We need to add a couple of columns to our account activity statement, i.e., eBay Item number. Not finding how or if we can do this. Any help would be greatly appreciated. Also, is there anyway to combine eBay and PayPal activity statements?

    Thanks.

    Cyndy C.

    DB:2.66:Add Columns To Account Activity Statements sm


    Hi All

    We need to add a couple of columns to our account activity statement, i.e., eBay Item number. Not finding how or if we can do this. Any help would be greatly appreciated. Also, is there anyway to combine eBay and PayPal activity statements?

    Thanks.

    Cyndy C.

  • RELEVANCY SCORE 2.66

    DB:2.66:Try Limiting The Number Of Unique Group Values In Cross Tab 1s



    Post Author: RAJESH PATEL

    CA Forum: Charts and Graphs

    Hi,

    I am using cross tab in crystal report 8.This cross tab have 12 fixed column and one dynamic column.I am having problem when the number of records exceed to 5000.

    The errror message:The number of rows or columns is too big.Try limiting the number of unique group values.

    Please help to resolve the issue.

    Thanks

    DB:2.66:Try Limiting The Number Of Unique Group Values In Cross Tab 1s


    Post Author: V361

    CA Forum: Charts and Graphs

    Rajesh, I am not aware of a limit, but I am using CR XI, not 8. That's why I asked about the filters, if you could limit the number of records returned it should fix your issue.

  • RELEVANCY SCORE 2.65

    DB:2.65:Maximum Number Of Columns In Wi Report zj


    Hello,I have created a Web Intelligence report with approximately 3 dozen unique objects, however, the result of my report will only display up to 28. I have attempted to increase the margin width and insert additional columns, but no luck.My question is: Is there any way I can insert additional columns in my report? Is there a maximum number of columns for a WI report?The resulting data from my report won't be printed anywhere but will be saved as a .txt file for uploading into another system.Thanks so much for your insight into this.Nancy

    DB:2.65:Maximum Number Of Columns In Wi Report zj

    Hi Nancy,You can increase Page Size to A3,A2,A1. So that all column will accommodate in one page.Best Regards,Atul