3 Replies Latest reply: May 25, 2012 4:13 AM by Roger Barnard RSS

    Query returns no results from IFS/Oracle

      I am trying to create a new report from IFS history logging tables and am being driven nuts by a weired problem.

       

      I have created 2 queries to extract data from 2 tables. One of them works perfectly and gets 75k lines of data.

      The other one (below) always returns 0 lines.Both are on the same tab, using the same connection to Oracle.

       

      If I run the query in SQL developer, it works and returns a few thousand lines, but the same sql script (copy/paste) returns 0 in QV

      WHY?? Have shuffled the order of fields about, have tried select *

       

      Does anything stand out as being wrong in this script?

      Has anyone else seen this and know why it does it?

       

      Thanks

       

      Roger

       

      *********************************

       

       

      AuditHeader:

       

      LOAD

      LOG_ID, 
      USERNAME,
      MODULE,
      LU_NAME,
      TABLE_NAME,
      HISTORY_TYPE
      ;
      SQL

      select LOG_ID, USERNAME, MODULE, LU_NAME, TABLE_NAME, HISTORY_TYPE , TRANSACTION_ID from IFSAPP.history_log;

       

      Drop  AuditHeader; 

       

       

       

       

       

       

       

       

       

       

        • Query returns no results from IFS/Oracle

          I tried every variation I could thnk of and nothing worked until suddenly, after re-using the wizard to generate the query, it started to return data.

          Not sure what previous action it was that did it, as by then I had tried so many things but it is now working and returning data.

            • Re: Query returns no results from IFS/Oracle
              Jonathan Dienst

              Hi

               

              Sometimes if you copy a connection string from one model to another, the scrambled username and password are no longer correctl y encrypted. Regenerating the connection string fixes the problem as you saw.

               

              Regards

              Jonathan

                • Query returns no results from IFS/Oracle

                  Hi Jonathan,

                  Thanks for that. Strange thing was that there were 2 queries in the original. One returned data, the other didn't but they both used the same connection string.

                  I tried swapping them over to see if was the order that caused it. Same thing

                  I tried deleting and recreating the failing one. Same problem.

                  I could run both queries from SQL Developer and both returned data but one returned 0 records every time in QV. No error.. just no data!!

                  Then after days of frustration, I must have done something different and it just worked.. Problem was that by then I had tried so much I had almost given up on it.. then it started returning data.

                   

                  Roger