8 Replies Latest reply on Jan 8, 2018 5:53 AM by Srinivas

    Help with RDBMS SQL Statement

    MartyWerner

      I am trying to setup an RDBMS interface and pull some data into the PI Arc using a data distribution tag. I have created the SQL statement and it works in my test tool but when I try to execute the statement with the interface it errors.

       

      Any help would be greatly appreciated.

      SELECT CONCAT (Concat(S.SMPL_DT ,' '),s.smpl_TM) AS PI_Timestamp, CONCAT(CONCAT (S.SMPL_PNT_NAME,'.'),R.PRPRTY_NAME) As PI_TAGNAME,  R.RSLT_VALUE AS PI_Value FROM IP_SMPL S, IP_TST_RSLT R WHERE  S.SMPL_DT = TRUNC(SYSDATE) AND S.SMPL_NAME = R.SMPL_NAME AND R.RSLT_VALUE IS NOT NULL AND S.EQ_NAME = 'CRUDE ASSAY' AND S.SMPL_PNT_NAME = 'CRUDE CVILLE'  ORDER BY S.SMPL_PNT_NAME, R.PRPRTY_NAME,S.SMPL_DT, S.SMPL_TM;

       

      It appears it doesn't like the concat statements but they are required to get the correct data.

        • Re: Help with RDBMS SQL Statement
          John Messinger

          Hi Marty,

           

          Some questions for you to help diagnose this issue:

           

          1. What is the target RDBMS that you are executing this query against?

          2. Which specific ODBC driver have you configured the DSN with?

          3. Can you post a copy of the errors that you get from the interface when this query is executed? 

           

          From past experience, I suspect that the issue might be with the specific ODBC driver targeted in your DSN configuration.

           

          Regards,

          John

          1 of 1 people found this helpful
            • Re: Help with RDBMS SQL Statement
              MartyWerner

              John:

               

              Thanks for reaching out.

               

              1. What is the target RDBMS that you are executing this query against? - Not sure waht you are asking.

              2. Which specific ODBC driver have you configured the DSN with?

               

              3. Can you post a copy of the errors that you get from the interface when this query is executed?

               

              Unsupported RDB column PI_TIMESTAMP type -9.

                • Re: Help with RDBMS SQL Statement
                  John Messinger

                  Hi Marty,

                   

                  Thanks for providing this additional information. I see what the issue is now - your PI_Timestamp column is being treated as a string type (as you are performing a string concatenation of two columns to generate a timestamp). You will need to perform a cast of this column in your query into a supported datetime type. You can have a look at KB01326, as it may have some relevance, but I think the proper cast is to a datetime type. I don't know what the exact Oracle data type is for your query, so hopefully you can determine that part.

                   

                  Let us know how you go with that.

                   

                  Cheers,

                  John

                  1 of 1 people found this helpful
              • Re: Help with RDBMS SQL Statement
                Srinivas

                Hi Marty,

                Concat function not recognized in Oracle.  As pipe is symbol used in the oracle queries for Concatenation and use TO_DATE function to convert to timestamp.

                Please see below sample query:

                TRUNC(TO_DATE(LPAD('01', 2, '0') || '/' || LPAD(MONTH, 2, '0') || '/' || YEAR, 'DD/MM/YYYY')) AS PI_TIMESTAMP,

                 

                Regards

                Srinivas

                  • Re: Help with RDBMS SQL Statement
                    John Messinger

                    I don't think that is entirely correct, as the Oracle CONCAT() function is clearly documented here, and the OP states that the query works in the test tool against the source database. The issue with the query as it stands is that the resulting column value isn't recognised by the RDBMS interface as a valid timestamp value type.

                      • Re: Help with RDBMS SQL Statement
                        MartyWerner

                        John:

                        So I got past the time stamp issue but now I am having the same problem with the tagname. I tried to cast to to a varchar but that is not working. Any more ideas?

                         

                        SELECT CAST(CONCAT (Concat(S.SMPL_DT ,' '),s.smpl_TM) AS TIMESTAMP) AS PI_Timestamp, CAST (CONCAT(CONCAT (S.SMPL_PNT_NAME,'!'),R.PRPRTY_NAME) As varchar(255)) As PI_TAGNAME,  R.RSLT_VALUE AS PI_Value FROM IP_SMPL S, IP_TST_RSLT R WHERE  S.SMPL_DT = TRUNC(SYSDATE) - 1 AND S.SMPL_NAME = R.SMPL_NAME AND R.RSLT_VALUE IS NOT NULL AND S.EQ_NAME = 'CRUDE ASSAY' AND S.SMPL_PNT_NAME = 'CRUDE CVILLE'  ORDER BY S.SMPL_PNT_NAME, R.PRPRTY_NAME,S.SMPL_DT, S.SMPL_TM;

                        • Re: Help with RDBMS SQL Statement
                          Srinivas

                          Hi  John/Marty,

                           

                          My apologies for writing CONCAT function is not available.If the query  is working on the source side then should not be a issue with query and function used in the query.

                          we have used the below format of query to add values from different columns to form a tag name..Its working..

                           

                          SELECT NVL(sdt,NVL(c.Last_update,c.creation_date)) AS PI_TIMESTAMP,

                          ((decode(c.Field_id,2,'FLD1-')||LPAD(UNIT_ID,4,'0')||'-OP-'||LPAD(Well_id,3,'0')||'-'||String)||'_WHP') AS PI_TAGNAME1,well_press AS PI_VALUE1,0 AS PI_STATUS1, c.REMARKS AS PI_ANNOTATION1 FROM

                          WHERE  CLAUSE....

                           

                           

                          Regards

                          Srinivas