I would like to know if there is a way to define values in a field to a defined new data.
As an example:
If field A value is equals to May, then set field "Quarter" data value to "2nd Quarter".
If field A value is equals to January, then set field "Quarter" data value to 1st Quarter".
if i make the source for a field is a current date with format dd/mm/rrrr hh:mi:ss and the report published on the application server and the clients from different machines run this report
what the field display on Cline or on DB or On Application Server?
where I have to display the value of current cell in next following cells.The table structure is as follows :
ttdate - date
individualplanid - varchar(10); - train number
sch_deptime - number(8); - scheduled departure time in milli seconds
sch_arrtime - number(8); - scheduled arrival time in milli seconds
stn - varchar(10); - station short name
dep_delay - number(8); - dep delay in milli seconds
arr_delay - number(8); - arr delay in milli seconds
The delay is filled continuously by application software continuously.I want to make a query where I want a calculated field which does the prediction of train arrival on coming stations. This shall be done as the delay + sch_deptime for all the next stations. Following is a dataset :
individualplanid sch_deptime sch_arrtime stn arr_delay dep_delay
BO646NULL57900000BVINULL80000
BO646NULL58140000KILENULL40000
BO646NULL58320000MDDNULL20000
BO646NULL58530000GMNNULLNULL
BO646NULL59160000ADHNULLNULL
BO646NULL59550000STCNULLNULL
BO646NULL59940000BANULLNULL
BO646NULL60540000DDRNULLNULL
BO646NULL61200000BCLNULLNULL
BO64661800000NULLCCG12000NULL
[code]....
The last column (EAT) is calculated one. It shall be like as the dep_delay is updated the EAT for the following records shall be sch_deptime + dep_ delay. I did was something like this. The calculated field is cum_depdelay which is only the delay ( not sch_deptime + dep_delay ).
select ttdate,individualplanid td,station,sch_deptime,sch_arrtime,act_depdelay,
sum(act_depdelay) over ( partition by individualplanid order by sch_deptime rows between current row and unbounded following ) c_depdelay
from logtime where ttdate='14-Mar-2011' and individualplanid='BO646' order by sch_deptime,sch_arrtime;
[code]...
how shall i proceed ?
After validating the SQL statemnet during creating a Custom folder in OracleBi Admin following Error displays. While the same query execute in toad successfully.
The Custom SQL entered is not a valid SQL statement - ORA-01031:Insufficeint Privileges
Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.
My Report Query looks like this,
select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number
:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.
But the CASE statement is throwing Error
ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
When running my query in sql developer, I have to execute the command apps.fnd_global.apps_initialize(user_id, resp_id, resp_appl_id) in order to show the result of my query. Without this, the query returns nothing. The problem is I am using this query as a custom query for my Discoverer report and my report does not show any record.
View 3 Replies View RelatedI have a doubt that can we make a report on sum of the salary record wise and grouped by dept number?
like as shown BELOW
Dept No Dept Name
10 Accounting
Employee ID Name Salary Sum Salary
7782 CLARK 2450 2450
7934 MILLER 1300 3750
7839 KING 5000 8750
Dept No Dept Name
20 Research
Employee ID Name Salary Sum Salary
7369 SMITH 800 800
7788 SCOTT 3000 3800
7902 FORD 3000 6800
Dept No Dept Name
30 Sales
Employee ID Name Salary Sum Salary
7844 TUNER 1500 1500
7499 ALLEN 1600 3100
7521 WARD 1250 4350
7900 JAMES 950 5300
emp table
Name Null? Type
----------------------------------------- -------- ------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
dept table
Name Null? Type
----------------------------------------- -------- --------------------------
DEPTNO NOT NULL NUMBER
DNAME VARCHAR2(15)
LOC VARCHAR2(15)
I have a report created in Reports6i. I have two fields at the bottom
1.Page Total
2.Voucher Total
I do not want to print the Page Total if the total number of pages = 1.
How can I achieve this?
I have one table storing all the information about employye, i have to develop three different reports based on the same table with different groupings , is there a easy way to call each format differently using single interface ,something like ref cursor.I dont want to create two or 3 different reports , instead choose the format.
CREATE TABLE OT_JOB_DET
(
JT_TXN VARCHAR2(12 BYTE),
JT_NO NUMBER,
JT_DT DATE,
[Code].....
I want to generate the excel output from a RDF.
If I am using the DESFORMAT=DELIMITED then I am getting unformated data and some time I am getting less data as per PDF output.
There are about 25 queries with interrelated way.
How can I get the output in excel from a RDF.
In Reports 6i Functionality was working while we were running reports because of reports were running in Previewer mode but in 10g reports output coming in PDF format.
I am running a report thru form....While i individually run my rdf file it will run successfully with data.When i tried same rdf by using form , it will run but without data..thats why i check PARAFORM ='YES' but all parameter goes perfectly...
my rdf run but without data.
We have a view that shows the all the data that we expect, so we created a report to show that data, the report worked, then all of a sudden the report stopped showing all the data. (we did have a server move)I checked the view and the all the data is there....
I have created a new report based on the view but it does the same thing, it doesn't bring all the data up (i get very old data), there are no conditions or anything.
other tables and views seem fine. i have recreated the view and i still have the same problem.
I have a report for this report i want to insert a data into one table. when am inserting the data then two time its inserted. if i run the report its inserted the data and i have taken the pdf file then also inserted 2 time for this.
Note: This report having only one group. not necessary below method give any other solution if destype=pdf then no need to insert otherwise insert the data.
I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..
The 1st query is..
SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;
It created two user parameters.ie FROMDATE and TODATE.
And 2nd query like this
SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;
I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...
following is my query model
SELECT INV.VENDOR_ID,inv.invoice_date,inv.po_header_id,
INV.INVOICE_ID,
INV.INVOICE_NUM,
[Code]....
WHAT i have to write in select field
Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.
View 3 Replies View RelatedI know how to append fields to text, but how do I deal with the variable length? I want to place text on a report and inside the text I have data from two different fields. It works great except that my fields are 35 characters long. If the data fills the entire field it looks great, if the data is only 10 characters long, I have a huge gap between the end of the data and the text that follows. How do I fix this?
View 3 Replies View RelatedHow To Convert Data from Reports 6i to oracle Tables.
View 2 Replies View RelatedI need to design a report out of the below data:
1. bprf_no will be my primary field
2. report parameters will be the bill_month & no_of_months
Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).
In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.
For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:
BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6
Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.
BPRF_NOBILL_MONTH VOID_STATUS AVG_IND
------- ----------- ------------ --------
BP0130-Jun-1200
BP0230-Jun-1200
BP0330-Jun-1201
BP0430-Jun-1201
_BP0530-Jun-1201_
_BP0630-Jun-1202_
[code]....
My below query is fetching me wrong data:
----- Query -----
select bprf_no, no_of_month--count(*)
from
(
select a.bprf_no, count(*) no_of_month
[code]....
Here BP03 & BP04 should not come into the listing itself.
Here is my req - There are 2 columns C1, C2 - both of which capture a different set of values in 2 seperate custom oracle apps forms.
I have a RDF query to get the result set of C1,C2.
When this query does not return any values I need to look in form 2 and handle each value of C1 separately. I do not know how to achieve this with formula column - since I can perform checks/validations on the data returned by the query using a formula column.
I am actually having problems with a report that is generating wrong data in Windows 7 only. I have the same report working correctly on many Windows XP Computers.
View 9 Replies View RelatedI have query in that query i should display the number of row. I have tried to select the row num but when i use it the data will be duplicated. I have found something that related to use temporary table, or subquery but all of them will not work because the query depends on many tables and if i use temporary table filling the table and then reading from it will take too long time.
View 11 Replies View RelatedI have a database in access 2003 with the empno,empname and photo (bounded image). Using queries and an ODBC connection I have inserted the table into Oracle 8i (8.1.7). When I try to read the data using reports I get 'REP-1818 Unable to read data in image format.' error for the photo field.
View 5 Replies View RelatedI need the data in this below format.
I added the image...
how can i print the stored images in reports 6i from database, based on the condition,that suppose if i have one field approval status whose flag is either 3 or 1 , based on this flag ,if the status is 3 then image should be displayed otherwise no. the rest of the process.
CREATE TABLE FT_GALVA (GALV_NO VARCHAR2(2),GALV_APPR_ST NUMBER,img_name varchar2(30),PIC_GALV BLOB)
Create or Replace directory image_dir as 'e:image_dir';
Grant all on directory image_dir to public
/* Formatted on 2013/04/25 23:50 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE insert_image_file (
p_id NUMBER,
p_status NUMBER,
[Code]....
Report 6i. I created Matrix Report . it is working fine.
I would like to know that it is possible If Matrix Report Cell do not have data then display any words like 'A'\
In the following query which is highlighted ,I need to consider the records which have T.CURRENT_STATE='COMPLETE' AND 'CMPSCSRC' AND 'FINISHED' when M.MAINTAINED_FLAG = 'Y' AND S.SALE_LOCATION_ID = 118443 .So when i tried to write by using case stmt as follows it is giving me records only with T.CURRENT_STATE='COMPLETE'.But i want the records that satisfies all three current_states .
SELECT INI.UPC_ID,S.SALE_LOCATION_NAME,S.SALE_LOCATION_ID,I.KEYCAT_ID AS INITIAL_KEYCAT_ID,M.XLONG_NAME AS INITIAL_KEYCAT_NAME,
CASE WHEN M.MAINTAINED_FLAG = 'Y' THEN 'MAINTAINED' ELSE 'NON MAINTAINED' END AS INITIAL_MAINTAIN_DESC,
I.APPROVAL_USER_ID AS INITIAL_APPROVED_USER_ID,I.APPROVAL_DATE AS INITIAL_APPROVAL_DATE
[code]...
I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.
View 11 Replies View Related1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"
2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.