Dusan Kolesar wrote:
>
> Hello Elke,
> Thank you very much. Your statement works excelent.
> One more question:
> Is it possible to make VIEW using RECURSIVE CURSOR ?
Unfortunately: NO
Elke
SAP Labs Berlin
>
> Dusan
>
>
> On Wed, 02 Nov 2005 10:01:03 +0100, Zabach, Elke <[EMAIL PROTECTED]>
> wrote:
>
> > Dusan Kolesar wrote:
> >>
> >> On Mon, 31 Oct 2005 15:14:15 +0100, Zabach, Elke
<[EMAIL PROTECTED]>
> >> wrote:
> >>
> >> > Dusan Kolesar wrote:
> >> >
> >> >>
> >> >> Hello list,
> >> >> Is it posible to do in SAP DB something like:
> >> >> SELECT CONCAT (column1 || ', ') FROM t1 GROUP BY column2
> >> >>
> >> >> Manual says:
> >> >> The GROUP clause allows the functions SUM, AVG, MAX/MIN, COUNT,
> > STDDEV,
> >> >> and VARIANCE
> >> >> to be applied not only to entire result tables but also to
groups
> > of
> >> >> rows
> >> >> within a result table.
> >> >>
> >> >> Or is there any other way how to obtain such a result?
> >> >> Thank you for your support.
> >> >>
> >> >
> >> > If a GROUP BY - clause is given, every column has to be part of
this
> >> > clause OR part of a aggregate function like SUM/MIN/MAX/COUNT/...
> >> >
> >> > --> your select is not possible. And, by the way, what should the
> >> > semantic be if all rows with the same column2-value are put
together
> > and
> >> > one of the column1-values is used to produce the output for the
> >> > corresponding resultrow? Which column1-value ????
> >> >
> >> > Which problem do you want to solve?
> >> >
> >> > Elke
> >> > SAP Labs Berlin
> >> >
> >> >
> >> >> Dusan
> >> >>
> >>
> >> Hello, Elke
> >> let me describe you my situation.
> >> I have tables:
> >> //
> >> CREATE TABLE "STATION"
> >> (
> >> "ID" Integer NOT NULL DEFAULT SERIAL,
> >> "NAME" Varchar(30) ASCII NOT NULL DEFAULT '',
> >> PRIMARY KEY ("ID")
> >> )
> >> //
> >> CREATE TABLE LINE
> >> (
> >> "ID" Integer NOT NULL DEFAULT SERIAL,
> >> "NAME" Varchar(10) ASCII NOT NULL DEFAULT '',
> >> PRIMARY KEY ("ID")
> >> )
> >> //
> >> CREATE TABLE STATION_LINE_REL
> >> (
> >> "LINE_ID" Integer NOT NULL,
> >> "STATION_ID" Integer NOT NULL,
> >> "STATIONORDER" Integer NOT NULL DEFAULT 1
> >> CONSTRAINT "STATIONORDER" > 0,
> >> FOREIGN KEY "LINE_ID" ("LINE_ID")
> >> REFERENCES "LINE" ("ID") ON DELETE CASCADE,
> >> FOREIGN KEY "STATION_ID" ("STATION_ID")
> >> REFERENCES "STATION" ("ID") ON DELETE CASCADE
> >> )
> >> //
> >> INSERT STATION (ID, NAME) VALUES (1, 'ST1')
> >> //
> >> INSERT STATION (ID, NAME) VALUES (2, 'ST2')
> >> //
> >> INSERT STATION (ID, NAME) VALUES (3, 'ST3')
> >> //
> >> INSERT STATION (ID, NAME) VALUES (4, 'ST4')
> >> //
> >> INSERT LINE (ID, NAME) VALUES (1, 'L1')
> >> //
> >> INSERT LINE (ID, NAME) VALUES (2, 'L2')
> >> //
> >> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES
(1,
> > 1,
> >> 1)
> >> //
> >> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES
(1,
> > 2,
> >> 2)
> >> //
> >> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES
(1,
> > 3,
> >> 3)
> >> //
> >> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES
(2,
> > 2,
> >> 1)
> >> //
> >> INSERT STATION_LINE_REL (LINE_ID, STATION_ID, STATIONORDER) VALUES
(2,
> > 4,
> >> 2)
> >> //
> >>
> >> So between station and line is N:N relation.
> >> Line L1 : ST1, ST2, ST3
> >> Line L2 : ST2, ST4
> >>
> >> I need make a list of station for 1 station only 1 row
> >> with lines description column (LINES) = all lines on which station
> > lies.
> >> ID NAME NUMLINES LINES
> >> 1 ST1 1 L1
> >> 2 ST2 2 L1, L2
> >> 3 ST3 1 L1
> >> 4 ST4 1 L2
> >>
> >
> >
> > If you always can assure that not more than 2 lines will stop at the
> > same station, some kind of (outer) join may help.
> > I did not think of that case, but wanted to assure a correct result
for
> > as many lines per station as are in reality.
> > The resulting query looks a 'little bit' different:
> >
> > DECLARE C CURSOR FOR
> > WITH RECURSIVE PX (ID, NUMLINES, LINES, LAST_LINE) AS
> > (SELECT STATION_ID, 1, NAME, LINE.ID
> > FROM STATION_LINE_REL, LINE
> > WHERE STATION_LINE_REL.LINE_ID = LINE.ID
> > AND (STATION_ID,LINE_ID) = ANY
> > (SELECT STATION_ID, MIN(LINE_ID) FROM STATION_LINE_REL
> > GROUP BY STATION_ID)
> > UNION ALL
> > SELECT PX.ID, NUMLINES+1, LINES||', '|| NAME,
> > STATION_LINE_REL.LINE_ID
> > FROM STATION_LINE_REL, LINE, PX
> > WHERE STATION_LINE_REL.LINE_ID = LINE.ID
> > AND PX.ID = STATION_LINE_REL.STATION_ID
> > AND PX.LAST_LINE < STATION_LINE_REL.LINE_ID
> > )
> > SELECT PX.ID, STATION.NAME, FIXED(NUMLINES, 2), LINES
> > FROM PX, STATION
> > WHERE (PX.ID, NUMLINES) = ANY
> > (SELECT STATION_ID, COUNT(*)
> > FROM STATION_LINE_REL
> > GROUP BY STATION_ID)
> > AND PX.ID = STATION.ID
> > ORDER BY PX.ID
> >
> > But it works (tested with an additional line 3 stopping at station
2)
> >
> > Good luck
> >
> > Elke
> > SAP Labs Berlin
> >
> >
> >
> >
> >> SELECT STATION.ID, MIN (STATION.NAME)AS NAME,
> >> COUNT (STATION_LINE_REL.LINE_ID) AS NUMLINES
> >> /* CONCAT (LINE.NAME || ', ') AS LINES */
> >> FROM STATION
> >> LEFT JOIN STATION_LINE_REL ON STATION_LINE_REL.STATION_ID =
> >> STATION.ID
> >> GROUP BY STATION.ID
> >>
> >> And from other side lines list with station description colunm.
> >> Is it posible to find some elegant solution for this?
> >>
> >> Thank you, Dusan
> >>
>
> --
> Dusan Kolesar
> Helsinska 19
> 040 13 Kosice
> Slovakia
> e-mail : [EMAIL PROTECTED]
> ICQ# : 160507424
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
