Skip to Content
0
Oct 28, 2013 at 12:12 PM

PIVOT query defining parameters as P.STATE

341 Views

Hello experts

I am trying to build a PIVOT query to show SUM(JDT1.Debit - JDT1.Credit), taking JDT1.Account as State (rows) and distributing the result in JDT1.ProfitCode (columns). But I am also trying to set the ceco's as selection parameters, but perhaps I am overrating PIVOT query capabilities and that is not even possible.

That is the query that I have been able to make so far:

SELECT P.STATE,

[0%] as 'Ceco1',

[1%] as 'Ceco2',

[2%] as 'Ceco3',

FROM

(SELECT (T0.Account) as State,

(T0.Credit - T0.Debit) as importe, (T0.ProfitCode) as ceco

FROM JDT1 T0

WHERE T0.RefDate >= (CONVERT(DATETIME, '20120701', 112)) AND T0.RefDate <= (CONVERT(DATETIME, '20130630', 112))) S

PIVOT (SUM(importe) FOR ceco IN

([0%], [%1], [%2])) P

By executing it in Query generator, system displays syntax errors near 'FROM' and near 'S'.

I have also tried modifying the query without trying to set the Ceco as selection parameters, so this query should at least display SUM(JDT1.Debit - JDT1.Credit) for the 3 concrete cost centers indicated in following query:

SELECT P.STATE,

[FUT0101] as 'Ceco1',

[FUT0102] as 'Ceco2',

[FUT0201] as 'Ceco3',

FROM

(SELECT (T0.Account) as State,

(T0.Credit - T0.Debit) as importe, (T0.ProfitCode) as ceco

FROM JDT1 T0

WHERE T0.RefDate >= (CONVERT(DATETIME, '20120701', 112)) AND T0.RefDate <= (CONVERT(DATETIME, '20130630', 112))) S

PIVOT (SUM(importe) FOR ceco IN

([FUT0101], [FUT0102] , [FUT0201] )) P

But when executing it in Query generator system displays same syntax errors near 'FROM' and near 'S'.

Is it possible to meet some of my requirements with this PIVOT query?

Thanks in advance and regards