Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    11

    Unanswered: Selecting multiple columns

    Say I have 20 columns in my table. I want to select all of them.
    That would be SELECT * FROM

    However out of these I want to rename say 3
    SELECT a AS b etc.

    Is there anyway to do this without having to name all 20 columns in the select statement?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, there is not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    NO, It's all or nothing.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just an observation, but using the SELECT * syntax is usually considered "bad form" anyway. If something causes a change in the table schema, any code that depends on specific columns in a specific order will break. If you explicitly name the columns that you expect and want, you can be much more certain of getting what you expected.

    If you follow the "best practice" of explicitly stating what columns you want, then aliasing one or all of them is trivial.

    -PatP

  5. #5
    Join Date
    Jul 2006
    Posts
    11
    Darn! Well anyhoo!

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Some RDMBS's support
    Code:
    SELECT a AS b, c AS d, *
    FROM mytable
    but as Pat mentioned: it's not a good idea to use "*" in "production" code.
    With some cut-and-paste work (from a query on the catalog) you don't need to type in all 20 column names...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Isn't it a lot to ask to individually code 20 column names into an SQL statement?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by urquel
    Isn't it a lot to ask to individually code 20 column names into an SQL statement?
    no, it isn't

    it isn't a lot to ask to code all desired columns into an SQL statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by urquel
    Isn't it a lot to ask to individually code 20 column names into an SQL statement?
    I have several SQL queries (embedded in applications) on joins of (sometimes) more than 5 tables, which return more than 20 columns.
    I never use "SELECT *" in those cases but mention the columns explicitly (typically one per line, for readability).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jul 2006
    Posts
    11
    Thanks for the tips guys.

    Patience is the key to good SQL statements then.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When you write the code, spending a minute or two to put all columns into the SELECT column list might look too long, but you'll be grateful after a few months when you (or, even worse, someone else) will have to debug this code.

    Although you *know* which column belongs to which table at that moment, believe me, you'll forget that and - without using table aliases along with column names - you'll be in a deep trouble. How will you know which tables you joined in every WHERE clause?

    Spend some extra time now to save much more time and effort in the future.

  12. #12
    Join Date
    Mar 2008
    Posts
    2

    Smile Selecting columns but modifying data

    I am developing an application that will select a table with approximately 50 columns, but if a column contains NULLs, I want high-values returned from the selection. Currently, i'm doing this process manually. For example:

    SELECT COL1, COL1, COL3, COL4
    INTO WORK1:IND-WORK1,
    WORK2:IND-WORK2,
    WORK3:IND-WORK3,
    WORK4:IND-WORK4

    If IND-WORK1 = 0
    MOVE WORK1 TO VALUE1
    END-IF
    IF IND-WORK2 = 0
    MOVE WORK2 TO VALUE2
    END-IF

    Is there a way of having DB2 internally change nulls to high-values before returning the value? When i'm updating the table, I also have to do the reverse, where I check for high-values, if present, I move -1 to the IND-WORK1 to indicate NULL values should be loaded into the table.

    Is there any easier way of doing this, has anybody been challenged like this?

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by quikenheimer
    Is there a way of having DB2 internally change nulls to high-values before returning the value?
    Sure:
    Code:
    SELECT COALESCE(col1, x'FF'), COALESCE(col2, x'FF'), ...
    INTO :work1, :work2, ...
    FROM   mytable ...
    Quote Originally Posted by quikenheimer
    I also have to do the reverse
    Also this is easily possible:
    Code:
    INSERT INTO mytable(col1, col2, ...)
    VALUES (NULLIF(:work1, x'FF'), NULLIF(:work2, x'FF'), ...)
    (Or likewise for UPDATE, for that matter.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Mar 2008
    Posts
    2
    Thanks. I'll give this a try!

Posting Permissions

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