Subject | RE: [firebird-support] CrossTab Select |
---|---|
Author | Svein Erling Tysvær |
Post date | 2014-06-26T06:37:52Z |
Your problem is the line where you explicitly says that you want to have one line returned for each combination of HORA, PRODUTO and DATAB in TABELAB:
GROUP BY HORA, PRODUTO, DATAB
The simplest solutions is probably either to change your group by to
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
or to use DISTINCT rather than GROUP BY:
CREATE PROCEDURE PRODS(PAC INTEGER)
RETURNS(
HOR TYPE OF HORA,
SEG TYPE OF DESCRICAO,
TER TYPE OF DESCRICAO,
QUA TYPE OF DESCRICAO,
QUI TYPE OF DESCRICAO,
SEX TYPE OF DESCRICAO,
SAB TYPE OF DESCRICAO,
DOM TYPE OF DESCRICAO)
AS
BEGIN
FOR SELECT DISTINCT HORA,
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0)
FROM TABELAB
WHERE DATABBETWEEN '23.06.2014' AND '28.06.2014'
INTO :HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND;
END
This could (depending on your data) give you:
HORA SEG TER QUA QUI SEX SAB DOM
09:00 CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA
09:10 0 0 0 0 0 0 0
HTH,
Set
GROUP BY HORA, PRODUTO, DATAB
The simplest solutions is probably either to change your group by to
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
or to use DISTINCT rather than GROUP BY:
CREATE PROCEDURE PRODS(PAC INTEGER)
RETURNS(
HOR TYPE OF HORA,
SEG TYPE OF DESCRICAO,
TER TYPE OF DESCRICAO,
QUA TYPE OF DESCRICAO,
QUI TYPE OF DESCRICAO,
SEX TYPE OF DESCRICAO,
SAB TYPE OF DESCRICAO,
DOM TYPE OF DESCRICAO)
AS
BEGIN
FOR SELECT DISTINCT HORA,
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0),
IIF(EXTRACT(WEEKDAY FROM DATAB) = 0, PRODUTO, 0)
FROM TABELAB
WHERE DATABBETWEEN '23.06.2014' AND '28.06.2014'
INTO :HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND;
END
This could (depending on your data) give you:
HORA SEG TER QUA QUI SEX SAB DOM
09:00 CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA
09:10 0 0 0 0 0 0 0
HTH,
Set