This content has been marked as final. Show 16 replies
Off the top of my head:1 person found this helpful
You are using the LOV value in the query for the tabular form? Correct? If the new values had the wrong "case" (upper, lower, sentence) or trailing spaces, then the query would fail to find data. ???
Edited by: Howard (... in Training) on Mar 25, 2013 5:01 PM
What is the source for the LOV values? A table in the DB? A master List defined in APEX? Do these values match the data in the table?
Thanks Howard - all of the values are uppercase in the table and LOV so the case shouldn't be an issue. I'm going to try to rebuild the report from scratch and see what happens. Any other ideas?
edit: the source of the LOV is basically a select distinct on the same table the the tabular form is based on. So the case should match and I checked for trailing spaces etc and that doesn't seem to be an issue either.
edit 2: I just tried to reproduce on apex.oracle.com and my inserts are all showing up immediately in the tab form so this is really baffling.
Can you show us what the query is? Or is there proprietary "stuff" in it?
Do you have a time/date range dependency in the query that's satified on apex.oracle.com but not on your own system?
How complex is the WHERE clause? My approach is to start commenting things from the WHERE clause untril I get what I expect and then determine where that was being excluded in the WHERE.
The query works fine when I create a new page, new tabular form etc. in my app.
Edit: I should also mention that I performed the insert in sqldeveloper using a database link to the source table. Perhaps there are issues with APEX when using db links? Strike that - I just tried a simple SQL insert and those records aren't being returned either.
This is the strangest behavior of apex I've ever witnessed. If anyone is internal to Oracle and wants to look at my app I'd be happy to grant you access.
Just a thought. Do all the "new" rows/records -- the ones you cannot retrieve -- have new LOV values? Or are they a mix of old and new LOV values? If they have only the new LOV values, try updating one to an old LOV value and then see if it's retrieved? I'm trying to isolate if there is something in one of the DAs (or other) that's preventing records with new LOV values from being retrieved.
Some but not all the new records being inserted result in new LOV values.
I tried to insert a record for an LOV value that is returning rows and the new record does not get returned in the query. So for example, if my LOV value is 'Department A' and it returns 8 records and then I insert a new record for Department A, I still only get the original 8 records returned. Interestingly my LOV also includes a record count, and I can see in the LOV that Department A does show 9 records.
Does that help narrow it down any?
Can it be there is a problem with DISTINCT rows/records? Could you be counting ALL rows/records but only retrieving DISTINCT rows? Do you have any UNIONs in the query? Instead of UNION ALL? ???
Another thing. Does your chart have a Maximum Rows setting? What is it?
Thanks Howard - My chart is returning the proper number of values; it's just the tabular form that is not returning the proper records.
The query is really simple:
I have had no issues with this until I noticed that records that I insert with SQL aren't getting returned. I can run this same query in sqldev and it returns the proper results.
select "ROWID", "TABLE_NAME", initcap("COLUMN_NAME") "COLUMN_NAME", "COLUMN_VALUE", "ENG_VALUE", "LANGUAGE", "SOURCE_LANG", "CHAR_LENGTH", LENGTH ("COLUMN_VALUE") COL_LENGTH, "COMPLETION_STATUS", "LAST_UPDATED_BY", "LAST_UPDATE_DATE" from "#OWNER#"."GSE_LNG_STRINGS" WHERE "LANGUAGE" = :P15_LANGUAGE AND "TABLE_NAME" = :P15_SUPERLOV AND "ENG_VALUE" is not null AND COLUMN_VALUE like ('%'||nvl(:P15_TRANS_VALUE, '%')||'%') AND ENG_VALUE like ('%'||nvl(:P15_ENG_VALUE, '%')||'%')
More thoughts ...1 person found this helpful
How about running this in the APEX SQL command window? Then what do you get? You'll have to give values to the bind variables when they are parsed.
!!! Please do this: If you replace the bind variables in the SQL with hard-coded values, to you get the expected values back?
Interesting! I ran in APEX SQL Commands window and entered the variables when the window popped open, and I get the same results as my report, i.e. 8 rows for Dept A.
Next, I replaced the variables in the sql with hardcoded values and viola! now I get 9 rows. I think you are on to something! What does that mean?
Ah!!!!! (Subject to being contradicted by some very special case someone might come up with ...) All APEX bind variables are character. So, are any of these "bind variable"columns numeric or dates that might be undergoing implicit conversion?
Nope - there are really only two required variables in my WHERE clause, language_code and table_name. So for example the above query would run on language = 'ZHS' and table_name = 'GL_JE_SOURCES_TL'. Both are varchar2 columns in the table.
At this point, I'd do a binary search type of quest for the variable causing a problem.1 person found this helpful
Start with your hard-coded query and then put :P15_LANGUAGE and :P15_SUPERLOV back into the query keeping the two "LIKES" hard-coded. I'm supposing that will work and the problem is in the "LIKES" code.