473,836 Members | 2,358 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I SELECT multiple columns as one column

In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:

select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don't see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,

Nov 12 '05
10 22939
Serge Rielau wrote:
Well, it is a very basic tool for SQL.
Every query you ever do must somehow involve a table.
To produce tables I can think only of:
table functions, tables and various forms of "external tables" such as
Nicknames in DB2 and values.
From there it's transitive closure.
I use VALUES whenever I need to pass a "table" from outside SQL into
a query.
Let's presume you read a cursor into a form for editing.
As common in these cases (.Net, J2EE) you don't want to keep the cursor
open. Instead you build an in memory result set set teh GUI loose on it.
Now hwo to you get the changed data back in?
You can take one row at a time running update statements for each.
Or you cross the boundary to SQL in one shot:

MERGE INTO T USING (VALUES (?, ?), (?, ?), (?, ?)) AS S(pk, val)
ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET T.val = S.val;

Now, what do you do when you have a lot of rows? You don't want to spell
them all out. The trend is going to this (search for "multi-row-insert"):
http://publib.boulder.ibm.com/cgi-bi...20040216135741
Cheers
Serge


Thanks for the further education, it's really appreciated.

BTW The manuals at the first URL below are easier for browser users to
read, since they are PDFs. The manuals you referenced are apparently
formatted for a program other than Netscape 7.1 to render, especially
the ASCII art RNF syntax diagrams. For the subjects at hand there should
be little or no difference between the zOS and Linux versions. The
latest information, for 8.1.x may be found at the second URL by drilling
down under Reference / SQL. For stuff added after 8.1 it is the best
resource I have found.

http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main

http://publib.boulder.ibm.com/infoce...help/index.jsp

Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5021
by: Taper Litwater | last post by:
after selecting from two table like so- select table1.column, table2.column from table1, table2 ... What is the correct syntax to access the individual table data? With one table I would normally just use- rs("column") TIA
6
5638
by: Dennis | last post by:
In CSS3 it looks like we'll have multiple column flowing of text (newspaper style) in which the number of columns can be determined automatically given the available horizontal space. http://www.w3.org/TR/css3-multicol/ Until then, what's the current best practice for achieving the same effect? Or is it just not possible yet? Thanks ... Dennis
17
5035
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no)
8
2184
by: Arpan | last post by:
A Form has a select list which lists all the column names of a SQL Server database table. Users will select one or more than one column from this select list & after submitting the Form, the records of only those columns that he had selected in the previous page will be displayed to him. This is the Form code: ---------------------------------------- strSQL="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tblSheet'...
3
2893
by: syounger | last post by:
Hi. I have a report in Access 2000 that is based on selection made from a series of interdependent list boxes. The boxes I have right now are Source, Table, Column, Date. The user chooses Source first, then the Table list box populates only tables from that source. Once a table is chosen, only the columns for that table appear in the Column list box. In the date box, the only dates that appear are those that are stored against the...
19
2947
by: Shwetabh | last post by:
Hi, I have two tables: Code and Color. The create command for them is : create table Color( Partnum varchar(10), Eng_Color char(10), Span_Color char(20), Frch_Color char(20), CONSTRAINT pkPartnum PRIMARY KEY(Partnum)
5
3410
by: rAinDeEr | last post by:
Hi, I have a web application with a table to store terms and conditions of a Company. This may some times run into many pages and some times it may be just a few sentences. It is a character text field. I want to know which Data type I need to use so that it doesnt waste memory. thanks in advance, rAinDeEr
7
15667
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is there an equivalent property for the DataGridView? I have searched, but have not found one. I would like the user to be able to see all the columns of the table on one screen - thus eliminating the need to use the horizontal scroll bar to view...
2
11439
omerbutt
by: omerbutt | last post by:
hi there i have to select 7 columns from table1, 1 column from table2, and 1 column from tables3 and show the result but i am not getting it right the main thing that i am trying to achieve is that i am making a notice board in a community and i have to show the notices that are posted for the specific user by comparing the fields like GENDER AGE AND CITY that are given in the notice i have done it quiet right to some extent means if i have to...
1
4892
by: KrazyKasper | last post by:
Access 2003 – Multi-Column List Box – Select Multiple Items I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended). I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10573
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7773
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4443
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.