Application Express :: Column Link Where Column Number Changes With Query
Feb 11, 2013
We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report
<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012
[Code]....
The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.
Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)
ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.
So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.
On my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
I have IR wit one column link target to URL, and it uses javascript:my_function(#COLUMN_VALUE#)as URL, that is, calls some function and passes the value of that column to the function; everything works fine; but when I place a cursor over the link, bottom line of IE9 browser (is it called a 'status bar'?) clearly shows:
'javascript:my_function(<actual value>)' Is there any way to prevent it from showing function name and parameter value? I tried onmouseover="window.status='';"but it does not work
I'm a APEX newbie using APEX 4.1. I have a table with engineering drawing information (drawing number, title, revision, etc.) and another table with the corresponding drawing files (pdfs stored as blobs where the filename is the drawing number with a pdf extension). The user wants to search for a particular drawing by drawing number from the drawing info table and if the drawing number exists in the drawing files table the drawing number will also be a download link for the file, if the drawing number doesn't exist in the drawing files table than the drawing number will display as plain text (not as a download link).
I have a report linked to a form. The link column is currently ROWID and I want to change it to the Primary Key of the DB table, which is called ID.
I have done this before on a previous report/form where that ID was a display column and it works fine. Now I'm trying to copy what I did, which is this:
On the report page, go to Interactive Report attributes, scroll down to Link Column, and for Item 1, change the Name and Value.
The problem is that Name does not list P52_ID. If I type it in, it throws an error when I run the page.
I think I added ID, which is also a Display column, later on, so I wonder whether Apex has a refresh issue and can't "see" P52_ID.
I have an Interactive Report with a column that I need to create a link on but I need to pass the value of another column.
This report shows the Features of a particular Release. There is a select list for the Release that is selected and executes the report. There is a column that indicates whether the Feature has a Commitment attached to it. If there is have that a link to another report that shows all the Commitments for that Feature by passing the FEATURE_ID which is also a column on the Feature report.
Query: SELECT .... ,CASE WHEN ft.feature_id IN (SELECT cc.feature_id FROM customer_commitments cc WHERE ft.feature_id = cc.feature_id ) THEN 'Yes' ELSE 'No' END "Commitment Exists" ,ft.feature_id ....
FROM ....If "Commitment Exists" has a 'Yes', the 'Yes' is a link that will pass the FEATURE_ID to another report that will show the Commitments for that Feature. If the "Commitment Exists" has a 'No' then no link. I was also wondering if the Commitment report could open in a seperate window and make it modal?
Apex 4.2.0.00.27 I have a page with a Form region and a Classic Report region. How can I automatically save changes made in the Form when a user clicks the Edit link column in the Report? At the moment, when the user returns to the Form, any changes made there are lost.
I Have an apex page that display a modal window utilizing jquery. In the modal window I have a classic report with a link column that I want to capture its click event.
I was thinking I could create a dynamic action with selection type=jquery selector. Not for sure if I need to do anything on link column and do not know the syntax jquery selector.
I go to Report Attributes, then I click Add Column Link in the "Tasks" right menu, it adds me a column link, I just add some text for the link and a page to go to. Then I run the report and I get :
report error: ORA-01403: no data foundTested with several classic reports on multiple pages.
Debug mode shows me :
0.43816 0.00240 ...Execute Statement: select distinct [...] order by 3,11 ,4 0.44056 0.00162 print column headings 0.44218 0.04816 rows loop: 25 row(s) 0.49037 0.00141 report error: ORA-01403: aucune donnée trouvée 0.49175 0.00078 Computation point: After Box BodyWhen I run the query in my favorite tool, I get expected results.
select memberid, mi.typeid, emailaddress from memberdata@prod_db m join memberitems@prod_db mi on m.memberid = mi.memberid;
These are the tables:
SQL> desc memberdata@prod_db Name Null? Type ----------------------------------------- -------- ---------------------------- MEMBERID NUMBER EMAILADDRESS VARCHAR2(50) ADDRESS1 VARCHAR2(50)
SQL> desc memberitems Name Null? Type ----------------------------------------- -------- ---------------------------- MEMBERID NUMBER ITEMID NUMBER
Shouldn't we raise an 'ambiguous column' error on memberid?
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name, ------ ------ ---------- ------------------------- -----------
# of records not null # of records null ---------------------- --------------------
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.
eg
id description 78664 Pumps Alarm from CAMS RTU154
In the above example this column has 5 word so i want data in 5 rows like below
78664 Pumps 78664 Alarm 78664 from 78664 CAMS 78664 RTU154
This column data can be varied from 1 to any number of words.
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
I have a query that selects multiple series for a bar chart (I have defined only one chart series, and have 3 columns selected to represent 3 series). The series display on Bar Chart is fine. Now I want to create a separate link target depending on which series is clicked on the chart. I am not able to do this.
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.* from DOC a , FOLDER B where a.NUMBER= B.INCIDENT_ID and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
I have a partitioned table with ~50 million rows that was setup with a number(10) instead of a date column. All the data in the table is ALWATS in this format YYYYMMDD
CREATE TABLE T1.monthly ( SEQ_NUM NUMBER(10) NOT NULL, DAY_DK NUMBER(10) NOT NULL ) TABLESPACE USERS PCTUSED 0 PCTFREE 10 [code]........
When I use the exchange partition method the parition is able to move the data from "monthly" table to "mth" table.
desc t1.mth; ### my temorary table Name Null? Type ----------------------------------------- -------- ---------------------------- SEQ_NUM NUMBER(10) DAY_DK NUMBER(10)
Than when I try to alter my temp table "mth". I get an error table must be empty to change column types.
alter table n546830.mth modify (DAY_DK date);
Next I tried making my temporary table "mth" a date column. When I an the exchange partition command I get the following error:
alter table t1.monthly exchange partition DEC_2012 with table t1.mth without validation; alter table n546830.monthly exchange partition DEC_2012 with table n546830.mth without validation * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Method I can use to convert a number(10) to date column and keep the information in a table. Note, I don't care about HH:MM:SS as I never had that information to begin with and would be happy to set that part of the date column to all zeroes "00:00:00"
I'm trying to do a pivot query in oracle to get the years from a column and make a separate column for each. I found an example of the code to use on the internet and i changed it for my own tables but i'm getting errors. Namely a "FROM keyword not where expected" error at the beginning of the 'avg(...' statements.
I have copied the code used in
select stud_id, 2006, 2007, 2008, 2009 from ( select stud_id, avg(case when year=2006 then ((present/poss)*100) else null end) 2006, avg(case when year=2007 then ((present/poss)*100) else null end) 2007, avg(case when year=2008 then ((present/poss)*100) else null end) 2008, avg(case when year=2009 then ((present/poss)*100) else null end) 2009 from attendance.vw_all_attendance_perc group by stud_id );
I'm having an issue pulling an ID from a report column.
I have a report based on the query:
select Inspection_Name, Activation_Status, Activate, Deactivate, Activation_Date, Inspection_Sector_Id from Inspections
where Event_Id = :P0_Event_IdThis report is based on a select list at the top of the page. The user selects an event from the select list and the report is populated.
The query for the select list is: select event_name, event_id
from Water_EventsSimple enough so far. Each row is identified by the Inspection_Sector_Id. I want to be able to turn each row under the 'Activate' and the 'Deactivate' columns into links. I have accomplished this by turning this field into a link. The link needs to navigate to a page that has that rows Inspection_Sector_Id, but am not sure how to grab that Id. I am navigating via URL on the link, i.e. f?p=application_id:page_id:session......
I have a table defined where some columns are of the datatype RAW(16). In fact these are foreign keys to other tables. Our company has a policy that all primary keys are of the datatype RAW(16). Not much I can do about that.
Now I want to retrieve all columns of a certain row in that table and just visualize the foreign keys in hex format. All values are filled out nicely, except the columns with datatype RAW(16). Is there something special I have to do to retreive and display these? I defined an item as text-field for these, but this doesn't seem to be working...
Since I'd rather not do it directly in the SQL used to populate the report, any clean way to style all cells of a given column from an interactive report? Perhaps all cells of interest can be addressed through jquey using the "headers" attribute they share?
I have Dynamic report and column say some_date column and data can be in the columun (01-JAN-13) OR (01-JAN-13,04-JAN-13), (01-JAN-13,04-JAN-13,10-JAN-13) so on.
in actual table column some_date is date field but in report as above (string) because using RTRIM(some_date, ',') some_date within the select MODEL clause.
my problem is when run the report and select filter from action menu then it doesn't give option such as < , > or so on because of string but I want it should give <, <=, >, >= operators too as date column.
I have an IR report and want to add another column such as complete_date. I want to add as a link so when user click it then should display the popup window or javascript popup for date selection. i don't want to use apex_item.date_popup or apex_item.date_popup2. On date selection popup user can choose only two dates such as yesterday or today all other date should be disabled?
In Apex version 4.2 in interactive reports, you can stop users from hiding columns by unchecking a check box under column definition.Is there a way to stop users from hiding a specific column in version 3.2.
How i can disable column in tabular form.working with 4.1 and i have wizard based tabular form.If i have entered value in one column second column should disable and vice versa?
Its solved the problem by fixing the width of the column but the column is not showing the full data now. It is hiding most of its part to fit it in that column.
Recently I remember reading what I think was a blog post on placing the last column of a report underneath the penultimate column - I think it was for classic reports.
I think it was done with some magic in the column definition, or maybe some jQuery?Agnostic to version. It was a more robust solution than using a HTML expression of#COL2#<BR>#COL3#
