In my report I'm using inner join in queries .I got correct record but itz repeated.How to deduct that repeat values?
this is my query:
SELECT f.feeid,
f.studentid,
( s.first_name + last_name )AS studentname,
b.branchname,
c.coursename,
y.yearname,
[Code].....
I am using oracle developer 10g R2.also using Crystal Report 9. Now i want to call the report created by crystal report.
View 8 Replies View RelatedOne of my developer creating views in his own schema,we are able to see that views from sqlplusw. But we are unable view them from crystal report tool(CRW32) ,Except this schema all are visible...
View 3 Replies View Relatedwe have moved our database to a new server which is 64bit. (earlier database was 32bit)
when i try to connect my crystal report or Vs .net to the new server i'm getting below error.
ORA-03113: end-of-file on communication channel
my computer is Win 7 (32bit)
I have table with the values as below.
C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
I want to run a report developed by crystal reports by click on a button in oracle developer 6i.
Is it possible. If yes then how can i do this.
After running my report I generate into the file by delimited type and then I save as it by XLS extension.The problem is each row of this excel file has header repeatedly!
View 4 Replies View RelatedI have a query in oracle report in which i am getting this output.Manager Arnav have 2 employees Inder and kaushal whose salary is 10000 and 20000 respectively,
And another manager is Anjali whose employees are Kavya and inder whose salary is 40000 and 10000 respectively .as Inder is repeated I want the salary become 0 in place of 10000 second time.I am in dilemma,What should i do ,if i want to change 10000 to 0
Manager employee salary
Arnav Tiwari Inder 10000
kaushal 20000
Anjali Kavya 40000
Inder 10000[/b]
What should i do in the formula of salary.according to employee name .means if Name exists already then salary value should be 0 and if it comes for the 1st time then its actual value i.e 10000 should be printed.
I have problems in Oracle returning data from a stored procedure. In MS SQL it's quite simple.
i.e.
CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/
next the stored procedure:
CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUTGLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;
Next trying to run the Stored procedure:
VARIABLE resultSet REFCURSOR
EXEC mytest (:resultSet);
Result always returns this error:
REFCURSOR - Unrecognized type.
I have tried using sample on the internet, but all returns the same error, what am I missing
I have a table with the following column and data is like this.
SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');
[code]....
Is there any function to count number of specific character inside string. For example :
my string is 'ABCEF AYZA'
I want to calculate how many 'A' exist in my string. result should be = 3
I recently got a new PC at work which is Windows 7 64 bit.
We run lots of crystal reports(1000's) that pull data from an oracle back end.
I have installed the Oracle Client 32 bit on this machine and have no problem connecting to the database and pulling back data.
However, when I use crystal to access the TNS Name it gives me the following error.
[URL]
My path variable on the PC has "C:oracleproduct10.2.0client_1BIN;".
I also tried to create and ODBC using the 32bit ODBC on the machine and that connects fine, but when you try to access the DSN from Crystal, I still get the 12154 error.
I have two tables one source table and one destination . Column names and data types of both table are same.
source table (source)
ID NAME
1 aa
2 bb
3 cc
. ...
. ..
. ..
destination Table(dest)
ID NAME
1 aa
2 bb
I need destination table like
destination table
ID NAME
1 aa
2 bb
3 cc
. .....
. ....
I want to remove repeated rows
i nee a function which accepts 4 digit number and in four digit number the number should not be repeated. i want all the number in the output.
ex:1234
2367
1262(is not valid)
I have a query that seems to repeatedly call an index scan on a table for reasons I'm not sure about. Why it would be doing the index scan on totaldwellingarea in the dimensions table (DIMEN_PID_TDWELLAREA) repeatedly? This only seems to happen when I put on the range clause d.totaldwellingarea between scr.lowvalue and scr.highvalue.
I am using Oracle version 9.2.0.3.
select d.propertyid,d.totaldwellingarea, e.size_, scr.size_
from eqid e, dimensions d, brt_eval.size_code_ranges scr
where e.style not in ('1','A','G','L') and e.size_ = '0'
and d.propertyid = e.propertyid and e.style = scr.style and d.totaldwellingarea between scr.lowvalue and scr.highvalue;
CREATE
TABLE "BRT_ADMIN"."EQID"
(
"PROPERTYID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"EQID" CHAR(10 BYTE),
"ZONE_" CHAR(1 BYTE),
"AREA" CHAR(1 BYTE),
[code].....
query for number of users connected to the database:
select count(*) "Number of users Connected" from v$session where type='USER'; it produces 125
The following query is used to describe where and when the users are connected to the database.
set pages 500
set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20
[code]...
It should produce 125. But its showing only 119 users. so around 6 users missing in the list. find why 6 users are not listed..
For the below query:
SELECT id, pob,exp
FROM emp
where exp= (:exp)or @exp is null
O/P:
id pob exp
_____________________
1 CT 2
2 NJ 3
3 NY 2
It takes only one value of :exp but, I would like to give multiple values separated by ','. My problem is the parameter can be either single value or Blank or multiple values. My code do work for single value and Blank, Now how to implement multiple values .
how I can profile values to be displayed in RDF report.Here is what I have in my before report trigger
srw.USER_EXIT ('FND SRWINIT');
apps.fnd_client_info.set_org_context(FND_GLOBAL.ORG_ID);
:CP_Fnd_User :=FND_GLOBAL.USER_NAME;
:CP_CompanyName := FND_PROFILE.VALUE('xx');
When I print the value of the user name it gets printed fine.But when I print the place holder column value for the profile I get null.
I want to Fix Null values as 0 in Matrix Report. But i am not able to Do it...
let it be there is one item NUM1
what Sequencing and triggers(format trigger or else) should i follow on it with what code?
I have a report, where there are opening balance and closing balance, so i have to store the closing balance values in a separate table during runtime and should show this closing value as opening balance for next month.
View 24 Replies View RelatedI have created a workflow which is going through 12 pages. At each page the user needs to choose his variables.
This could be the y-axes, x-axes, amount, surname ... etc. The variables are given to the next page via branch to next page process. Everything works well until I need to create an interactive report out of the chosen variables.
Lets say I have a variable called :P7_XAXIS with static LOV :
Amount,Amount;Date,Date;quantity,quantity
Page 8 is my IR. If I try :
Select :P7_XAXIS from mytable This will not work.
But if I try
Select Date from mytable This will work. Why? In my workflow there are several variables that can be used in a where condition. This also works. But I need to access the values before the condition and I also need to get a chart out of it.
I have a user requirement where they want to be able to select multiple values for a single parameter that will act as a filter for a report. The possible values for a parameter can come from almost any master data Oracle eBusiness table. I don't want to just present the user with a LOV where they search down a list selecting what they want. That wouldn't be very practical for selecting part numbers from 1000's of rows. I also want the user to easily see what values they have selected. I thought about providing a button that would navigate to a multi-row form where they could record their values and use those values in the where clause of the SQL for the report, but that is a somewhat complex solution I have never tried before. how to do this. I'm working in the latest version 4 of APEX.
View 1 Replies View RelatedOracle 10, Apex 4.0.
Working on this query:
SELECT waarde1,waarde2,
APEX_ITEM.POPUP_FROM_QUERY (3,waarde3,'select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)') waarde3
FROM lov_test
ORDER BY 1
The idee is to get a popup or dropdown box for "waarde3" in witch the selectable values are waarde1 to waarde2 rising with 0.1 at a time.
And this in a multi row updatable report.Example
waarde1 waarde 2 waarde3
(possible values in the dropdown box)
1 4 1-1.1-1.2 ... 3.8-3.9-4.0
3 7 3-3.1-3.2 ... 6.8-6.9-7.0
The error I get is:Error in init lov: ORA-00936: Ontbrekende uitdrukking. p_lov:select select ((waarde1-0.1)+(level*0.1)) d, ((waarde1-0.1)+(level*0.1)) r from (select * from lov_test where waarde1 = c001) connect by level <= (((waarde2-waarde1) *10)+waarde1)wwv_flow_security.g_security_group_id:1264429985836387wwv_flow_security.g_curr_flow_security_group_id:1264429985836387 Unable to initialize query. For every row in the table lov_test.
We are getting Negative values of Library cache hit ratio in AWR Report of 11g(11.2.0.3) with Solaris[tm] OE (64-bit). Why it shows negative value.
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.87 Redo NoWait %: 99.99
Buffer Hit %: 92.17 In-memory Sort %: 100.00
Library Hit %: -3,321.23 Soft Parse %: 81.95
Execute to Parse %: 92.88 Latch Hit %: 95.11
Parse CPU to Parse Elapsd %: 87.25 % Non-Parse CPU: 81.39
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View RelatedI have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
How can i print a report name with full path at left bottom on report.
View 3 Replies View RelatedHaving this issue in Apex 4.1.1 and 4.2 (haven't tried it in versions prior to 4.1.1 yet). So I got my standard IR, and trying to create a private report with control breaks, aggregations.
Once I save it as a private report, It applies the same on my primary report.
It's no big issue as I can flip back to Primary report and delete the control breaks, aggregations but is some kind of bug? Ideally it shouldn't modify my primary report unless I try to save it as "Default Report Setting".