• RELEVANCY SCORE 3.64

    DB:3.64:Find Unique Names In Two Columns? sa




    Hi all,
    Is there a way to find set of unique names between two columns (column 1 and 2)and populate the third column(column 3)with it. For eg.
    Column 1
    Column 2
    Column 3 (Result)
    John; Jen; King; Kyle; Jen; Jenny; Kyle; John; Jen; Jenny; King; Kyle
    This is to get around the fact that I cannot perform lookup in a list depending on two columns using SPD workflow 2010.

    Regards, Kapil ***Please mark answer as Helpful or Answered after consideration***

    DB:3.64:Find Unique Names In Two Columns? sa

    Thanks Brandon. I cannot use code in my environment so have figured out a different way. The columns that I was looking up, I have now concatenated the value of those and populated the name of the person against the concatenated value. So my workflow will
    look up the final combined value. Thanks for your input.Regards, Kapil ***Please mark answer as Helpful or Answered after consideration***

  • RELEVANCY SCORE 3.62

    DB:3.62:Help Me In Solving This m9




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

    DB:3.62:Help Me In Solving This m9

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

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

    from Customers C GROUP BY [Name]

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

    My blog

  • RELEVANCY SCORE 3.49

    DB:3.49:Combining Data From Two Excel Worksheets, With One Common Field. 11




    I have two columns of text and data in tab-1 of my Excel 2007 workbook.
    Column “A” contains a list of unique alphanumeric names and column “B” contains a list of the alphanumeric IDs - one unique ID for each unique name. The list will be several hundred rows long.
    TAB-1

    COLUMN A

    COLUMN B

    PEN RED

    1234

    COMPUTER BLUE

    5678

    In tab-2 of the same workbook, column “A” is empty and I have a list of unique IDs in column B, some of which are the same as the IDs on tab-1.
    To the right of the IDs on tab-2, I have several columns of information for each unique ID.

    TAB-2

    COLUMN A

    COLUMN B

    COLUMN C

    COLUMN D

    1234

    RED

    Pink

    5678

    BLUE

    Purple

    2121

    BLACK

    GRAY

    3434

    GREEN

    LIME

    How can I copy the data from columnA tab-1, to the appropriate rows in column A tab-2?
    In other words, how can I copy the hundreds of unique names from tab-1 to tab 2 and have each name be on the same rows as its partner ID #?

    I've never done anything like this before in Excel. I'm confident it can be done, but I'm stumped as to how.

  • RELEVANCY SCORE 3.27

    DB:3.27:Comparing Two Columns To Find The Differences a1


    I have a list of file names in an Excel worksheet, about 350 file names. I've added many files to this directory, and made a second worksheet with the new file names, so that now I have about 450 file names. So.....if I put these two lists into the same
    worksheet, maybe the older list of 350 in column A and the newer list of 450 in column B, I need to compare the two columns and know which names have been added.
    Is there some code out there that can be used to compare data in column A to data in column B, and in some way, define which data is different, maybe in column C?

  • RELEVANCY SCORE 3.22

    DB:3.22:Copy Only Rows Based On Unique Value In One Column And Paste Them To Another Worksheet Where The Worksheet Name Equals Column Value zp


    I have multiple worksheets in one shared folder. There is one master worksheet but I need to break that out into separate worksheets based on each unique value in a column named ModCategory (column V). So if I were to determine the unique values in this
    column by using this piece of code:

    Columns("A:BO").Select
    Columns("A:BO").EntireColumn.AutoFit
    Range("V2").Select
    'extract a list of unique ModCategory in this column
    ws1.Columns("V:V").Copy _
    Destination:=Range("BQ1")
    ws1.Columns("BQ:BQ").AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("BP1"), Unique:=True
    r = Cells(Rows.Count, "BP").End(xlUp).Row
    'set up Criteria Area
    Range("BQ1").Value = Range("V2").Value

    In the end what I would like to do is extract only the rows for each unique value found, copy those rows and columns (A:BO) to another worksheet within that same shared folder where first two characters of that worksheet equal the unique values found in
    the master worksheet. This would loop through until all the worksheets in that folder have been found and rows have been copied into each one.

    DB:3.22:Copy Only Rows Based On Unique Value In One Column And Paste Them To Another Worksheet Where The Worksheet Name Equals Column Value zp

    I assume that there are no empty cells in the column "ModCategory" and each unique value could be used as filename.
    I also assume that your headings are in the first row.

    Copy the code below into a normal module.

    Andreas.

    Option Explicit

    Sub Main()
    Dim R As Range
    Dim Items
    Dim i As Long, c As Long
    Dim WB As Workbook
    'Supress messages from "SaveAs" dialog
    Application.DisplayAlerts = False
    'Screen off, runs faster
    Application.ScreenUpdating = False
    'The current sheet must contain the column
    With ActiveSheet
    'Find the column "ModCategory"
    Set R = .Rows(1).Find("ModCategory", LookIn:=xlValues, LookAt:=xlWhole)
    If R Is Nothing Then
    MsgBox "Column ""ModCategory"" not found."
    Exit Sub
    End If
    'Get the column number
    c = R.Column
    'Build the unique items
    Items = UniqueItems(R.EntireColumn)
    'Be sure the filter is off
    If .FilterMode Then .ShowAllData
    'For each item except the header
    For i = 1 To UBound(Items)
    'Set the autofilter
    .Range("A1").AutoFilter c, Items(i)
    'Get the visible cells
    Set R = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    'Create a new file with only one sheet
    Set WB = Workbooks.Add(xlWBATWorksheet)
    'Copy the cells
    R.Copy Cells(1, 1)
    'Save and close it
    WB.Close True, ThisWorkbook.Path "\" Items(i)
    Next
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    Private Function UniqueItems(ByVal R As Range, _
    Optional ByVal Compare As VbCompareMethod = vbBinaryCompare) As Variant
    'Return an array with all unique values in R
    Dim Area As Range, Data
    Dim i As Long, j As Long
    Dim Dict As Object 'Scripting.Dictionary
    Set R = Intersect(R.Parent.UsedRange, R)
    If R Is Nothing Then
    UniqueItems = Array()
    Exit Function
    End If
    Set Dict = CreateObject("Scripting.Dictionary")
    Dict.CompareMode = Compare
    For Each Area In R.Areas
    Data = Area
    If IsArray(Data) Then
    For i = 1 To UBound(Data)
    For j = 1 To UBound(Data, 2)
    If Not Dict.Exists(Data(i, j)) Then Dict.Add Data(i, j), 0
    Next
    Next
    Else
    If Not Dict.Exists(Data) Then Dict.Add Data, 0
    End If
    Next
    UniqueItems = Dict.Keys
    End Function

  • RELEVANCY SCORE 3.10

    DB:3.10:Counting Two Columns Of Data 8x


    Hello,

    I have a spreadsheet with account names incolumn Aand the year the account ran inColumn B. There are over 260,000 entries in column A and only six unique entries in column B (years2008through 2013).

    The 260,000 account names incolumn A are not unique. there are many duplicates. For example let's say theXYZ Corp had 500 orders in 2013, 650 orders in 2010 and 50 orders in 2008.

    I would like to create a pivot table pivotthat shows how many years each account placed orders, not the total numbers of orders. For example, I would like to show the number "3"for theXYZ Corp. because they placed orders in three years. Whenever I use
    the "Count" value setting in my pivot table, it shows the total number oforders placed not the total years.

    Can I accomplish this in the pivot table or do I have to add another column of data that counts the number of years before I pivot?

    Any help would be greatly appreciated.

    Thanks.

    Chris

    DB:3.10:Counting Two Columns Of Data 8x

    Benishiryo,

    Thank you for responding to my request. Unfortunately, I am using Excel 2007. The company I work for is very slow to invest in software upgardes. It makes my job challenging.

    Thanks anayway.

    Chris

  • RELEVANCY SCORE 3.06

    DB:3.06:Retrieve Data Using Not In Clause fm


    Hi,

    Ihave two temporal tables(temp,temp2) with two columns on each

    temp(phone,operator)
    temp2(phone,operator)the phone and operator columns are unique key in temp2

    what can i do to retrieve the data which are in temp but not in temp2?

    thanks in advanced

  • RELEVANCY SCORE 3.05

    DB:3.05:I Have Dought On Dynamic Sql Please Clarify This One kx


    i have a table with say - rule_component

    which has two columns

    1.db_table
    2.from_clause

    The two columns contains tables for example

    db_table
    ------------
    emp
    dept
    bonus
    emp
    def

    from_clause
    ----------------

    emp,dept,bonus,dept,emp,abc

    My requirement

    i want to find the duplicate tables in both the from_clause and db_table column
    and populate in my from clause which are unique to both the columns

    say example

    select ......

    from emp,dept,bonus,abc,def

    DB:3.05:I Have Dought On Dynamic Sql Please Clarify This One kx

    Hi Kishore,

    Sorry about that, but it's not easy to see what you need to do!

    What will you be selecting from these tables once you have your from clause? Normally, when you select from multiple tables, you have joins between the tables - your example does not show this. If, however, you want to select a single column (eg, an ID column) from all tables you should be using the UNION operator.

    Can you please explain further what information you need to get.

    Regards

    Andy

  • RELEVANCY SCORE 3.04

    DB:3.04:Vlookup From Two Columns kk


    =VLOOKUP(Sheet3!$A2,'Budget Builder'!$B:$U,4,FALSE)

    How can I use a simular expression to search for data in Column B and T?
    Column B has project Names (three lines of data for each name where T is unique)
    Column T has Type (Capital, Passings, and Mileslines for eachProject Name)

    I guess I am looking for a nested VLookup, but I don't know how I would do that.

    DB:3.04:Vlookup From Two Columns kk

    with my constraints of pulling from a sheet I cannot modify, it does not.
    I did find the solution on youtube:
    {=INDEX('Budget Builder'!F:F,MATCH(A2C2,'Budget Builder'!B:B'Budget Builder'!T:T,0))0}
    REF: http://www.youtube.com/watch?v=Q_7M-DRNPCI#t=187

  • RELEVANCY SCORE 3.03

    DB:3.03:How Do I Compare Two Columns Of Data In Numbers To Find The Unique Results? p1


    Hello all,I'm looking for a way to compare the data from two columns so I can find the unique results and display that data in a third column. To be specific, here's what I'm doing. Column A is a list of email addresses for people I have already written. Column B is a list of email addresses for people I would like to contact. I am not sure if there are email address from Column A in Column B, but there may be and if there are, I must find out so that I don't send a second email to these recipients. How can I have Numbers look at both columns and tell me which email addresses in Column B are not in Column A?Thanks for the help!!!

    DB:3.03:How Do I Compare Two Columns Of Data In Numbers To Find The Unique Results? p1

    Thanks everyone for your insight and help. These ideas were all really useful.

  • RELEVANCY SCORE 3.00

    DB:3.00:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx


    In table which have millions of records -
    I have a Table B under Schema A and i have to find the duplicate records in a table. My Table B uses the combination of columns to identify the unique values.
    Scenario is something like this-
    One SV_Id can have multiple SV_Details_Id and i have to find out if there is any duplicate in the table. (I have to use combination of columns to identify the unique values and there is no Primary Key, Foreign Key or Unique Key on the table)

    then wrote the following query -
    select SV_Id,SV_Details_Id count (*) from SchemaA.TableB group by SV_Id,SV_Details_Id having Count (*) 1 ;

    Is it correct as after firing the above query it is returning the rows which is not matching to the duplicate.

    kindly help me.
    Looking forward for some guidance -
    Thanks in advance.

    DB:3.00:Find Duplicate Values In A Table Based On Combination Of Two Columns. zx

    What is the desired output??

    Do you want to see only unique records??

    Or you want to see the duplicate records.

    Giving an example will make it easier to provide the required querty.

    BTW here is an simple query which is used to delete the duplicate records and keep the unique records in table.

    DELETE
    FROM table_name a
    WHERE EXISTS (SELECT 1
    FROM table_name b
    WHERE a.sv_id = b.sv_id
    AND a.sv_detail_id = b.sv_detail_id
    AND a.ROWID b.ROWID
    );Regards
    Arun

  • RELEVANCY SCORE 3.00

    DB:3.00:Is The Order Of The Columns In An Index Important 98


    Hi,

    i have an index with two columns.
    Both columns define the unique index.

    Now my index is Column B, Column A.

    Column A is more selective than Column B. Is there a different, if i change the columns in the index? Index Column A, Column B

    Both Columns are used in a where condition in a select-statement.

    Thanks
    Wolfgang

    DB:3.00:Is The Order Of The Columns In An Index Important 98

    hi john,

    thanks for this, i never this is mis leading unit today :-)
    is there any other link you have which i can go through and improvise my concepts
    regards
    nic

  • RELEVANCY SCORE 3.00

    DB:3.00: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.00: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 2.99

    DB:2.99:Matching Two Fields In Same Table fm


    I have a table with two columns containing Names (NameA and NameB columns)

    I want to match these two columns to find which rows don’t match, meaning they have different characters.

    TableA

    NameA
    NameB
    James Bond James Bond
    Steve Ray Steve Ray
    Ellie Golden Natasha Kiss

    So the end result would return the last row only as the two names don’t match, how can I write a sql script to do this

    DB:2.99:Matching Two Fields In Same Table fm

    Try
    select *
    from TableA
    where NameANameB;Many Thanks Best Regards, Hua Min

  • RELEVANCY SCORE 2.96

    DB:2.96:Count Unique Values Two Columns Of Data pc


    I need to count the X's in column B only if the values in Column A are unique.
    The example below would resultin a count of 2 (one X for 5262, and one X for 6520)
    I triedseveral formulas (sumproduct, countifs) but can't get the correct answer.

    5262
    X

    6520

    6520
    X

    6520
    X

    6520
    X

    6520
    X

  • RELEVANCY SCORE 2.95

    DB:2.95:Duplicate Net Names In Different Busses k1



    If I have two different buses with different names can the net names within the buses be the same or does every net have to have a unique name? For example:

    Bus Name Net Names

    DC_MOTOR1 Enable, Dir

    DC_MOTOR2 Enable, Dir

    DB:2.95:Duplicate Net Names In Different Busses k1


    You can definitely use the same net name in two or more bus definitions but they will represent the same nets. If you rip net Enable out of DC_MOTOR1 and DC_MOTOR2 there will be only one net called Enable. Export Quick Connection View with option "Flat Mode" is a quick way to understand how nets are connected together if you have a doubt as it shows the flat nets which are used by CES and Expedition.

  • RELEVANCY SCORE 2.95

    DB:2.95:Remove Duplicates In A Query 8j


    I have two columns...

    The first columns have machine names and the second column is the user who owns that machines

    I would like to run a query that deletes all the dups...keeping only one of them.
    So if you have four machine names and four of the same user names. I want it to find all the machine names and delete all except for one.

    How do I do that?

    I did a query wizard "find match" but it wont keep one of each.

    DB:2.95:Remove Duplicates In A Query 8j

    Duh! I was naming the query not the table. Back to basics! Thank you so much!

  • RELEVANCY SCORE 2.93

    DB:2.93:Non Clustered Unique Index Creation Failing Due To Duplicate Key jz


    I am a DBA and my rebuild index job has failed with the error that The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name and index name. The non clustered unique index is created on two columns and
    i see that these two columns have a duplicate value. My question is that how was the duplicate data inserted in to the table when it already had anon clustered unique index in place on these columns.
    Please help me to understand this concept.

    DB:2.93:Non Clustered Unique Index Creation Failing Due To Duplicate Key jz

    . So how will I resolve the issue. Is there any way other than deleting one of these rows?

    There is no other way. You need to delete the duplicates:
    http://www.sqlusa.com/bestpractices/eliminateduplicates/

    Kalman Toth Database OLAP Architect
    SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

  • RELEVANCY SCORE 2.93

    DB:2.93:Two Unique Columns In Sql Azure Federation 7p


    Assume a Users table has ID and emailAddress columns though in reality it would have more. ID is a uniqueidentifier whereas emailAddress is nvarchar(256). I need to make sure both of these columns are unique. Also, this table is in a federation member
    database. How could I achieve this?

    DB:2.93:Two Unique Columns In Sql Azure Federation 7p

    just follow what the error tells you.
    federated columns are required to be part of a unique or clustered index.
    see: Federation Guidelines and Limitations
    so, for your unique index, the columns should be IdemailAddress

  • RELEVANCY SCORE 2.93

    DB:2.93:Infopath 2013 : The Form Cannot Be Submitted. The User Does Not Exist Or Is Not Unique ds


    Hi Friends,
    I have created a list form which has two text columns Text1 and Text2 getting information from another list. Using a data connection, I'm auto populating the values in Text1 and Text2 which is working correctly

    Next, I created 2 peplepicker columns in list form, on which I have set the rules
    Condition: None - Rule run when field is changes
    Set a fields value AccountID=Text1
    This will take the text from Text1 and convert into a username in the peoplepicker fields
    But for some reason these rules are not working and I'm getting this error

    Any ideas on how to resolve this issue and convert the text values to user names?

    DB:2.93:Infopath 2013 : The Form Cannot Be Submitted. The User Does Not Exist Or Is Not Unique ds

    Chandra,
    Make sure your form ppl picker control data type is UserType.
    Is it possible recreate this from using Customise in infopath using list tab from ribbon.Please 'propose as answer' if it helped you, also 'vote helpful' if you like this reply.

  • RELEVANCY SCORE 2.92

    DB:2.92:How Do I Get The Columns Names And Data Types From Msaccess Database For Particular Table And How Do I Find Schema Name In Msaccess jm


    How do i get the columns names and data types from MsAccess database for particular table and how do i find schema name in MsAccess

    DB:2.92:How Do I Get The Columns Names And Data Types From Msaccess Database For Particular Table And How Do I Find Schema Name In Msaccess jm

    Hi,
    Check below similar thread
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/c8d7b3a6-cd99-4a45-a90b-66bfd6142b35

    Regards
    Ishan

  • RELEVANCY SCORE 2.92

    DB:2.92:Unique Key 19


    HI All

    How to check constraints in table?

    I have defined a unique constraint on a column a

    Now i want to cahnge it for cobination of two columns become unique

    Please Guide
    Thanks Regards
    Vikas Singhal

    DB:2.92:Unique Key 19

    vikas singhal wrote:
    Hi ANurag,

    Is index is working like unique constraint??

    Thanks And Regrards
    VikasHi,

    Yes if you create a unique index.
    Else create a unique constraint

    Regards
    Anurag

  • RELEVANCY SCORE 2.92

    DB:2.92:Help With Multi-Dimensional Lookups sa


    Hello,

    I've gota worksheet ("WORKSHEET A") withten columns. Each column ("COLUMN A1", "COLUMN A2",etc.)is titled withauniquenumber generated from anseparate worksheet. Each titlewill dynamically change to a differentnumber based on changes to this outside
    worksheet.
    I've got another worksheet in the same workbook("WORKSHEET B") withten columns. Each column ("COLUMN B1", "COLUMN B2",etc.) is titled with auniquenumber that is static. Each column containsten rows of static data with a unique number in every cell.There
    isoneDefined Name ("NAMED ROW 1", "NAMED ROW 2",etc.)for eachrow in this worksheet. Each of the Defined Names has a range of one row and all ten columns.

    Here's an example of what I would like to do:

    I wish to have a cell in "WORKSHEET A" in "COLUMN A1" use theuniquenumberstated inits owncolumn title to look for thesame unique numberin the column titles of "WORKSHEET B." When it finds an exactmatch, inthis example it's "COLUMN B6", I then want
    to tell it to find whatever number intersects "NAMED ROW 3" and "COLUMN B6" and display it back in that cell in "COLUMN A1".
    I've done two-dimensional lookups before, but it's been a long while, so any help would be greatly appreciated.
    Also, don't worry about if some of the title numbers in "WORKSHEET A" don't match any title numbers in "WORKSHEET B" as I'm going to slap on an IFERROR message inthat rare circumstance.

    DB:2.92:Help With Multi-Dimensional Lookups sa

    Ashish,

    Wat is _401_a_17_AND_404_l_Limits? Where does it come from?

    Jack.

  • RELEVANCY SCORE 2.92

    DB:2.92:Getting Unique Values Across Two Files p3


    I have two very large files we'll call Old and New. New contains many entries that Old contains. What I need to do is remove any entry from New that Old contains. There are 9,459 entries in Old with 55 columns. New contains 11,983 entries with 76 columns.
    I need to make the comparison based on 5 columns; 'name_last', 'name_first', 'name_middle', 'street', and 'type'
    I'm using Excel 2010, I'm very new to it, and haven't got a clue where to start.

    DB:2.92:Getting Unique Values Across Two Files p3

    Hi,

    One simple approach could be as follows:

    1. In a spare column of the old file, concatenate the five columns i.e. A2C2F2G2H2. I have assumed that 'name_last', 'name_first', 'name_middle', 'street', and 'type' are in column A, C, F, G and H
    2. Copy this down till last row
    3. Repeat steps 1 and 2 for new file as well. Supoose the spare column in new file in BZ
    4. In another spare column of thenew file, enter this formula

    =VLOOKUP(BZ2,spare column of the old file created in step1 above,1,0)

    You will get an error against entries which are in new file and not in old file. Filter on non error values and delete those rows.

    Tareduce file size, you may now delete the spare columns created.

  • RELEVANCY SCORE 2.91

    DB:2.91:I Am Trying To Find All The Duplicate Values That Exist In A Column I Created Using A Concatenate Formula In Excel. 38


    I was working with 2 columns of data, one containing client last names and the other containing dates of birth. Some of the names are duplicates for the same person (same last name and DOB that appears each time the person placed an order). However, some
    of the last names apply to totally separate individuals (same last name different DOB).

    I used a concatenate formula combining the two columns and now have a list of all the individual names that appear in my original list without the duplicates. Excel also created a unique identifier to go with each last name. My problem is that the person
    who originally entered the data made spelling errors with some of the names (ex. smith 351339 and smyth 351339). Each entry corresponds to the same person, but it is listed in 2 separate rows in my new sheet. Is there a formula that can identify the mistake
    duplicates by searching for the partial last name and unique identifier?

    DB:2.91:I Am Trying To Find All The Duplicate Values That Exist In A Column I Created Using A Concatenate Formula In Excel. 38

    If the results of your concatenation formula are in column C, then use a formula like this in, say, D2

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: How To Check For Uniqueness Of Columns cp


    Hi,

    If I have 2 columns one age and other date of birth, I need to find whether the combination of these two columns are unique or not?

    DB:2.91:Re: How To Check For Uniqueness Of Columns cp

    Sorry this is the query

    select age,birth from table group by age,birth having count(age||birth)1.

  • RELEVANCY SCORE 2.91

    DB:2.91:Extracting Unique Records From Two Different Tables Record 7x


    Hello,

    In the following code of two different tables www.testing.com exists in both tables. I want to compare two different columns of the two different tables to get unique records.

    SQL select unique(videoLinks) from saVideos where sa_id=21;

    VIDEOLINKS
    -----------------------------------------------------------------------
    www.testing.com

    SQL ed
    Wrote file afiedt.buf

    1* select unique(picLinks) from saImages where sa_id=21
    SQL /

    PICLINKS
    -----------------------------------------------------------------------
    test
    test14
    www.hello.com
    www.testing.comThanks best regards

    DB:2.91:Extracting Unique Records From Two Different Tables Record 7x

    Unfortunatly you didn't mention the expected output. I guess it would be the one line
    "www.testing.com"

    in that case simply join the two tables.

    select *
    from saVideos v
    join saImages i on i.sa_id = v.sa_id and i.picLinks = v.videoLinks
    where v.sa_id=21;If needed then you could change the select list to retrieve only distinct values.
    select unique v.sa_id, v.videolinks
    from saVideos v
    join saImages i on i.sa_id = v.sa_id and i.picLinks = v.videoLinks
    where v.sa_id=21;I usually avoid distinct/unique whereever possible. This requires the database to do a sort and makes the query slow.

    Edited by: Sven W. on Feb 10, 2011 1:55 PM

  • RELEVANCY SCORE 2.89

    DB:2.89:Retrieve Columns Within A Table a7


    I have been looking and can not find an anwser to my problem. I have a table (Derby) that contains a large number of colums.
    The user needs to filter the data based upon their unique needs at the time. However, they do not have the column names. Therefore, I need to retireve the columns in the table and preapre a query to show the data.

    I have not been able to find out how to retireve the set of column_names that currently exist in a table. yet I know it must be possible.

    Can someone provide some sample code, or approprate references, that show how determine all of the column_names defined in a table?

  • RELEVANCY SCORE 2.89

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


    Hello Gurus,

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

    Example:

    TABLE1: PK

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

    TABLE2: PK_COLS

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

    B_PKBB1
    B_PKBB2

    C_PKAA1

    E_PKEE1

    D_PKBB1
    D_PK BB2

    OUTPUT REQUIRED:

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

    Thank you in advance,

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

    Hi,

    I got it done. Thank you for the guidelines

    Thanks

  • RELEVANCY SCORE 2.88

    DB:2.88:Create Key For Dimension Using 2 Columns 1k


    Hello,
    I have table which unique id are two columns. For example:
    A B
    -1 1
    -1 -1
    2 -1

    In the fact table these columns also appear. I would like to ask if it is possible to create dimension in SSAS using these two columns or I have to create real unique one column.

    DB:2.88:Create Key For Dimension Using 2 Columns 1k

    Yes but i would suggest to create physical primary key by including both column if possible otherwise you can also do by logical in DSV .Mukesh

  • RELEVANCY SCORE 2.88

    DB:2.88:Error In Summarise Function fa


    Hi,
    I have a table named feedback with two columns - Date and Organised By. The Organised by column has Client names.
    I want to generate a table where I Group by Organised by column and count the unique dates
    I wrote the following calculated field formula (name is Sessions conducted by client) to compute Sessions conducted per client
    =SUMMARIZE(Feedback,Feedback[Organised by],Count of sessions,DISTINCTCOUNT(Feedback[Date]))
    When I click on Check formula, I get the following error
    Calculation error in measure 'Feedback'[Sessions conducted by client]: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
    Could you please correct my formula above.
    Once the error is resolve, I will pass it to a function which will return require a Table input.
    Pleas help.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    DB:2.88:Error In Summarise Function fa

    Hi,
    Thank you for clarifying. I used the following calculated field formula to compute the sessions conducted at Top 10 clients
    =sumx(TOPN(10,SUMMARIZE(Feedback,Feedback[Organised by],Count of sessions,[Sessions conducted]),[Sessions conducted],0),[Sessions conducted])
    Sessions conducted is a calculated field computed as follows
    =DISTINCTCOUNT(Feedback[Date])

    Thank you for your help.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

  • RELEVANCY SCORE 2.87

    DB:2.87:Count Of Unique Text-Value Cells In One Column That Also Have Specific Text Values In Two Other Columns 9m


    I have a worksheet that has 28 columns, with data in almost 11,000 rows.

    Column D is "name" which is a text string
    Column X has text values of either Yes or No
    Column AA has a 4 character text string (e.g. ABCD - only 6 possible values in this column, representing business units)

    I need to count the number of unique values in D for each unique value in AA, e.g. ABCD has 5 unique names, EFGH has 30 unique names, etc. This is UniqueNamesPerBusinessUnit

    Next, I need to count the number of unique values in D that also have the value Yes in X, for each value in AA. This is UniquePositiveNamesPerBusinessUnit

    This will allow me to then get a "score" percentage of UniquePositiveNamesPerBusinessUnit/UniqueNamesPerBusinessUnit

    I'm more than willing to make intermediate calculations in separate cells - one for each business unit for UniqueNames and then UniquePositiveNames?

    I've tried samples that use matches within frequency, but only ever get #N/A values when copying the formula from the office site examples, even just to get a unique list of names, but I need to also consider the extra parameters of business unit, and then
    the Yes/No in column X, and at that point my eyes glaze over.

    Ideas?

  • RELEVANCY SCORE 2.87

    DB:2.87:How To Find/Count Duplicate Cells In Worksheet pc


    I have an Excel 2010 worksheet with two columns that contain about 500 cells each. The contents of the cells are names (such as Bob Smith). I need to get a count of all names that appear in both columns. I've tried this:
    =COUNTIF($A$2:$A$500, B$2:B$500)
    no luck.
    What I'm looking for is a simple number to appear in the cell that contains this formula.
    Kinda new with Excel formulas... appreciate the help.

  • RELEVANCY SCORE 2.87

    DB:2.87:Select Columns Based On Patterns Within The Column Names c1


    In SQL 2005 is there a way to select columns based on patterns within the column names?  For example I have a view with many columns in it but I want to create a dataset that only contains a few of these columns.  The columns I'm interested in have a common and unique naming convention.  So if my columns were as follows:table.columngroup1.colum1table.columngroup1.colum2table.columngroup1.colum3table.columngroup2.colum1table.columngroup2.colum2table.columngroup3.colum1table.columngroup3.colum2table.columngroup3.colum3I'm interested in seleting only the columns from columngroup1.Thanks,Scott

    DB:2.87:Select Columns Based On Patterns Within The Column Names c1

    I managed to build the table I was looking for with your suggestion and some minor edits as follows:

    declare @sql nvarchar(max),
    @col nvarchar(max)

    SELECT @col = isnull(@col + ',', '') + '[' + COLUMN_NAME +']'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myview'
    AND      LEFT(COLUMN_NAME, 12) = 'mycolumn'
    select @sql = 'SELECT ' + @col + ' FROM myview'

    exec (@sql)
    Now I just have to figure out why it's not building a dataset for my report.  I was doing this to pull specific columns from a view for a dataset for a report in reporting services.  Need it to be dynamic because this report needs to work for more than one customer.  However in RS, while the dataset executes fine and gets me the data, none of the data is showing up in my report.  I'll post something else in the Reporting Services forum.Thanks for the tips!Scott

  • RELEVANCY SCORE 2.85

    DB:2.85:Tables And Lists And Maintenance kj


    I've looked through the CS3 Bible and still can't see any way
    to handle a very simple problem. I want a table on my web page
    which will contain 3 columns and multiple rows. I simply want to
    put a name in each cell. Since the list of name is long I don't
    want one column to contain all the names. The names are maintained
    outside of DW and in sorted order. It really doesn't matter if the
    names are loaded in row order, such as the first three names go
    into the first row and the fourth, fifth and sixth names go into
    the second row, and so on. Or, the first fifty names go in to the
    first column and then the next fifty go into the second column and
    the last forty-nine names go into the third column. How do I
    re-seed the table when the list of names changes. One name drops
    out for instance or a new name is added? It doesn't seem to me to
    be amenable to Spry or any form usage. What would be the best way
    to accomplish this? I can't find any relevent information regarding
    this type of table filling so I'll end up with approximately three
    equal columns, although one or two of the columns might be one cell
    longer. What have I overlooked?

    DB:2.85:Tables And Lists And Maintenance kj

    Thanks for the response. I just got finished testing and
    implementing something. I took the data into Excel and made new
    cells featuring the additional xml coding which would be required.
    I then copy this into an xml page in DW and the use Spry to load
    the table. I wasn't sure I could use this method as I don't require
    header information and there are three columns. The data come to me
    from another source, produced in Word. It works well since I tested
    and realized I didn't need white spaces in xml. Thanks again for
    the response.

  • RELEVANCY SCORE 2.85

    DB:2.85:Find Constraint Name Given Column Names And Table Name dz


    Hi,

    Does anybody know how to create a sql query that will return a constraint name from user_constraints and user_cons_columns given a table name, a list of columns and the constraint_type.

    For example, I would like to find out a UNIQUE constraint's name on a table T1. This constraint is on columns ('C1', 'C2', 'C3') in table T1.

    Any help is appreciated!

    Thank you.

    DB:2.85:Find Constraint Name Given Column Names And Table Name dz

    select * from user_constraints where table_name='table_name'
    /

    select * from user_cons_columns where table_name='table_name'
    /

  • RELEVANCY SCORE 2.84

    DB:2.84:Obtaining A List Of Column Names In Omb Plus s3


    I have to add several operators to about 120 mappings and am trying to learn OMB Plus in hopes that it might be able to save time (and my eyesight/sanity;-). However, each mapping operates on different tables, each with their own unique set of columns. This list of columns needs to be added to a GROUP BY property of an aggregator.

    Is there a way to obtain a list of table column names in OMB so I can assemble this GROUP BY list? So far, I haven't been able to find one.

    I am using 10.2.0.1

    thanks

    DB:2.84:Obtaining A List Of Column Names In Omb Plus s3

    OWB has these experts that are like macros in Word and you can add actions on to the tree for example (or run them individually), so the Build from SQL is created by you when you install the expert (through the MDL on the blog). This expert is a plaything though, but an interesting one for folk to learn OWB map construction who know SQL. It uses a SQL parser that you would have to download and install.

    Cheers
    David

  • RELEVANCY SCORE 2.84

    DB:2.84:Camparing No Of Columns In Differe Schemas kj


    i have two schemas, want to find no of columns and table in both scehmas.

    DB:2.84:Camparing No Of Columns In Differe Schemas kj

    The reason I used dba_tab_cols is that it will get all the tables in the database for the schema specified.

    If you use user_tab_cols you only get tables in the schema select from. If you use all_tab_cols you only get tables in your schema and tables that you have privileges to select from, therefore its possible to get an incorrect count using all_tab_cols view.

  • RELEVANCY SCORE 2.83

    DB:2.83:Definition Of Collations dp


    I'm searching for complete definitions of the collations SQL_Scandinavian_Pref_Cp850_CI_AS, andSQL_SwedishStd_Pref_Cp1_CI_ASBackground:We have until now used the first collation (SQL_Scandinavian_Pref_Cp850_CI_AS) as default, but encountered problems with it regarding the treatment of the letters 'v' and 'w'. When inserting sample names in a column that requires unique names, the two names 'TV_063' and 'TW_063' are regarded as the same. I now want to change to the second collation (SQL_SwedishStd_Pref_Cp1_CI_AS) which seems alright, but I would very much like a complete documentation of it. Anyone who knows where to find it?Thank you

    DB:2.83:Definition Of Collations dp

    Did you consider using Windows collation instead. They are considered more modern compared to the SQL collations. For instance Finnish_Swedish_CI_AI.Tibor Karaszi

  • RELEVANCY SCORE 2.83

    DB:2.83:Dynamic Sql - Built m7


    i have a table with say - rule_component

    which has two columns

    1.db_table
    2.from_clause

    The two columns contains tables for example

    db_table
    ------------
    emp
    dept
    bonus
    emp
    def

    from_clause
    ----------------

    emp,dept,bonus,dept,emp,abc

    My requirement

    i want to find the duplicate tables in both the from_clause and db_table column
    and populate in my from clause which are unique to both the columns

    say example

    select ......

    from emp,dept,bonus,abc,def

    DB:2.83:Dynamic Sql - Built m7

    SQL descr sswms_rule_components;
    Name Null? Type
    ----------------------------------------- -------- ---------------------
    RULE_COMPONENT_ID NOT NULL NUMBER
    RULE_COMPONENT_CODE NOT NULL VARCHAR2(30)
    RULE_COMPONENT_NAME NOT NULL VARCHAR2(100)
    ENABLED_FLAG NOT NULL VARCHAR2(1)
    DB_TABLE VARCHAR2 (100)
    DB_COLUMN VARCHAR2 (100)
    DB_FUNCTION VARCHAR2(100)
    WHERE_CLAUSE VARCHAR2 (2000)
    FROM_CLAUSE VARCHAR2 (2000)

    The table contains the following data

    db_table
    --------------------------------
    sswms_shipment_lines
    wsh_carrier_ship_method --- the data inside the db_table column is a table

    db_column
    --------------------------------
    Ship to
    Carrier_id

    Where_clause
    -----------------------------
    Oe_order_headers_all.header_id = sswms_shipment_lines.order_header_id

    From_clause

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

    Oe_order_headers_all, sswms_shipment_lines -- the data inside the From_clause is a table

    Now my requirement is to build a dynamic sql in forms 6i.when I click the build sql it should update the sql

    So --- I have to write a procedure --

    Select db_table || . || db_column || || group_key

    ---group_key is an alias

    From db_table, From_clause

    --- Here the logic should be

    1. I should remove the commas from the FROM_CLAUSE and check for duplicate values
    2. i should check for duplicate values for the DB_TABLE column
    3. I should compare both the FROM_CLAUSE and DB_TABLE for DUPLICATE VALUES
    4. After doing this I should add the result to the FROM in the select statement

    where where_clause

    hope this is clear.

    regards

    sudharshan

  • RELEVANCY SCORE 2.82

    DB:2.82:Crosstab - Calulated Member - Using Partial Row Name - Crystal 2008 Basic k1


    Hi,I want to add two columns in a calculated member.The rows have two levels, then column by month.Row Level 0 = Master GL Account (i.e. 410000)Row Level 1 = GL Account name (i.e. 410100-10-10-Domestoc Sales Revenue 410200-10-10-Overseas Sales Revenue ............-etc.)My data comes from a stored procedure.I want to SUM two or more, non-consecutive, selected rows (GL Account Name).My problem is rather then find the grid value, of each row, using the full GL Account Name(see above "410100-10-10-Domestoc Sales Revenue") I want to find the row by the LEFT 6 characters (i.e. 410100 ONLY, it is unique) this is because the GL Account Name changes, after the first six characters, depending of selection criteria.I could use:GridValueAt (currentRowIndex-?,currentCloumnIndex,CurrentSummaryIndex) where? = number of row too offset.But, over time there will be GL Account Names added to the report and the report would fail when one new account was added.Is there a way to write the GridValueAt using a statement that looks at only the LEFT 6 characters?Any assistance would be greatly appreciated,Eric

    DB:2.82:Crosstab - Calulated Member - Using Partial Row Name - Crystal 2008 Basic k1

    Please re-post if this is still an issue or purchase a case and have a dedicated support engineer work with you directly

  • RELEVANCY SCORE 2.82

    DB:2.82:Length Of Uniqueid For Matrix Column 39



    I suggest increasing the permissible length of the Unique ID used for matrix columns from 10 to 12 for user defined columns, so as to permit the database column name to be used as the Unique id. System created columns can have names this long but not user defined ones. The ability to use the field name as the Unique ID is extremely useful when writing generic code to populate/process matrixes and this is not possible unless you restrict your UDF field names to 10 characters (including the U_), once you allow for a 3 character partner code (so as to play nice in the sandbox) that doesn't leave a lot of scope for meaningful fieldnames.

    DB:2.82:Length Of Uniqueid For Matrix Column 39


    I suggest increasing the permissible length of the Unique ID used for matrix columns from 10 to 12 for user defined columns, so as to permit the database column name to be used as the Unique id. System created columns can have names this long but not user defined ones. The ability to use the field name as the Unique ID is extremely useful when writing generic code to populate/process matrixes and this is not possible unless you restrict your UDF field names to 10 characters (including the U_), once you allow for a 3 character partner code (so as to play nice in the sandbox) that doesn't leave a lot of scope for meaningful fieldnames.

  • RELEVANCY SCORE 2.82

    DB:2.82: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.82: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.82

    DB:2.82:Compare Two Columns And Find Unique Values kc


    I have two columns and need to keep the unique values from BOTH columns.For example:Column 1abcColumn 2bcdDesired results:ad('a' and 'd' are unique to both column 1 and column 2)How on earth? Thanks,Kurt

    DB:2.82:Compare Two Columns And Find Unique Values kc

    Thanks Barry!This is a great solution. And it works with two columns that have a different number of rows, even when a row has the same value.This is a correct solution to my problem.

  • RELEVANCY SCORE 2.82

    DB:2.82:Unique Search Operator sp



    I know how to find alla unique values using ScripMaker, but wouldnt it be great to have a operator to choose to find all unique values in a field.

    Say for example that you have a database with all zipcodes from a country with matching city names. Say you want to find all unique city names from that database table without having to use ScriptMaker, List()-function or a value list based on that field.

    Might be something useful in next version of FileMaker.

    DB:2.82:Unique Search Operator sp


    johanhedman wrote:

    Say you want to find all unique city names from that database table without having to use ScriptMaker, List()-function or a value list based on that field.

    In version 12, you could also use the ExecuteSQL() function.

  • RELEVANCY SCORE 2.81

    DB:2.81:Count Unique Entries In One Column When They Fall Between Time Frames In Two Other Columns x3


    Hello all,

    I'm working on a report where I'm evaluating production times from a production report I receive daily.

    Column C has the list of producing associates, ColumnGthe production start time, and Column H the production end time (format: H:MM:SS AM/PM). The list of associate names on column C is not unique, meaning that an associate appears more than once.

    What I'm trying to figure out is how many unique associateson column Cproduced between the hours of7 AM and8 AM. For example; below the results show that between 7 AM and 8 AM there are 4 production results, but only Mary and Bill produced during the
    time frame. I would like a count of unique associates between the time frame. In this example, my result would be 2.

    Associate
    Start Time
    End Time

    Mary
    7:13:00 AM
    7:17:00 AM

    Mary
    7:14:00 AM
    7:37:00 AM

    Mary
    7:17:00 AM
    7:51:00 AM

    John
    7:18:00 AM
    8:12:00 AM

    Bill
    7:30:00 AM
    7:45:00 AM

    Bill
    7:30:00 AM
    8:10:00 AM

    Mary
    7:37:00 AM
    8:10:00 AM

    John
    7:37:00 AM
    8:25:00 AM

    Thanks

    DB:2.81:Count Unique Entries In One Column When They Fall Between Time Frames In Two Other Columns x3

    With:
    O3: start time
    P3: end time
    and
    correcting a typo in your posted formula (Shhet vs Sheet), this would be your ARRAY FORMULA:
    =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Sheet1!$C$2:$C$10296,ROW(Sheet1!$C$2:$C$10296)-ROW(Sheet1!$C$2),0,1)),IF((Sheet1!$H$2:$H$10296=P3)*(Sheet1!$G$2:$G$10296=O3),MATCH(Sheet1!$C$2:$C$10296,Sheet1!$C$2:$C$10296,0))),ROW(Sheet1!$C$2:$C$10296)-ROW(Sheet1!$C$2)+1)0,1))

    Using the previously posted sample data, that formula returns for unfiltered data: 2
    When filtered to hide Mary, the formula returns: 1

    Try that same test.
    If the results are correct, the issue probably lies within the data.
    If they are not correct, we need to troubleshoot the problem.

    Let us know what you find.

  • RELEVANCY SCORE 2.80

    DB:2.80:Comparing Substr Values Of Two Columns 8s


    Say you have a table with 3 columns:
    *Unique random-generated id,
    *First name
    *Mailing name

    I need to find all the rows where the mailing name does not contain the value of the first name column.

    The "like" operator (only used with where clause) doesn't seem to work because what you want to do is:
    select * from table
    where [first_name is *not* found in *part of* (it will never be exact match) the mailing_name]

    What's the correct where clause?

    Any help is appreciated. Thanks

    DB:2.80:Comparing Substr Values Of Two Columns 8s

    How about
    select * from table1 where col3 not like '%'#0124; #0124;col2#0124; #0124;'%'

    Of course you can switch to use the minus operator instead of not like if performance becomes an issue.

  • RELEVANCY SCORE 2.80

    DB:2.80:Count Unique Values And Unique Text Comparing Two Columns pj


    I have two colums, one is an ID and the other is a Name. I want to count only the Unique ID and Unique Name, without counting Multiple IDs of the same Name

    Example...

    Column
    A B
    ID Name
    1111 Test
    1111 Test 1
    1111 Data
    1112 Data 1
    1112 Data 1
    1112 Test
    1113 Test
    1113 Test
    1113 Test
    ...

    Count should be 6.... Dont want to count multiple Names or blanks of the same ID

    Thanks so much in advance!!

    DB:2.80:Count Unique Values And Unique Text Comparing Two Columns pj

    Hi,

    May I request you to post your question in the Comments section of this link -

    http://www.ashishmathur.com/count-uniques-with-conditions/#comments

    Thank you.

  • RELEVANCY SCORE 2.79

    DB:2.79:To Find Common Data In 2 Columns In Two Different Tables. ds


    Hi,

    Could someone help me out with a querry to find out common data from 2 columns in two different tables??

    Thanks
    Rajesh

    DB:2.79:To Find Common Data In 2 Columns In Two Different Tables. ds

    966915 wrote:
    suppose i hav 2 tables

    Table A
    s.No name EID
    1 aaa 123
    2 bbb 234 ... etc..

    and Table B

    S.no salary eid location
    1 mmm$ 123 india
    2 ddd$ 556 Uk
    3 fff$ 236 US

    How do i select the common entry in both the tables??why do you post in forum for SQL PL/SQL; yet never post anything in either language?

    what do you mean by "common enrty"?
    does every column value must match exactly across 2 rows in different table?

  • RELEVANCY SCORE 2.79

    DB:2.79:Difference Between Unique Constraint And Unique Index 38


    1. What is the difference between unique constraint and unique index when unique constraint is always indexed ? Which one is better in this case for better performance ?
    2. Is Composite index of 3 columns x,y,z better
    or having independent/ seperate indexes on 3 columns x,y,z is better for better performance ?
    3. It has been very confusing for me to decide which columns to index, I have indexed most foreignkey columns, is it a good idea ? We do lot of selects and DMLS on most of our tables. Is there any query that I can run and find out if indexes are really being used and if they are improving any performance. I have analyzed and computed my indexes using ANALYZE index index_name validate structure and COMPUTE STATISTICS;

    null

    DB:2.79:Difference Between Unique Constraint And Unique Index 38

    1. Unique index is part of unique constraint. Of course you can create standalone unique index. But is is no point to skip the logical view of business if you spend same effort to achive.
    You create unique const. Oracle create the unique index for you. You may specify index characteristic in unique constraint.

    2. Depends. You can't utilize the composite index if the searching condition is not whole or front part of the indexing key. You can't utilize your index if you query the table for y=2. That is.

    3. As old words in database arena, Index may be good or bad for a table depending on the size of table, number of columns in the table... etc. It is very environmental dependent. In fact, It is part of database nomalization. Statistic is a way oracle use to determine the execution plan.

    Steve

    null

  • RELEVANCY SCORE 2.79

    DB:2.79:Query On Index Key And Unique Key 8d


    I have a table with around 10 columns. This table has unique key created for three columns(Column1,Column2 Column3)

    First question :
    I will be using following statement.
    SELECT * FROM TABLE WHERE COLUMN1
    = 'XXX' ANDCOLUMN2 = 'YYY' ANDCOLUMN3 = 'ZZZ'
    In this case whether query performance will improve if I create a Index Key for these 3 columns or having unique key for these columns itself enough.
    Second Question:
    I also want to use following query
    SELECT * FROM TABLE WHERE COLUMN1
    = 'XXX' ANDCOLUMN2 = 'YYY'
    In this case, whether I need to create an Index Key for these two columns in order to improve performance or having Unique or Index key for the three columns itself will give the same improved performance?

  • RELEVANCY SCORE 2.79

    DB:2.79:Trying To Use All_Tab_Cols To Find Two Tables Based On Input Rows fz


    I'm using 9i.

    I'm trying to use the ALL_TAB_COLS table to find two tables that contain columns I can join them on. I know the names of the two tables. We can call them table1 and table2. I just using the columns that are stored in them below to find a common set of tables.

    The query I'm using is the following
    select distinct did.TABLE_NAME, did.COLUMN_NAME
    from
    (Select * from ALL_TAB_COLS
    where column_name in('SO_LINE_ID','SO_LINE_NUMBER', 'SO_ORDER_NUMBER', 'SO_HEADER_ID','AR_CUSTOMER_TRX_LINE_ID')
    --and OWNER = 'FTBV'
    ) did
    join
    (Select * from ALL_TAB_COLS
    where column_name in('SCHED_DISTRIBUTION_ID', 'CODE_COMBINATION_ID', 'PRODUCT_BUSINESS_CODE', 'PRODUCT_GROUP_CODE', 'PRODUCT_CODE')
    --and OWNER = 'FTBV'
    ) iii
    on did.TABLE_NAME = iii.TABLE_NAME;It's giving me limited results.
    What I need is something that can find interim tables that would link my two tables together based on the unique columns I've provided. Does such a query exist?

    Thanks in Advance.

    DB:2.79:Trying To Use All_Tab_Cols To Find Two Tables Based On Input Rows fz

    You're correct. I just started here recently and we ,in fact, have NO ER diagram.

    It's one of the things I petitioned to be put on our 'to-do' list when I got here.

    All the SMEs that knew the most about this DB have left.

    At this point I'm flying blind. ARE WE HAVING FUN YET!!!

    Thanks for the input!

    Edited by: user10382685 on Dec 9, 2008 8:29 AM

  • RELEVANCY SCORE 2.79

    DB:2.79: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.79: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.78

    DB:2.78:I Have 2 Name Columns, One Has Two Names While The Other Has 3 Names, I Want To Return A Value That Checks Whether The 2 Names Are Contained In The 3 Names Column ac


    I have 2 name columns, one has two names while the other has 3 names, I want to return a value that checks whether the 2 names are contained in the 3 names column

  • RELEVANCY SCORE 2.78

    DB:2.78:Comparing Two Columns In Set Analysis sk



    Hi,

    I have a fact table which contains Id1, Id2, Type, ItemCount. As per business, there should be a unique combination of Id1 and Id2. But in fact table more than one Id2 value is available for a particular Id1 value.

    So to find out the unique combination, we have another table which contains Id1 and UniqueId2.

    I want to show the sum of ItemCount for each Id1 which have the Type = 'A'....like count(if(Id2=UniqueId2 and Type='A',ItemCount))

    But this value should not be changed by the selections in "Type" filter...

    How to achieve this?

    DB:2.78:Comparing Two Columns In Set Analysis sk


    Hi,

    Try this.

    sum({1Id2 = p(OriginalId), Type = {"A"}}Item_Count)

    Regards,

    Kaushik Solanki

  • RELEVANCY SCORE 2.78

    DB:2.78:Ssrs 2008 Model Generation Error cx


    I have a SQL Server 2008 configured with SQL Server Reporting Services 2008. I created a datasource and I'm trying to generate a model for it. I get the following two error messages:More than one item in the Entity Table Name has the name Column Name. Item names must be unique among immediate siblings. (DuplicateItemName)More than one Field in the Entity Table Name has the name Column Name. Field names must be unique within an Entity. (DuplicateFieldName)Although I don't (and can't have two columns with the same name in a table). The columns that exist are like ColumnName and ColumnNameKey and SSRS is somehow not able to differentiate this. Any idea how I can fix this without deleting or renaming my column names?Much appreciated!-Anand

  • RELEVANCY SCORE 2.78

    DB:2.78:Updating Single Values From One Row To Another 11


    Dear everyone

    How I can copy just one single value from one row to another existing row, where although the row already exists, the value does not. I know how to update columns using other columns and how to update a table using aliases to create two virtual copies. However I cannot figure out how to do it for single values.

    I have
    1. a list of street names in one column: name_of_feature;
    2. a map index id number which is a unique id for each name: map_index_id;
    3. an original map publication id number: original_publication_id;
    4. geographical_coordinates column: geographical_coordinates

    Having digitised over a raster map I now need to copy values from the geographical_coordinates column, for certain feature names, to other rows in the table with the same name or in some cases a user entered id number because the name has changed.

    So it would work like this.

    I enter a feature_name that needs updating (or id number if running the id script).
    I enter the original_map_publication_id number so it knows which map is to be updated.
    Then I enter the map_index_id for row where the geographical_coordinate for that feature name are already stored.
    Then Oracle copies the single geographical_coordinate value to the row, without copying the rest of the row as well.

    Then once that is done I run it again for another feature name or id number, depending on which script I am running.

    I need to update value by value because I am checking maps whilst I do this and in some cases this will lead to further digitising instead of copying existing values.

    I would be grateful for any advice or pointers to where I can find such advice. I looked in the Oracle SQL manual but could only find the parts relating to updating of complete columns or rows.

    Kind regards

    Tim

    DB:2.78:Updating Single Values From One Row To Another 11

    Dear SomeoneElse and Frank

    Thank you for your replies. I use ' all the time yet as soon as I need to use a variable I forgot to add such things. May be one day I will remember all these simple but important things.

    I finally got it to work. I had already been reading an Apress book with an example in it but even reading that I was thinking that doesn't pin point the exact field I need and only does a whole column, when actually it was pin pointing a field and was all I needed all along. That, coupled with typos that always take me time to notice, if I do notice them, caused my queries to fail or be incorrect in the first place.

    The final script was as follows:
    UPDATE MAP_INDEX B
    SET (B.geographical_coordinates) =
    (SELECT A.geographical_coordinates
    FROM MAP_INDEX A
    WHERE A.original_map_publication_id=original_map_to_be_used
    AND A.name_of_feature='name_of_feature_to_be_used')
    WHERE B.original_map_publication_id=original_map_to_be_updated
    AND B.name_of_feature='name_of_feature_to_be_updated';Many thnkas

    Tim

  • RELEVANCY SCORE 2.78

    DB:2.78:Looking For Duplicates cf


    I have two columns of data, 65, 000 rows each. Assume column A contains street addresses and column B contains town names.
    There are many instances of a unique A, B combination.
    I want to run a process that will identify instances of street addresses that show up in more than one town and towns that show up for more than one street address.
    Ideas?

    DB:2.78:Looking For Duplicates cf

    The first part: street addresses that show up in more than one town:
    In C2, enter this, then copy down:
    =IF(SUMPRODUCT((A2=A$2:A$65000)*(B2=B$2:B$65000))COUNTIF(A:A,A2),"More than one town","")

    The other part is either poorly wordedor nonsensical: my town has thousands of street addresses. You can easily count the addresses for each town by using a pivot table, adding town to both the row and data fields.
    HTH,
    Bernie

  • RELEVANCY SCORE 2.78

    DB:2.78:Can Two Columns Have The Same Display Names But Different Underlying Names? xc


    I have two columns which are set up differently. But for a display purpose and different usage, they shouldshare the samedisplay names but not the underlying name. How may I set them up in SharPoint?

    DB:2.78:Can Two Columns Have The Same Display Names But Different Underlying Names? xc

    Hey PMA,
    In SharePoint Designer you can convert the list to a DataView web part. Once this is done the columns can have different actual names but the same display names. More information is available here:
    http://blog.pathtosharepoint.com/2008/08/24/your-first-data-view-web-part/
    http://www.sharepoint-tips.com/2007/05/sharepoint-designer-article-2-creating.html

    I hope that helps!Tom Molskow SharePoint Architect

  • RELEVANCY SCORE 2.77

    DB:2.77:Excel Update Via Field Position, Not Name. Also, No Key Field cx


    Hi,I am trying to update rows in an Excel spread sheet.  I have two issues.1) The is no real unique key data.   I update the 3rd, 5th, and 8th row in the data table and what the updates written back. I can rewrite the entire sheet if it will let me update.2) We will need to make single value changes.  We have a lot of fields and our users change the names of the columns to suit their needs.  Therefore we always access the columns by position.  For example, table.item(4).  How can we write this change a single row.  That is, is there a way to reference the column position, not the name, in the SQL update statement.Thanks,Jerry

    DB:2.77:Excel Update Via Field Position, Not Name. Also, No Key Field cx

    Hi,1) To acccess the row uniquely, you can include a ID.2) To access specific column you can use columnName as well. E.g
    table.Rows[0][

    ColumnName] // C# Codeor based on positiontable.Rows[0][2] // C# Code - where 0 is the row and 2 is the column.

  • RELEVANCY SCORE 2.77

    DB:2.77: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.77: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.77

    DB:2.77:Reverse Search Possible In Excel 1s


    Hi there,

    I have a data set in Excel 2013 with product names in the first column and details about the products in the corresponding columns like the price, size, weight etc. The problem is when I search for the product names. Most of the time, I have to search the
    product names in the exact order or else it would say "We couldn't find what you are looking for".

    For instance, if there is a product named "ALUMINIUM PROFILE SHEET 0.7 MM OFF WHITE", I can't find it if I type only the keywords in random order like "profile sheet aluminium" or "sheet aluminium". I have to search like this because I have 14,000 products(rows)
    and I can't remember the exact order of all the products and most of them are home improvement items with scary names and details.

    Another problem, If I try to do text filters and then select "contains" option to search using keywords of the product names I get a message saying "This column has more than 10,000 unique items. Only the first 10,000 unique items are displayed".

    Is there any way to sort out this search problem of mine? :(

    AikoVD

    DB:2.77:Reverse Search Possible In Excel 1s

    Hi,

    You may refer to my solution at this link -
    http://www.ashishmathur.com/perform-a-lookup-with-inexact-text-strings-andor-spelling-mistakes/

    Hope this helps.

  • RELEVANCY SCORE 2.77

    DB:2.77:Unique Constraint On Multiple Columns xj


    I have a unique constraint on two columns in a typed dataset. I've looked in the designer code and it is there. But nothing happens when it is violated. The single column unique constraints are fine. Anyone have this problem before? Any suggestions? TIA.

    DB:2.77:Unique Constraint On Multiple Columns xj

    Correction, the single column unique constraints work on all other tables in the dataset but this one.

  • RELEVANCY SCORE 2.77

    DB:2.77:Text To Columns 1s


    I've read through several previous posts on this topic, but I'm still unable to get this to work. I have a list of names that appear as "Last, First" in one column. In order to be able to perform other functions using these names, I need them to be split into two columns with no comma. In excel, this is easy - there is a text to columns wizard that enables this. I can't find this in Numbers. I converted the column into a .csv file, but when I open the file, no option to separate the text appears.

    Thanks in advance!
    db

    DB:2.77:Text To Columns 1s

    Why this isn't a built in part of numbers

    As I am a end user like you, I don't know the response.

    Maybe it's because XL is quite 20 years old while Numbers appeared in summer 2007

    _Go to "Provide Numbers Feedback" in the "Numbers" menu_, describe what you wish.
    Then, cross your fingers, and wait _at least_ for iWork'10

    Yvan KOENIG (VALLAURIS, France) dimanche 20 septembre 2009 11:11:47

  • RELEVANCY SCORE 2.77

    DB:2.77: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.76

    DB:2.76:Unique Columns And Logical Deletion 8d


    In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships.  However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for active rows.  I'm hoping someone can provide some advice on how to best handle this situation.
     
    Here is an example to work from.  Suppose we have a table that contains job positions.  The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been deleted.  This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company.  You can substitute your own scenarios here as there are quite a few.
     
    Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want.  However we will want to be able to delete a position but later add a new position with the same name.  One could argue that we should just undelete the original position but that is not often a good idea so assume undelete is not an option.  Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name.  We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.
     
    My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column.  The UniqueName column is set (via a trigger) to the Name column when the position is active.  When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness.  This is handled by the system and not exposed to clients.
     
    Any advice or alternatives on this approach (preferably with advantages and disadvantages)?  Thanks.
     
    Michael Taylor - 5/31/07
    http://p3net.mvps.org
     

    DB:2.76:Unique Columns And Logical Deletion 8d

    For data that doesn't change often an archive table adds more work than it benefits.

  • RELEVANCY SCORE 2.76

    DB:2.76:Sys.Indexes.Is_Unique Vs Sys.Indexes.Is_Unique_Constraint fd


    What is the difference between these two columns for an index?

    I created a dummy table, like this.
    create table TBL(col1 int, col2 int)

    Next I created a unique contraint and a unique index, like this.
    alter table TBL add constraint UNQ_TBL unique (col1)
    create unique index IX_TBL on TBL (col2)

    They both show up in Mgmt Studio as unique non clustered indexes.

    But when I run this query, there is a difference.
    select name, is_unique, is_unique_constraint from sys.indexes
    where object_id = object_id('TBL') and index_id 0

    Why is there a need to distinguish between these two columns in sys.indexes?

    DB:2.76:Sys.Indexes.Is_Unique Vs Sys.Indexes.Is_Unique_Constraint fd

    Another difference: table variables and the returned table in a multistatement table function can have unique constraints. But they can't have indexes.
    Tom

  • RELEVANCY SCORE 2.76

    DB:2.76:Total Unique Columns On Different Sheets 9f


    Hello,
    I have 5 sheets
    Each sheet has columns unique and total
    I would like to find the same value on each sheet in the unique column, then total up all the sums.

    so

    Sheet1
    Unique total
    a 4
    b 2
    c 9
    d 7

    Sheet2
    Unique total
    a 2
    d 4
    e 6
    f 7

    Sheet3
    Unique total
    a 2
    b 4
    e 6
    f 7

    On the next sheet I would like to have

    Unique total
    a 8
    b 6
    c 9
    d 11
    e 12
    f 14

    Is this possible with formulas?

    thanks

    DB:2.76:Total Unique Columns On Different Sheets 9f

    ... I would like to find the same value on each sheet in the unique column, then total up all the sums ...Is this possible with formulas?

    If your sample truly represents the actual situation, you can stack a few
    standardand arrayformulas together to achieve your results.

    The array formulain Sheet4's A2 is,

  • RELEVANCY SCORE 2.76

    DB:2.76:Regarding Composit Primary Keys c3


    Hi,

    my parent table having composit primary key on two columns.

    i need to use these two columns for foreign key in child table.

    after creating the parent table , i am facing problem to create child table it always shows no unique/primary key found

    is there any way to do like this?

    Regards,
    sh

    DB:2.76:Regarding Composit Primary Keys c3

    While composite primary keys are legal in data modelling, many people dislike them when it comes to practice. This is partly because they take up more space and require more typing (extra join clauses), and partly because of a suspicion that composite keys are more susceptible to change and hence more trouble over the lifetime of the database.

    So it is common to treat the composite key as if it were a business key: enforce its uniqueness with a unique constraint but define a surrogate (synthetic) primary key which uses a single column for use in foreign keys.

    Cheers, APC

  • RELEVANCY SCORE 2.76

    DB:2.76:Unique Vs Non-Unique Clustered Indexes d7


     
    Hello All,
     
      We are on SQL Server 2000.. We have a clustered index on two columns of a table and neither of the two columns are part of the primary key. What we have noticed is that sometimes the table is accepting duplicates for the clustered index rows.. My assumption was that making the two columns part of a clustered index ensures that data in the two columns combined would be unique because of a 'uniqueifier' added by SQL Server...
     
     But looks like my assumption may have been wrong... Do we need to re-create the clustered index as a unique clustered index in order to enforce uniqueness in the two columns... Could you please advise.
     
    Thanks for your assistance.
     
    Siri

  • RELEVANCY SCORE 2.75

    DB:2.75:Random/Unique File Names In Air? z7


    Is there any way to generate unique file names in Air in a
    similar way to tempnam() (in python its os.tempnam() )?

    My code generates pictures and saves them to the desktop, and
    I wanted an easy way to generate unique file names (or "almost"
    unique..)

    Thanks

    DB:2.75:Random/Unique File Names In Air? z7

    Thanks Aaron!
    The tempFile option seems like what I was looking for.
    I have a distributed application that takes many images and
    synchronizes them with many nodes, so I care more about not having
    to deal with multiple files with the same name than with
    overwriting :)

    Nadav

  • RELEVANCY SCORE 2.75

    DB:2.75: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.75: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.75

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



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

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

    sw_u_management_port_ip_address

    loc_u_customer_data_center_name

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

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

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


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

  • RELEVANCY SCORE 2.75

    DB:2.75:Addm Scan Data Into Cmdb 8j



    We areusing ADDM to bring in field data into the CMDB. We are also using an external source to enrich the ADDM accordingly.

    Description about the two variant configurations:

    A - Duringthe enrichment process we are creating duplicate records in COBA_DNNameCollection to reflect unique DNS names in the CMDB and relate it back to IPEndpoint:
    IPEndpoint-- COBA_DNNameCollection

    B - Duringthe enrichment process we are creating duplicate records just in the IPEndpoint toreflect unique DNS names in the CMDB IPEndpoint

    Question about the variants:

    With variant B (seems to be the best choice) we can use an existing CMDB Class only.But what about ADDM if ADDM will find the duplicates in the IPEndpoint does ADDM will reduce them to one IP record? If yes, I would suggest variant Ainstead of variant B.

    Can somebody provide some background knowledge about using variant A?

    -Hans

    DB:2.75:Addm Scan Data Into Cmdb 8j


    I am still not clear what you are trying to do here but the correct place to add additional data is the CMDB.

    You should not be altering data in the BMC.ADDM import dataset as it is a direct transform of the ADDM data.

    Merging your imported additional data and the BMC.ADDM data into your gold dataset is really a task for the reconciliation rules and you may get better advice over in one of the CMDB forums.

  • RELEVANCY SCORE 2.74

    DB:2.74:Download Link As A Column Of Report j8


    Hi All,

    I really tried from my side to somehow implement this requirement but couldn't find a way.

    Requirement: A report with two main columns - One has unique value and the other has a download link. This download link need to be used to fetch a set of data from the database table using the other column's (unique) value. Then, user should get a popup on his screen to download the fetched data in csv format.

    Please give some guidance or pointers to resolve this issue.

    Regards,
    Mangal

    DB:2.74:Download Link As A Column Of Report j8

    Hi Ben,

    Thanks a lot for the solution. Hope I continue to get your guidance in future as well.

    Regards,
    Mangal

  • RELEVANCY SCORE 2.74

    DB:2.74:Compare And Count Two Worksheets ap


    Hello,

    I have in one sheet a productsheet with names,prices and barcodes, in the other sheet i have a list which i made with a scanner and only barcodes(in columns). Is it possible to compare the barcodes and count the unique barcodes and add a column to the first sheet with the counted result?

    DB:2.74:Compare And Count Two Worksheets ap

    Sorry (Jerrold and Yvan) for my "dummies" numbers talk/question (and perhaps it also because of my translation from dutch to english ). But thank you, this it exactly what i wanted Yvan many thanks from Holland for your fast and perfect answer. Mercy beaucoup pour votre temps

  • RELEVANCY SCORE 2.74

    DB:2.74:How Do I Filter Two Columns Of Names To See What Names Are The Same? c1


    I have two lists of names (one with 2,000+ names and one with 300+ names) I want make a column that shows the names that are not in both columns and the names that are. Is there an automated way of doing this? Basically, I have 2,000 friends on facebook and only 300 of them like my business page. I want to know which friends to market too and which friends to stop annoying Any help would be greatly appreciated!

    DB:2.74:How Do I Filter Two Columns Of Names To See What Names Are The Same? c1

    Thanks a ton! That's exactly what I wanted About 90% of my comp knowledge comes from people like you contributing to forums like this so thanks a ton!

  • RELEVANCY SCORE 2.74

    DB:2.74:Filtering Rows Using Unique Identifier ds


    I am trying to find a formula to help select a random sample in a universe file. The random sample would select the first two rows of each identifier in a column.

    Example:
    We have a file with 200 Agent Names in Column A. In Column B we have a Unique Identifier (lets say a Case Number). There are a total of 20,000 rows on the sheet; and we'd like to select two cases per agent.

    Is there a formula or code that could help filter this data out or select the data?

    DB:2.74:Filtering Rows Using Unique Identifier ds

    By randomizing between1 and the COUNTIF() of the agent minus 1, in a helper column you can get two sequential (by agent) case numbers. Assuming that the case numbers are unique, the rest of the data can easily be pulled from the master
    file.

    That sample workbook is available from my OneDrive here for you to reference and download,

    Random_Cases.xlsx

  • RELEVANCY SCORE 2.74

    DB:2.74:Unable To Create Index - Duplicate Records Found kk


    I have a table containing 15,000 records.
    I want to create a unique index on two columns.

    alter table sample add constraint sample_uk unique (sample_id,sample_source,sample_dest);

    As the table is having duplicate data, i am unable to create the index.

    Please tell me how to know, what are the duplicate records present in the table for these 3 columns.

    Regards
    Raghunadh

    DB:2.74:Unable To Create Index - Duplicate Records Found kk

    use this query to remove duplicate rows

    DELETE FROM sample a
    WHERE rowid (
    SELECT min(rowid)
    FROM sample b
    WHERE a.sample_id = b.sample_id );

    UG

  • RELEVANCY SCORE 2.74

    DB:2.74:Should Window-Name Be Unique ? xx



    I used names unique in the context of the page and it seems that I am not getting the expected instance (instances have globally unique names)

    DB:2.74:Should Window-Name Be Unique ? xx


    What do you mean ? WIndow name should be unique in the context of the page, but you can have multiple windows with the same name on different pages

  • RELEVANCY SCORE 2.74

    DB:2.74:Best Formula To Calculate These Multiple Conditions And Return A Summed Value pa


    I have a large data set, and will need to calculate a large number of summarized data cells, so I'm struggling with how to do this at all, and then if there are multiple methods, which is the most efficient so I don't get locked up in long 'processing....'
    updates.

    Sheet1 contains a list of Cities in column A, Utilities in Column B, and dollars per month in columns C-N
    Atlanta Power 55 65 40 90 110 85 (etc)
    Atlanta Water 19 22 31 26 27 19 (etc)
    Atlanta Sewer 8 11 13 14 9 11 (etc)
    Denver Power 71 82 65 58 61 (etc)
    Denver Water (etc)

    Sheet2 contains a list of unique city names:
    Atlanta
    Denver

    For each city on Sheet 2, I need to tally the total dollars for all utilities excluding sewer

    If I was only tallying one column of results, I would use sumproduct, but when I try to include multiple columns in the third parameter, I'm getting incorrect results
    =sumproduct((Sheet1!A1:A50000 = "Denver")*1, (Sheet1!B1:B50000 "Sewer")*1, (Sheet1!C1:N50000))

    I also tried =sumifs, with the same problem when trying to return dynamically the sum of multiple columns when the first two columns match my conditions.

    Suggestions on formula/approach would be greatly appreciated! I'm hoping to just get an overall total (Atlanta = 589, based on all the electric and water numbers shown above example). My alternative is to load the output page with formulas to pre-calc each
    of the 12 months, then sum them up, but I'm hoping to find an efficient method to sum all these values within one formula per location.
    Many thanks,
    Keith

    DB:2.74:Best Formula To Calculate These Multiple Conditions And Return A Summed Value pa

    I had the syntax wrong somewhere, when I re-enter it using your last suggested formula, I get the expected result. I thought it was the C1:N50000 that was giving me problems. Fortunately, it is working now- the time difference just for this first measure
    for 100 locations is significantly different than the non row-limited formula (about a second vs over a minute).

    Many thanks!
    Keith

  • RELEVANCY SCORE 2.73

    DB:2.73: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.73: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.73

    DB:2.73:System Field $Field Set Analysis Alternate States s8



    Hi,

    So I have a unique problem. I want to give the user the ability to select two columns/field names from a listbox that contains the contents of $Field(the System field showing all of the column names). The user will select two different columns/field names. I will have a graph that will take these two seperate selections and create a graph showing both selections.

    For example:

    -Two field names I am using are ACDCalls and ABNDCalls

    I was thinking that I will have two list boxes of $Field. Using Alternate States, I will place a different state on each of the boxes so that I can select two seperate fields from the same $Field. I will have a chart in the inherited state. I will have two expressions such as:

    (sum( {State1}[$Field]))

    (sum( {State2}[$Field]))

    It does not work. The graph shows no data. However, when I change things up and use a different field instead of $Field, I am able to use alternate states and set analysis, so it is not my syntax that is incorrect. I am guessing that using a systemfield such as $Field with alternate State and Set Analysis will not work.

    Could anyone give me a better option or fine-tune my current idea?

    DB:2.73:System Field $Field Set Analysis Alternate States s8


    Example:

    =if([$Field]='acdtime',Time(( (sum( $(=only({State1} $Field)) ) ) )/(3600*24),'hh:mm:ss'),

    if([$Field] = 'acdcalls',num(sum(acdcalls),'####'))

    I will have two list boxes that where the user will select from a list of system fields to compare two sets of data in one graph. Since the list of fields come from the same system field($Field), I have to break it up into Alternate States so that the user can select two values from two different list boxes. The problem I now face is that the user can potentially select a time value(acdtime which will be displayed in hh:mm:ss) and a number value.

    I could leave the expression as :

    Time(( (sum( $(=only({State1} $Field)) ) ) )/(3600*24),'hh:mm:ss'),

    However, if the user selected his second value as a number and not a time value, the number would be shown in hh:mm:ss as well. I hope it is becoming clearer apologies!

  • RELEVANCY SCORE 2.73

    DB:2.73:Using Unique In Cte...... mj


    Hi,
    I am using CTE instead of a TABLE in my stored proc. Before when I was using TABLE, while declaring the fields for that table, I am specifying UNIQUE for two columns....but when I try to use same in CTE, it gives an error.....
    Is it possible to use UNIQUE in CTE........

    DB:2.73:Using Unique In Cte...... mj

    alternatively, you can use DISTINCT operator in SELECT statement for CTE;Vishal Gajjar

  • RELEVANCY SCORE 2.73

    DB:2.73:Find And Drop Duplicate Rows mz



    I have 2462 rows by 13 columns. Some of these rows contain duplicate values in 12 of the 13 columns. The column that always contains unique text is called PK.SAMPLE. How can I find these rows of almost exact duplicates and remove them from the data set?

    Thanks,

    mgm

    DB:2.73:Find And Drop Duplicate Rows mz


    I wrote a script recently to check to make sure I wasn't adding duplicate rows to a data table. Maybe this will help.

    The script iterates through all the rows in the table (i counter) and each of my 9 columns (k counter) comparing the values to those in the last row. If it finds any rows that are equal, it will delete the last row.

    Hope that makes sense. You would have to modify it to look for any duplicates, but the script should still be pretty straightforward.

    There are issues displaying my script.

    Message was edited by: MJaworski

  • RELEVANCY SCORE 2.73

    DB:2.73:Re: How To Find Out The Null Columns In The Table? 9z


    Hm,
    Thanks so musch for your instant reply.But,that is not my expectation. Basically, we don't know what are the columns has null values. But, we want to get those column names.

    Note: Same columns should be null for all the rows in the table . I am getting the data dynamically. So, just to indentify the names of columns those have null.

    Please help me.

    Regards,
    Vissu....

    DB:2.73:Re: How To Find Out The Null Columns In The Table? 9z

    Thanks so much Xtender , i am going to implement your logic.

    Regards,
    Vissu....

  • RELEVANCY SCORE 2.72

    DB:2.72:Break The Text In Two Columns cs


    I have first and last names listed in one clumns.I would like to break them into two columns (First Name and Last Name separate). I am using Excel 2003. Thank you in advance for your help.

    DB:2.72:Break The Text In Two Columns cs

    Highlight the entire column with the names in it to start before doing the Data TextToColumns, etc
    hth
    Vaya con Dios,
    Chuck, CABGx3

  • RELEVANCY SCORE 2.72

    DB:2.72:List Names From Two Columns In Aphlabetic Order 1a


    I have two columns of names C22:C46 and C51:75 and want to list the names in A-Z and any blanks on bottom, order. There are blank cells to between some of the names as well.
    I couldn'tfind an ARRAY formula on the forums to justspecificallysort the list from A-Z order to be used on a Drop-Down menu.

    DB:2.72:List Names From Two Columns In Aphlabetic Order 1a

    I'm not sure. I used both Gary's and Mike's formulas and both had spaces between the names.

  • RELEVANCY SCORE 2.72

    DB:2.72:Similar Data Search jp



    I have two files that I merged into one. I have two columns with names. I want to be able to query both columns and find names that are similar (not same), ie Sam Smith and Sam A. Smith. Doable?

    DB:2.72:Similar Data Search jp


    Doable? Yes.

    One source of a bunch of code, and a couple of articles, is the site where the following article can be found:

    http://www.sconsig.com/sastips/obswww15.pdf

    Conversely, if your site can afford it, SAS also has a product designed to facilitate such tasks, namely:

    DataFlux - Data Quality, Data Integration and Master Data Management

  • RELEVANCY SCORE 2.72

    DB:2.72:Using Access Database As A Secondary Dataconnection mk


    I have a repeating table which has 3 columns. The first column contains a drop-down list control containing a set of names (each unique) populated from a table in an Access database (secondary data connection). I need values from 2 other fields in the database table to appear in the other two columns of my repeating table pertaining to the same record as the name chosen in the drop-down control - how do I do this?
    I posted this before but it was way longer and more confusing. Hopefully someone can help this time :-)

    DB:2.72:Using Access Database As A Secondary Dataconnection mk

    Baskafishu said:

    I have a repeating table which has 3 columns. The first column contains a drop-down list control containing a set of names (each unique) populated from a table in an Access database (secondary data connection). I need values from 2 other fields in the database table to appear in the other two columns of my repeating table pertaining to the same record as the name chosen in the drop-down control - how do I do this?
    I posted this before but it was way longer and more confusing. Hopefully someone can help this time :-)

  • RELEVANCY SCORE 2.72

    DB:2.72:Re: Combining Two Data Sets In A Graph c3


    the above code wouldn't work when we force the row names to be unique.

    When I UNCHECKED "MAKE ROW NAMES UNIQUE" and used //ROW instead of //DATA in above code, Only then, it's working fine. BUT I have to make row names unique as per the requirement.

    Why isn't //DATA reference working. are the graphs designed to handle only ROW level data please help.

    DB:2.72:Re: Combining Two Data Sets In A Graph c3

    hello.
    I have a report with two data sets with unique row names checked.
    I have to combine the two datasets in a single graph.
    This isthe code I am using now:

    chart:
    Graph seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK" markerDisplayed="true" markerShapeInLegend="true"
    graphicAntialiasing="true" textAntialiasing="true"
    markerTooltipType="MTT_TEXT" lineDataLineDisplayed="true"
    frameSizeAutomatic="true" fitToContainer="true"
    borderColor="#000000"
    LegendArea visible="true" automaticPlacement="AP_NEVER" position="LAP_BOTTOM" /
    LocalGridData colCount="{count(xdoxslt:group(.//DATA, 'reporting_dates'))}" rowCount="{count(xdoxslt:group(.//DATA, 'gl_account_name'))}"
    RowLabels
    xsl:for-each-group select=".//DATA" group-by="gl_account_name" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    Labelxsl:value-of select="current-group()/gl_account_name" /
    /Label/xsl:for-each-group
    /RowLabels
    ColLabels
    xsl:for-each-group select=".//DATA" group-by="reporting_dates" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    Label
    xsl:value-of select="current-group()/user_" /
    /Label
    /xsl:for-each-group
    /ColLabels
    DataValues
    xsl:for-each-group select="//DATA" group-by="./gl_account_name" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xsl:variable name="G1" select="current-group()" /
    RowDataxsl:for-each-group select="//DATA" group-by="./reporting_dates"
    Cell
    xsl:value-of select="sum($G1[(./reporting_dates=current()/reporting_dates)]/BALANCE)" /
    /Cell
    /xsl:for-each-group/RowData/xsl:for-each-group
    /DataValues
    /LocalGridData
    /Graph

  • RELEVANCY SCORE 2.72

    DB:2.72:Dql To Add Unique Constraint In Registered Table 9z


    Hi experts,I created a registered table and now i want to add unique constraints on two columns in this table.Please help me out in this .Waiting for your response on the same.regardsKriti

  • RELEVANCY SCORE 2.72

    DB:2.72:Difference Between Unique Constraint And Unique Index fp


    1. What is the difference between unique constraint and unique index when unique constraint is always indexed ? Which one is better in this case for better performance ?
    2. Is Composite index of 3 columns x,y,z better
    or having independent/ seperate indexes on 3 columns x,y,z is better for better performance ?
    3. It has been very confusing for me to decide which columns to index, I have indexed most foreignkey columns, is it a good idea ? We do lot of selects and DMLS on most of our tables. Is there any query that I can run and find out if indexes are really being used and if they are improving any performance. I have analyzed and computed my indexes using ANALYZE index index_name validate structure and COMPUTE STATISTICS;

    null

    DB:2.72:Difference Between Unique Constraint And Unique Index fp

    Well, here is my opinion.

    1) composite indexes should (in theory) take up less space (over-head) than three individual indexes. Look at your queries, if you can, structure the queries so that x is first in the where clause, y is second and z is third. Depending on the version of Oracle you are using, create the index with x 1st, y 2nd, and z 3rd. Or visa versa.

    2) Only if queries match the Index structure.

    3) The only documentation I have seen on Indexes would suggest the following:

    1: Oracle 8i will never use an index on a table that has fewer than 100 rows.

    2: Oracle will not use an index that pulls back more than a few percent of the rows in the table.

    3: Queries with inequalities, null checks, etc. will not use an Index.

    Reference: Oracle Performance and Tuning Tips Techniques by Oracle Press.

    Index everything and waste space.
    Index poorly and get the performance blues.
    With that in mind index well and prosper.
    null

  • RELEVANCY SCORE 2.71

    DB:2.71:I Have An Excel Document With Some 10 Columns In It With Some Data...Now I Want To Find Which Combination Of Columns Gives Me Unique Data cj


    i have an excel document with some 10 columns in it with some data...
    now i want to find which combination of columns gives me unique data....
    it may be combination of col1 ,col2 ,col3 or it may be C1,c5 or it may be C1,C4,C8,C9..Soon...
    i want to find which comibnation of column gives me unique data

    so that i can find key coluumns which gives me unique data

    DB:2.71:I Have An Excel Document With Some 10 Columns In It With Some Data...Now I Want To Find Which Combination Of Columns Gives Me Unique Data cj

    I had a very similar question several years ago:

    https://groups.google.com/forum/?hl=enfromgroups#!topic/microsoft.public.excel.programming/4C_yJDWDvlk

    Please see the references and John Coleman's post.

  • RELEVANCY SCORE 2.71

    DB:2.71:Replication Config kj


    How do I configure two caches on separate machines as a single replicated cache?

    I assume they have to have unique names:
    rc = CacheFactory.getReplicatedCache("unique");

    Is it something in tangosol-coherence.xml?

    Thanks.

    DB:2.71:Replication Config kj

    Hi Mike,

    Rob, thanks for the speedy reply.No problem, anytime.

    So with this setup will there be a cache named
    "unique" on machine1 and a cache named "unique" on
    machine2 and they're magically kept in-sync?Don't think of it as 2 separate caches but one clustered cache or java.util.Map for all instensive purposes. And yes, we take care of all the clustering details behind the scenes including coherence, concurency, fail-over, etc.

    The command line tool only shows 2 caches, they're
    both replicated. Is there any way to see each
    instance of the replicated cache?Do you mean on each instance of the command line app you see 'Map (Default):'? If this is the case, again think of it as one clustered cache. 'Default' is a just the default Replicated Cache. To see all the caches currently running on an instance use the 'maps' command.

    While I'm begging, is there anyway to see the sync
    between the replicated caches?An easy way to do this is to do a 'put hello world' on machine1 and then on machine2 do a 'get hello'. This will return 'world'. To test concurrency, on machine1 'lock hello' and on machine2 'put hello world2'. This will cause an exception since machine1 has the key 'hello' locked.

    You can also sign up to listen to the cluster-wide events such as insert, update and remove by using the 'listen' command.

    Later,
    Rob Misek
    Tangosol, Inc.
    Coherence: Cluster your Work. Work your Cluster.

  • RELEVANCY SCORE 2.71

    DB:2.71:Primary Key - Better Use Integer Id Or Unique Composite Columns? f3


    Hello,
    When design a table, I have to put primary key.
    Table: PostTag
    Columns: PostTagID (int - Need??), PostTypeCd (smallInt), Tag(varchar(50)), Rating (int), Desc (varchar(250))
    Columns PostTypeCd and Tag should be unique.

    In sql server, I've created another new column PostTagID for primary key purpose only and put unique constrain on those two unique columns.
    However I found out that most Oracle people set those two unique columns as a primary key.
    Which way is better?
    1. Set PostTypeCd and Tag primary key OR
    2. Add PostTagID as primary key and Set Unqiue constrain on PostTypeCd and Tag columns

    What's cons an pro regarding Performance, design practice or stored-procedure writing?
    Anyone has idea?
    Thank you!MCSD .NET, SCJP, SCJWD

    DB:2.71:Primary Key - Better Use Integer Id Or Unique Composite Columns? f3

    Abosolutely number #2. I agree with Sylvain also... and...
    Using a number for a clustered index is going to perform much better. Carrying multiple columns for a join is not going to be fun, when there a lot of tables like that also, so option 2 just from a maintenance perspective is better. The unique index on the
    columns will give you the results you want. So make it easy on yourself and everyone else and have an ID column. I always design my tables this way. I seeing these composite keys lately in some other applications, and just got curious what everyone else thought.
    Imagine doing a report on a database with a different collation too , not fun, option 1 is out anyway I look at it...

  • RELEVANCY SCORE 2.71

    DB:2.71:How Can I Search For Partial Matching Words In Various Columns Without Needing To Provide Search Text? ax


    I have a list of names in three columns - B2:B425, D2:406, E2:30 where they were input by different people, so the names are worded differently. For example, I can have the name Handler, Jones, Wright and someone else has the name
    listed as Handler Corp. What I need is to find a formula or VBA macro code that can search through my list and notice the possible duplicates and highlight them. Since they are all different names, I cannot give it a unique text to
    search.

    I found a code posted in this forum from some time ago (for two columns) but it highlighted all these names that had no partial words in common. Perhaps you can look over the code below and modify it for me or provide me with another one? Please let me know
    if you need any further information to guide me.

    Sub HighlightDups()
    Dim rg1 As Range, rg2 As Range, c As Range, d As Range
    Dim sTemp As String, sTempWords() As String, sTempDWords() As String
    Dim re As Object, mc As Object
    Dim i As Long, j As Long
    Dim sFirstAddress As String

    Set rg1 = Range(B2, Cells(Rows.Columns.Count, B).End(xlUp))
    Set rg2 = Range(D2, Cells(Rows.Columns.Count, D).End(xlUp))

    Set re = CreateObject(vbscript.regexp)
    re.Global = True
    re.ignorecase = True

    With Range(rg1, rg2)
    .Font.Color = vbBlack
    .Font.Bold = False
    .Interior.Color = xlNone
    .FormatConditions.Delete
    End With

    For Each c In rg1
    re.Pattern = \b\w\b
    If re.test(c.Text) = True Then
    Set mc = re.Execute(c.Text)
    ReDim sTempWords(0 To mc.Count - 1)
    For i = 0 To UBound(sTempWords)
    sTempWords(i) = mc(i)
    Next i

    For i = 0 To UBound(sTempWords)
    Set d = rg2.Find(What:=sTempWords(i), _
    LookIn:=xlValues, _
    LookAt:=xlPart, _
    MatchCase:=False)
    If Not d Is Nothing Then
    re.Pattern = \b sTempWords(i) \b
    sFirstAddress = d.Address
    Do

    If re.test(d.Text) Then
    With c
    .Font.Color = vbWhite
    .Font.Bold = True
    .Interior.Color = vbBlue
    End With
    With d
    .Font.Color = vbWhite
    .Font.Bold = True
    .Interior.Color = vbBlue
    End With
    End If

    Set d = rg2.FindNext(after:=d)
    Loop While Not d Is Nothing And d.Address sFirstAddress
    End If
    Next i
    End If
    Next c
    Set re = Nothing
    End Sub

    DB:2.71:How Can I Search For Partial Matching Words In Various Columns Without Needing To Provide Search Text? ax

    Programming/Code related questions should really be posed in one of the following forums
    Excel for Developers
    http://social.msdn.microsoft.com/Forums/en-US/exceldev
    Microsoft Office Programming
    http://answers.microsoft.com/en-us/office/forum/customize?page=1tab=alltm=1361680524815
    Tony Chen
    TechNet Community Support

  • RELEVANCY SCORE 2.71

    DB:2.71:Can You Give Names To Groups? cx


    Can one give unique names to groups in a slide?

  • RELEVANCY SCORE 2.70

    DB:2.70:Comparing Text In Two Columns 1j


    In one column i have a list of names, in other column i have list of file names which may contain these names - for example in column A i have name like John Doe and in column B have list of file names like john_doe_resume_2012.doc, how can i find out
    if i have resumes of all the names in first column

    DB:2.70:Comparing Text In Two Columns 1j

    Many thanks Mike. This works ! and it is matching up the names from column A with those in Column B which-are-formatted-exactly-the-same-way as in column A . I'll figure out the way to clean-up whole list and apply this.

  • RELEVANCY SCORE 2.70

    DB:2.70:Remove Duplicates In Excel Between Columns xf


    Hello. I have two columns of names. I need to subtract duplicates between the two columns (Column A minus Column B). This is for Office Professional 2010. If anyone has an answer, I would be grateful.

    Amy

    DB:2.70:Remove Duplicates In Excel Between Columns xf

    Hi,

    Please clarify your question. Give the data set and expected output

  • RELEVANCY SCORE 2.70

    DB:2.70:Please Clarify This Dynamic Sql 39


    i have a table with say - rule_component

    which has two columns

    1.db_table
    2.from_clause

    The two columns contains tables for example

    db_table
    ------------
    emp
    dept
    bonus
    emp
    def

    from_clause
    ----------------

    emp,dept,bonus,dept,emp,abc

    My requirement

    i want to find the duplicate tables in both the from_clause and db_table column
    and populate in my from clause which are unique to both the columns

    say example

    select ......

    from emp,dept,bonus,abc,def

    DB:2.70:Please Clarify This Dynamic Sql 39

    i have a table with say - rule_component

    which has two columns

    1.db_table
    2.from_clause

    The two columns contains tables for example

    db_table
    ------------
    emp
    dept
    bonus
    emp
    def

    from_clause
    ----------------

    emp,dept,bonus,dept,emp,abc

    My requirement

    i want to find the duplicate tables in both the from_clause and db_table column
    and populate in my from clause which are unique to both the columns

    say example

    select ......

    from emp,dept,bonus,abc,def

  • RELEVANCY SCORE 2.70

    DB:2.70:How Do I Insert Unique Reference Values With Lookup + Ole Db Command? c8


    Hi, I am trying to insert data from an Excel file to a SQL Server database, using SSIS packages. (I have simplified the actual package, to narrow in the problem, so it's like a school book example). The Excel file has three columns: Name, Product, Supplier. The database has a table TestTable with three columns: Id (identity int), Name, SupplierId The SupplierId is mapped to a reference table named Supplier, with columns: Id, Name. Short description of the SSIS package: Excel Source -- Sort (on Name) -- Derived Column (adding SupplierId with dummy key 1) -- Lookup (trying to match Supplier names with reference table, replacing SupplierId if found) If the Lookup finds the supplier, move with a SupplierId. Sort on SupplierId. If not, insert new suppliers in reference table Suppliers. ** THIS IS WHERE THE PROBLEM IS. ** Lookup to get the SupplierId of the newly inserted suppliers. Sort on SupplierId. Merge and Lookup to either insert with OLE DB Destination or update with OLE DB Command. The problem is: When making the insert of new suppliers, not only unique suppliers are inserted. If, e.g., the Excel file has two records with supplier FOX LTD, and the supplier does not yet exist, it adds to records to the reference table. I of course want it to add unique suppliers. I tried to add a Sort before the insert OLE DB Command, removing rows with duplicate sort values, but then I of course will lose the records that I want to move on with... This must be an easy thing so solve, but I really can't find out how. Is there anyone in here who can help me out? If any code or further explanation is needed, I will provide it.

    DB:2.70:How Do I Insert Unique Reference Values With Lookup + Ole Db Command? c8


    Try SSIS sort component.

    The Sort component actually has two functions:

    -It sorts data (that much is obvious)
    -It can eliminate duplicates

    Hence you can just insert the distinct values.

    Not good. Sort is a blocking operation and only use SORT task when required absolutely. Always resort to Order By clause and as far as duplicates are concerned, again this should be handled by appropriate T-SQL, definitely not using SORT!!!Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar |
    My Blog