Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2013
    Posts
    14

    Unanswered: Select all columns expect one

    Hello!
    I'm trying to select all attributes that are generated dynamically into a view called DOR. The problem is that dataobject_id attribute is already being selected, so when I do:

    Code:
    SELECT o.dataobjected_id, o.name, DOR.*
    from tk_objects o inner join vw_dor dor on dor.dataobject_id = o.dataobject_id

    it gives me the error of duplicated columns.

    What can I do for when I do dor.* the dataobject_id doesn't come with the select?

    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should never use * in view definitions. List out all the columns properly. It's only laziness stopping you and that laziness could come back and bite you in the ass later.

    Also, if you want to include two columns of the same name in a view then you must differentiate them by using aliases.
    Code:
    SELECT a.duplicate_column_name alternative_name
         , b.duplicate_column_name As another_alternative_name
    ...
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2013
    Posts
    14
    The problem is that the DOR is dynamically generated, which means I can't select manually. Also I don't want to keep both dataobject_id. I only want to keep one time...

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This sounds like a truly horrible design.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2013
    Posts
    14
    Can you give me an answer if it is possible to be done?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I strongly suggest you change your design/approach. Is that an option?
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with GVee you should always explicitly define what columns you want to use.
    however you could try
    Code:
    SELECT o.dataobjected_id as ODataObjectID, o.name, DOR.*
    from tk_objects o inner join vw_dor on dor.dataobject_id = o.dataobject_id
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2013
    Posts
    14
    No, changing design isn't an option.
    Also healdem, doing that will keep both object_id, and I just want it to appear once.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case
    Code:
    SELECT o.name, DOR.*
    from tk_objects o inner join vw_dor on dor.dataobject_id = o.dataobject_id
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2013
    Posts
    14
    That way only one appears, but for me order matters... I need object_id, object_name and DOR.* without the object_id

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if that isn't working for you then you will have to select the columns you want in the order you want instead of being lazy and using "select *".

    you can't have it both ways
    you don't want to write out the columns, but you want them in a specific order

    where are you using this query?

    I've not seen native SQL queries used since way way back on IBM 360's running CICS.

    is there a front end?
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the consumer of this information
    how is being displayed
    where is it being used and in what form

    are you really givign your users access to query the db directly on and ad hoc basis
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This reads like you are trying to build an adapter for a shim for a kludge.

    You may not be able to answer this question due to an incomplete understanding of your own problem or due to organizational rules, but what are you trying to do in the real world (ignoring views and SQL)? We may be able to help you to craft a better solution if we understand the "real world" problem that you are trying to solve.

    If you insist on "doing it my way" after getting multiple suggestions to redesign to avoid the problem in the first place, then the best solution that I see based on what little you've given so far for dealing with a dynamic/ad-hoc generated query that produces ill-formed results is to resolve the problem in the code that generates your ad-hoc query.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Sep 2013
    Posts
    14
    The DOR view is dynamic and from the main query I want to select object_id and DOR.* (because of the dynamic columns)
    The problem is that DOR has the attribute object_id!

    I found something that would solve the problem but I can't get it to work:

    Code:
     
    SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_delete>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');
    
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so are your users gettign dirext native access SQL and effectively able to write their own SQL and view the results of that SQL or are those results being presented through some other medium say a web page, a .NET program or whatever.


    not knowing the design of the table(s), the business requirement its hard to comment on your SQL
    have you worked out why what you are proposing will not work
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •