3 Replies Latest reply on Dec 16, 2010 3:20 PM by spilon

    PIPB and ODBC dataset

    Børre Heggernes

      ....arrrgh. Have to write it all over again. My post disappeard  when I posted it (IE 9 beta....)

       

      Writing this from Chrome:

       

       

       

      My issue is I'm trying to create an ODBC DataSet within Processbook. I'm connecting to an Oracle 11g database. The table has a column that holds time as a whole number. Guess it's some EPOCH format (number secs since some date)

       

      How can I convert this timestamp coumn into something PIPB will understand? And also, I want to use placeholders ('?') to get the start/endtime from my trend.

       

       

        • Re: PIPB and ODBC dataset

          Haven't tried this myself with an Oracle database, but wouldn't be able to do the calculation and cast the result into a standard timestamp column that you label as desired?

           
          SELECT CAST(To_Date('19700101','YYYYMMDD')+ NumberOfSeconds/86400 AS TIMESTAMP [WITH LOCAL TIME ZONE]) AS RegularTimestamp, MyValue 
          FROM MyTable; 
          

           

           

           Then you use RegularTimestamp as your placeholder column in ProcessBook.

            • Re: PIPB and ODBC dataset
              Børre Heggernes

              Hi Steve,

               

              testing this in PI Data Services for the moment. Using your hint helped. I get this to work:

              SELECT CAST(To_Date('19700101','YYYYMMDD')+ NumberOfSeconds/86400 
                  AS TIMESTAMP [WITH LOCAL TIME ZONE])
                  AS RegularTimestamp, MyValue
                  FROM MyTable AND (MyTable.TIME>1281814201)

              However, when trying to use a placeholder for the comparison I get ORA-01841 when I clik validate SQL

              .....WHERE (CAST(To_Date('19700101','YYYYMMDD')+DETDATA.TIME/86400 AS TIMESTAMP)> '?') 

              Do I need to convert back to EPOC to do this comparison? Any ideas?