Hi,
My question is: How to concat values from several lines (the same
column) to only one value. For example:
We have table SGMENT with one column: NAME (varchar2(50)). We would
like to have as a query result the concatenation of names. I mean, for
example:
SGMENT
------
VS000_001_t
VS001_002_t
VS002_003_r
And with a query we would like to obtein:
"VS000_001_tVS0 01_002_tVS002_0 03_r"
Is this possible with only SQL Sentence as SELECT ....???
We are using IBM DB2 V7 UDB for OS390 AND ZOS
Thank you in advance
Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG 8 17708
Hi,
following works fine on DB2 8.2 for LUW, try this on your platform
WITH tmp (name, rn) AS
(SELECT root.name,1 FROM Segment as root WHERE root.name =
'VS000_001_t'
UNION ALL
SELECT parent.name||ch ild.name, child.rn
FROM tmp parent, (SELECT name, rownumber() over(order by name) AS rn
FROM Segment) child
WHERE parent.rn+1 = child.rn
) select * from tmp order by 2 desc fetch first 1 row only
Cheers
Adrian
IBM DB2 UDB for OS/390 AND z/OS V7 doesn't support "WITH
common-table-expression" and "OLAP functions".
If the maximum number of names to be concatenated is limited.
Following query will work on DB2 UDB for OS/390 AND z/OS V7.
SELECT name1 || name2 || name3 || name4 || name5
|| name6 || name7 || name8 || name9 || name10 AS names
FROM (SELECT MAX(CASE WHEN n = 1 THEN RTRIM(name) ELSE '' END) name1
, MAX(CASE WHEN n = 2 THEN RTRIM(name) ELSE '' END) name2
, MAX(CASE WHEN n = 3 THEN RTRIM(name) ELSE '' END) name3
, MAX(CASE WHEN n = 4 THEN RTRIM(name) ELSE '' END) name4
, MAX(CASE WHEN n = 5 THEN RTRIM(name) ELSE '' END) name5
, MAX(CASE WHEN n = 6 THEN RTRIM(name) ELSE '' END) name6
, MAX(CASE WHEN n = 7 THEN RTRIM(name) ELSE '' END) name7
, MAX(CASE WHEN n = 8 THEN RTRIM(name) ELSE '' END) name8
, MAX(CASE WHEN n = 9 THEN RTRIM(name) ELSE '' END) name9
, MAX(CASE WHEN n =10 THEN RTRIM(name) ELSE '' END) name10
FROM (SELECT a.name
, COUNT(b.name)
FROM SGMENT a
, SGMENT b
WHERE a.name >= b.name
GROUP BY
a.name
) q (name, n)
) r
WITH is not available in V7 zOS, only in V8.
Row_number() fuction as well is not there on zOS.
Adrian, I am not sure about this kind of recursive SQL doing what you
like.
I tried because I thought its interesting, but no way.
Did you try it on LUW ?
May, what you are asking is about how to present your resultset,
especially when it is only one column.
This usually is done outside SQL. Are u using a tool ? I not, then a
simple rexx script will do the job of changing it to one line.
Juliane
Hi, WITH is not available in V7 zOS, only in V8. Row_number() fuction as well is not there on zOS.
It's a pitty. This really nice features.
Did you try it on LUW ?
Yes. I tried it on LUW.
CREATE TABLE Segment ( name varchar(50) )
DB20000I The SQL command completed successfully.
insert into Segment
values('VS000_0 01_t'),('VS001_ 002_t'),('VS002 _003_r')
DB20000I The SQL command completed successfully.
WITH tmp (name, rn) AS (SELECT root.name,1 FROM Segment as root WHERE
root.name = 'VS000_001_t' UNION ALL SELECT parent.name||ch ild.name,
child.rn FROM tmp parent, (SELECT name, rownumber() over(order by name)
AS rn FROM Segment) child WHERE parent.rn+1 = child.rn ) select * from
tmp order by 2 desc fetch first 1 row only
NAME RN
-------------------------------------------------- -----------
SQL0347W The recursive common table expression "AKALICKI.T MP" may
contain an
infinite loop. SQLSTATE=01605
VS000_001_tVS00 1_002_tVS002_00 3_r 3
1 record(s) selected with 1 warning messages printed.
Cheers
Adrian
"adik_q" <ad****@wp.pl > wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com Hi,WITH is not available in V7 zOS, only in V8. Row_number() fuction as well is not there on zOS. It's a pitty. This really nice features.
Yeah !!! ..it's a pitty :( :(
Thanks for the answer
Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Thanks you very much.
We will try it as soon as possible. As I explain in other post to
Juliane, I think it's going to work, but now I'm not sure if our dev.
enviroment will support this kind of SQL. I think yes, but dev. team
it's not sure
Best Regards
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
First of all, thank you for your interest ;) May, what you are asking is about how to present your resultset, especially when it is only one column.
Well, the facts are:
- We’re developing an integration process from flat text file into DB2
database.
- We’re using the DataStage tool (ETL) one.
- At some stage, we need to create from several rows in that table
(well a complex one, but similar to the example ;) ) a unique name as
explained before.
This usually is done outside SQL. Are u using a tool ? I not, then a simple rexx script will do the job of changing it to one line.
We think we can query the table as Tonkuma has explained in a ODBC
connector and then get the result set (one row with only one column) to
use it later.
Well, our team thinks that with only one query we can solve our
problems. Be sure I will keep this thread alive and updated.
Thanks a lot everybody
Best regards
May
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Why do you want to violate First Normal Form? Why are smarter than Dr.
Codd and 30+ years of RDBMS theory?
In a tiered architecture, display and fomatting is done in the front
end and NEVER in the database. This is far more fundamental than just
SQL.
There are highly proprietary ways to do this (aka KLUDGES) using
cursors and other tricks. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dominik Kaspar |
last post by:
i want to initialize a constant list at the beginning of a python
program. but the list is too big for one line. is there any
possibility to spread the list over several lines, so that the code
looks neat again?
something like:
LIST = , , ,
, , ,
, , ]
|
by: Frans Englich |
last post by:
Hello,
I take PyChecker partly as an recommender of good coding practice, but I
cannot make sense of some of the messages. For example:
runner.py:878: Function (main) has too many lines (201)
What does this mean? Cannot functions be large? Or is it simply an advice that
functions should be small and simple?
|
by: William Gill |
last post by:
I need to display a couple of labels and a checkbox from each entry in
my database. Simple enough, but there are several hundred records, and
I only want to display 5 or 10 at a time. Can this be accomplished by
putting everything in a Frame(), using width, height, grid_propagate(0)
, and a scrollbar? or do I have to grid 5 rows at a time? If the
latter, can I just grid over the previous 5 or do they have to be
explicitly removed...
|
by: KitKat |
last post by:
Right now at work, I get forms e-mailed to me in an Excel chart with a
serial number in one column and a new expiration date for a device's
trial period in another column.
For a while, when people only sent forms with one or two lines, I
didn't mind making a simple parameter query that searched for a serial
number (ESN) and showed me the relevant line of my office's tracking
database so I could change the date, etc.
Here's how it...
|
by: casul |
last post by:
Hi All,
I was told there were a few macro gurus on this group :)
I'm trying to define a macro that will allow me to write the following
code :
#include MY_MACRO( NAME, SPACE )
| |
by: Shokoth |
last post by:
I also wanted to know how to spread out sql statement over 3 lines.
I am writing an sql update statment in VB.
I have about 12 fields to update.
strSQL = "UPDATE " & _
"Set . = DateSerial(Year(Date()),1,30), . = False, . = DateSerial(Year(Date()),2,28), . = False " & _
"WHERE (((.JanPaymentRec)= True) " & _
|
by: Pirmin |
last post by:
How can I find the matches for bookings that meet the following conditions.
A booking always starts with a date.
A booking can consist of several lines.
Dates can be part of the booking text.
Here is an example:
2007-01-17;Gutschrift VESR;510;69370.00;2007-01-17
|
by: TP |
last post by:
Hi everybody,
When using raw_input(), the input of the user ends when he types Return on
his keyboard.
How can I change this behavior, so that another action is needed to stop the
input? For example, CTRL-G. It would allow the user to input several lines.
Thanks
Julien
|
by: arungkumar |
last post by:
Can a string literal extend over several lines? why or why not?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
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...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |