topic-32614

Board index FiveWin for Harbour/xHarbour OT: SQL PIVOT question

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

OT: SQL PIVOT question
Posted: Wed Jun 22, 2016 10:37 AM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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

Posts: 1088

Joined: Fri Oct 07, 2005 03:33 PM

Re: OT: SQL PIVOT question
Posted: Wed Jun 22, 2016 01:16 PM

Hola,

try adding HAVING

Code (fw): Select all Collapse

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: OT: SQL PIVOT question
Posted: Wed Jun 22, 2016 02:05 PM

Thank you Marcelo,

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

Code (fw): Select all Collapse

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'

Marcelo Via Giglio wrote:Hola,

try adding HAVING

Code (fw): Select all Collapse

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

FiveTech Support Forums

Continue the discussion