topic Re: Query returns no results from IFS/Oracle in QlikView

Query returns no results from IFS/Oracle

Mon, 14 May 2012 12:08:25 GMT

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

Thu, 24 May 2012 14:36:08 GMT

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

jonathandienst — Thu, 24 May 2012 15:09:52 GMT

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

Fri, 25 May 2012 08:13:31 GMT

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