Subject | CrossTab Select |
---|---|
Author | |
Post date | 2014-06-25T22:29:24Z |
Partners'm racking my brain on a SQL code, does anyone could give a force
I made the following SP
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 HORA, (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END) from TABELAB
WHERE TABELAB.DATABBETWEEN '23.06.2014' AND '28.06.2014'
GROUP BY HORA, PRODUTO, DATAB
INTO HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND;
END
Correct Return
#Código
Incorrect Return
#Código
I made the following SP
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 HORA, (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END), (CASE EXTRACT(WEEKDAY FROM TABELAB.DATAB) when 0 then PRODUTO else 0 END) from TABELAB
WHERE TABELAB.DATABBETWEEN '23.06.2014' AND '28.06.2014'
GROUP BY HORA, PRODUTO, DATAB
INTO HOR, :DOM, :SEG, :TER, :QUA, :QUI, :SEX, :SAB DO
SUSPEND;
END
Correct Return
#Código
1 2 3 | HORA
SEG TER QUA
QUI SEX SAB DOM 09:00 CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA CEBOLA 0 09:10 CEBOLA 0 0 0 0 0 |
Incorrect Return
#Código
1 2 3 4 5 6 7 | HORA SEG TER QUA QUI SEX SAB DOM 09:00 0 0 0 0 0 CEBOLA 09:00
0 0 0
0 CEBOLA 0 0 09:00
0 0 0
CEBOLA 0 0 0 09:00 0 0 CEBOLA 0 0 0 0 09:00 0 CEBOLA 0 0 0 0 0 09:00 CEBOLA 0 0 0 0 0 0 |