none
Using DISTINCT with SELECT INTO

    Question

  • Basically I'm trying to get this statement to work:

    select distinct Substring(categoryname,0,charindex('/', categoryname)) 
    into Category
    from importdata
    where CategoryName is not null 

    Here is my error: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

    This is kind of two questions in one. I'm trying to get every record from a column "CategoryName" up to the first "/" but also the ones that don't have a "/". And I need to select them into another table.

    records in a table example:

    abc
    abcd/ef
    abcd/ef/ab & c
    abcdef/ef

    Here's what would be selected:

    abc
    abcd
    abcd
    abcdef

    Monday, June 24, 2013 11:18 PM

Answers

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed). Temporal data should use ISO-8601 formats; it is not just part of SQL, but the other ISO Standards. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    What little you did post is wrong. There is no such thing as a “category_name” in RDBMS; you can have a “<something>_category” or a “<something>_name”, but not that pile of attribute properties chained together.  Likewise, a lookup table of categories is particular and not generic. Read any book on data modeling or ISO-11179 standards. 

    The old Sybase SELECT ..INTO syntax is a nightmare. It looks like ANSI/ISO Standard sytax for a singleton select, but creates a table instead. A good SQL programmer would use INSERT INTO, and have a real table with keys and constraints in his schema. 

    >> This is kind of two questions in one. I'm trying to get every record [sic: rows are not records!] from a column "generic_category_name" up to the first "/" but also the ones that don't have a "/". And I need to select them into another table. <<

    No, you need to do parsing and data scrubbing in the input layer of the system; this is how a tiered architecture works. The kludge for your code is to have a table with an explicit column name that you did not have with the INTO clause

    INSERT INTO Foobar_Categories (foo_category) 
    SELECT DISTINCT SUBSTRING(generic_category_name + '/', 0, CHARINDEX('/', category_name)-1) 
    FROM Importdata

    Add a sentinel to the end of the string, then cut out the string up to either the first slash or the sentinel. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, June 25, 2013 4:32 AM

All replies

  • Try

    select distinct Substring(categoryname,1,charindex('/', categoryname+'/'))  AS Category
    into Category
    from importdata
    where CategoryName is not null 


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


    My blog


    My TechNet articles

    • Proposed as answer by DVR Prasad Tuesday, June 25, 2013 4:14 AM
    Monday, June 24, 2013 11:22 PM
    Moderator
  • Yes that helps me for the second part. Thanks! But how do I do a distinct select into another table? Using this substring function as well?
    Monday, June 24, 2013 11:44 PM
  • Yes that helps me for the second part. Thanks! But how do I do a distinct select into another table? Using this substring function as well?

    Yes,the distinct is needed, if there are multiple rows in importdata table with the same category value.

    Also, the substring was used to get the value prior to first '/'. So, even that is also needed, unless any other approach is used to get the specific part of category.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, June 25, 2013 12:22 AM
  • Yes, this is what I meant. I gave the name to the new column so it can be used to select into.

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


    My blog


    My TechNet articles

    Tuesday, June 25, 2013 2:39 AM
    Moderator
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed). Temporal data should use ISO-8601 formats; it is not just part of SQL, but the other ISO Standards. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    What little you did post is wrong. There is no such thing as a “category_name” in RDBMS; you can have a “<something>_category” or a “<something>_name”, but not that pile of attribute properties chained together.  Likewise, a lookup table of categories is particular and not generic. Read any book on data modeling or ISO-11179 standards. 

    The old Sybase SELECT ..INTO syntax is a nightmare. It looks like ANSI/ISO Standard sytax for a singleton select, but creates a table instead. A good SQL programmer would use INSERT INTO, and have a real table with keys and constraints in his schema. 

    >> This is kind of two questions in one. I'm trying to get every record [sic: rows are not records!] from a column "generic_category_name" up to the first "/" but also the ones that don't have a "/". And I need to select them into another table. <<

    No, you need to do parsing and data scrubbing in the input layer of the system; this is how a tiered architecture works. The kludge for your code is to have a table with an explicit column name that you did not have with the INTO clause

    INSERT INTO Foobar_Categories (foo_category) 
    SELECT DISTINCT SUBSTRING(generic_category_name + '/', 0, CHARINDEX('/', category_name)-1) 
    FROM Importdata

    Add a sentinel to the end of the string, then cut out the string up to either the first slash or the sentinel. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, June 25, 2013 4:32 AM
  • Thanks. I appreciate it the advice. I'm not super experienced and apologize for lack of proper forum etiquette. 
    Tuesday, June 25, 2013 3:49 PM