Adding a clause to CrossTab Query

[ashwinghanta]

Hello Everybody,

I have the following SQL code of Query

TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT
SELECT VWDRSSTA.ANTRAGSNUMMER,[B]Max(VWDRSSTA.DUNKEL) AS Dunkel[/B], Max(VWDRSSTA.VERS_NR_INT) AS Versicherungsnummer
FROM VWDRSSTA INNER JOIN V_NAMES ON (VWDRSSTA.SYSTEM = V_NAMES.SYSTEM_CODE) AND (VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS)
GROUP BY VWDRSSTA.ANTRAGSNUMMER
ORDER BY VWDRSSTA.ANTRAGSNUMMER
PIVOT V_NAMES.MAPPED_NAME;

I want to change the Max(VWDRSSTA.DUNKEL) AS Dunkel clause to Max(VWDRSSTA.DUNKEL) WHERE VWDRSSTA.SYSTEM = 'VS' AS Dunkel and I tried the following code for it

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]IIF[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]VWDRSSTA[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SYSTEM [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] [/COLOR][COLOR=#7D2727]'VS'[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] 
    [/COLOR][COLOR=#303336]([/COLOR][COLOR=#101094]SELECT[/COLOR][COLOR=#303336] [/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]Max[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]VWDRSSTA[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]DUNKEL[/COLOR][COLOR=#303336]))[/COLOR][COLOR=#303336] [/COLOR][COLOR=#101094]as[/COLOR][COLOR=#303336] d [/COLOR][COLOR=#101094]FROM[/COLOR][COLOR=#303336] VWDRSSTA [/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#303336],[/COLOR][COLOR=#101094]null[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]AS[/COLOR][COLOR=#303336] DUNKEL
[/COLOR][COLOR=#101094]FROM[/COLOR][COLOR=#303336] VWDRSSTA[/COLOR]</code>

but it gives me an error "Multi-level GROUP BY clause is not allowed in subquery". How can I achieve the above? Can someone guide me through this?

Thanks

 

[Fazza]

what about making the filtered dataset (WHERE VWDRSSAT.SYSTEM = 'VS') and then the TRANSFORM ?

so something like

TRANSFORM function
SELECT row fields
FROM (SELECT fields you need in a normal query
FROM source data
WHERE put your criteria here)
GROUP BY row fields
PIVOT field