OT: SQL PIVOT question

[Marc Vanzegbroeck]

Hi,

This is not a FWH-question, but I use this query in my FWH-program

I have a PIVOT query on a table with fields:
NAME,PARAMETER,INHOUD,POINT_TYPE

I use this query to show the result:

Code: Select all  Expand view

SELECT
    name,
    ( max( if( PARAMETER = 'NTWKNUM' , inhoud , NULL ) ) ) AS 'NTWKNUM',
    ( max( if( PARAMETER = 'NODENUM' , inhoud , NULL ) ) ) AS 'NODENUM',
    ( max( if( PARAMETER = 'MODNUM'  , inhoud , NULL ) ) ) AS 'MODNUM' ,
    ( max( if( PARAMETER = 'SLOTNUM' , inhoud , NULL ) ) ) AS 'SLOTNUM',
     TEMPLATE AS POINT_TYPE
FROM  
   mytable
 WHERE
    template = 'ANINNIM'   
GROUP BY name

This is working fine.
Now I want to show only the rows with NTWKNUM = '01'
The problem is that I can't put

Code: Select all  Expand view

WHERE template = 'ANINNIM'  AND NTWKNUM = '01'

because the field NTWKNUM does not exist in the table, but is a field from the result of the query.

How can I do this?
Thanks

[Marcelo Via Giglio]

Hola,

try adding HAVING

Code: Select all  Expand view

SELECT
    name,
    ( max( if( PARAMETER = 'NTWKNUM' , inhoud , NULL ) ) ) AS 'NTWKNUM',
    ( max( if( PARAMETER = 'NODENUM' , inhoud , NULL ) ) ) AS 'NODENUM',
    ( max( if( PARAMETER = 'MODNUM'  , inhoud , NULL ) ) ) AS 'MODNUM' ,
    ( max( if( PARAMETER = 'SLOTNUM' , inhoud , NULL ) ) ) AS 'SLOTNUM',
     TEMPLATE AS POINT_TYPE
FROM  
   mytable
 WHERE
    template = 'ANINNIM'
HAVING
    NTWKNUM = '01'  
GROUP BY name

 

[Marc Vanzegbroeck]

Thank you Marcelo,

Now it's working!
I have to move HAVING to the end, like this.

Code: Select all  Expand view

SELECT
    name,
    ( max( if( PARAMETER = 'NTWKNUM' , inhoud , NULL ) ) ) AS 'NTWKNUM',
    ( max( if( PARAMETER = 'NODENUM' , inhoud , NULL ) ) ) AS 'NODENUM',
    ( max( if( PARAMETER = 'MODNUM'  , inhoud , NULL ) ) ) AS 'MODNUM' ,
    ( max( if( PARAMETER = 'SLOTNUM' , inhoud , NULL ) ) ) AS 'SLOTNUM',
     TEMPLATE AS POINT_TYPE
FROM  
   mytable
WHERE
    template = 'ANINNIM'
GROUP BY name
HAVING
    NTWKNUM = '01'  

Hola,

try adding HAVING

Code: Select all  Expand view

SELECT
    name,
    ( max( if( PARAMETER = 'NTWKNUM' , inhoud , NULL ) ) ) AS 'NTWKNUM',
    ( max( if( PARAMETER = 'NODENUM' , inhoud , NULL ) ) ) AS 'NODENUM',
    ( max( if( PARAMETER = 'MODNUM'  , inhoud , NULL ) ) ) AS 'MODNUM' ,
    ( max( if( PARAMETER = 'SLOTNUM' , inhoud , NULL ) ) ) AS 'SLOTNUM',
     TEMPLATE AS POINT_TYPE
FROM  
   mytable
 WHERE
    template = 'ANINNIM'
HAVING
    NTWKNUM = '01'  
GROUP BY name