kjelavankal

Getting Value using descriptors and Transposing results

Discussion created by kjelavankal on Oct 27, 2014
Latest reply on Oct 28, 2014 by kjelavankal

Hi,

 

I need to get values from PI database using descriptors for various time intervals in the last 24 hours  I am using PI JDBC driver and my application is built in java

 

The following query gets the results i am looking for

 

SELECT b.tag ,b.time, b.value,a.descriptor, DIGSTRING(b.status) status
  FROM pipoint .. classic a
  join piarchive .. picomp b
    on a.tag = b.tag
   and b.TAG LIKE '%XXXX%'
 WHERE b.time >= '*-24h'
   AND a.TAG LIKE '%XXXXX%'
   AND (DESCRIPTOR LIKE '%Current - Phase A%' OR
       DESCRIPTOR LIKE '%Current - Phase B%' OR
       DESCRIPTOR LIKE '%Current - Phase C%' OR
       DESCRIPTOR LIKE '%Current - Ground%' OR
       DESCRIPTOR LIKE '%Phase A Voltage%' OR
       DESCRIPTOR LIKE '%Phase B Voltage%' OR
       DESCRIPTOR LIKE '%Phase C Voltage%' ) 

 

The Results are displayed like

 

Tag               time                                               Value         descriptor                           status

 

XXXX           10/27/2014 3:26 PM                      201          Current - Phase A             Good

 

XXXX           10/27/2014 3:26 PM                      180          Current - Phase B             Good

 

XXXX           10/27/2014 3:23 PM                     226          Current - Phase A             Good

 

XXXX           10/27/2014 3:23 PM                    198          Current - Phase A             Good

 

However I need the results transposed  like shown below

 

time                                          Current - Phase A Value        Current - Phase B  Value

 

10/27/2014 3:26 PM                     201                                                180

 

10/27/2014 3:23 PM                     226                                               198

 

 I get the following results using the query mentioned below but that is time consuming and inefficient .

 

    SELECT distinct c.time,
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
     where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Current - Phase A%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag  and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Current - Phase B%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Current - Phase C%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%'and DESCRIPTOR LIKE '%Current - Ground%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Phase A Voltage%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Phase B Voltage%'),
    (Select value from pipoint .. classic a Join piarchive .. picomp b on a.tag = b.tag and b.TAG LIKE '%71385479%'
    where b.time = c.time AND a.TAG LIKE '%71385479%' and DESCRIPTOR LIKE '%Phase C Voltage%')
    FROM piarchive .. picomp c
    WHERE c.TAG LIKE '%71385479%'
    AND c.time >= '*-1h'
    order by c.time desc

 

1)  Is there a better way to do it ?

 

2) Are there alternate tables to pipoint .. classic and   piarchive .. picomp  where i can get both descriptors and values without joining ?

 

 Thanks

 

Kiran

Outcomes