| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-07-06, 06:20
|
|
Registered User
|
|
Join Date: Jul 2006
Posts: 11
|
|
|
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?
|
|
08-07-06, 06:32
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
|
|
08-07-06, 11:04
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,455
|
|
|
|
NO, It's all or nothing. 
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
|
|
08-07-06, 13:41
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
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
|
|
08-08-06, 00:35
|
|
Registered User
|
|
Join Date: Jul 2006
Posts: 11
|
|
|
|
08-08-06, 02:43
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
08-08-06, 09:58
|
|
Registered User
|
|
Join Date: Aug 2004
Posts: 330
|
|
Isn't it a lot to ask to individually code 20 column names into an SQL statement?
|
|
08-08-06, 10:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
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
|
|
08-08-06, 10:50
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
08-09-06, 00:04
|
|
Registered User
|
|
Join Date: Jul 2006
Posts: 11
|
|
Thanks for the tips guys.
Patience is the key to good SQL statements then.
|
|
08-09-06, 02:17
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
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.
|
|
03-18-08, 17:42
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 2
|
|
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?
|
|
03-18-08, 17:52
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
03-18-08, 17:54
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 2
|
|
Thanks. I'll give this a try!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Selecting multiple columns
