Reports & Discoverer :: Select Query With Minimum Values
May 30, 2013
The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!
SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....
View 7 Replies
ADVERTISEMENT
Jun 9, 2012
I am trying to select multiple values from a parameter form based on a select statement.
I created the parameter and write the select statement under list-of-value property However what I want is to let users choose multiple values from the select statement not only one value.
View 1 Replies
View Related
Dec 16, 2009
I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
View 4 Replies
View Related
Dec 27, 2011
i was searching for the example of subject mentioned and visited the following website
[URL]...
here is a tutorial to create such report, but i am confused as here in Report's Data Model they created 3 Ref Cursor Queries and did not Close the opened cursor, is there no need of this or any mistake or if it necessary, where have to Close the cursor?First, they have created following Packages
PACKAGE concl_cv IS
type conclass_rec is RECORD
(EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
[code]...
then, created Functions which created in Ref Cursor Query in Data Model
function q_portdescRefCurDS return port_cv.portdesc_refcur is
temp_portdesc port_cv.portdesc_refcur;
begin
open temp_portdesc for select department_id, department_name from departments;
[code]...
nothing here any Close cursor statement.i am trying to explore this area of the development.
View 2 Replies
View Related
Jan 2, 2012
I have a reference number of application number which is sent to 3 different address as outward number . for example application number is intimated to the office , the employee and the treasury. this number is generated as follows
P/11/10/133
P/11/10/133A
P/11/10/133B
in my outward report when i run it all three numbers are getting listed.
i want only P/11/10/133 to be listed and ignore the rest of the two. i tried to use SUBSTR(INOUT_NO,9), but it reads only from the left gives me only the number ie 133, 133A and 133B. i tried rtrim and ltrim but it only clears the extra space. how do i ignore the other two dispatch numbers.
View 2 Replies
View Related
Jun 21, 2010
I have a report in report 6 which has static field where values are shown for previous year and for current year. the output of the report will be like this
sl no department prev year cur year
01 revenue 2000 5000
02 costing 500 1000
03 excise 8000 900
and so on....
I have a created a fomula field for each and every department and get the value from a particular table. What i require is if suppose the department revenue does not have any value then that row should not be visible. for eg if revenue has a null value then the report should be like this
sl no department prev year cur year
01 costing 500 1000
02 excise 8000 900
View 6 Replies
View Related
Dec 20, 2011
display values in descending order( as got from fomula column). i put that field in above group and got properly. now i want to sum these values at the end of report. its going to some distinct values, while i want all values to be sum.
View 1 Replies
View Related
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 4, 2010
Am looking for some stuff that allows me to get the query text of a query in Oracle Reports. Something like : get_query_text(query_name IN VARCHAR2);
View 5 Replies
View Related
May 10, 2012
How can I create a calculated field in one sheet to lookup a corresponding value in another sheet?
e.g. I have a person ID in one worksheet, and I want to look up the email address from a matching Person ID in another worksheet
View 1 Replies
View Related
Jul 5, 2013
I am facing a problem in leave_form report! want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP' repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'
problem is report is showing 22 records of leavetype 'CL' of same leavedate!
i want that report should show the actual leavedate and leavetype records.using 9i database 6i developer server2003
For leavetype
FUNCTION Cf_1formula
RETURN CHAR
IS
v_leavetype CHAR(40);
BEGIN
SELECT LT.description
INTO v_leavetype
FROM hrm_attendance L,
hrm_leavetype LT,
hrm_employees E
[code]....
View 2 Replies
View Related
Jul 3, 2012
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.
View 1 Replies
View Related
Jun 15, 2013
I have a problem with Between clause used in where statement to compare two string variable.
Query is like this,
select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)
Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.
now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'
:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.
it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).
But how can i get around this as i don't need that value to be fetched.
View 7 Replies
View Related
Feb 25, 2011
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?
View 2 Replies
View Related
Jul 19, 2010
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 Related
Feb 15, 2012
Well i like to display the Values in Boiler Plate/Text. For Example, i have Report with Address Columns. for Tin No: i have Hard coded as TIN: 34XXXXXX01 and i like to change this hard coded item as TIN: 34XXXXXX02 so during Runtime if the input is changed the above item to be changed.
Inputs given in Report Parameter;
if the input is given as 81
TIN: 34XXXXXX01
if the input is given other than 81
it should print as TIN: 34XXXXXX02
How to give it in the Boiler plate.?
View 6 Replies
View Related
Jan 15, 2012
I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"
Select * from emp, deptno &where.
and i know about substitution variables using & is this are same (lexical and substitution) or different.
View 14 Replies
View Related
Sep 18, 2009
When i'm sorting on a calculated field i'm getting the following error:
ORDER BY item must be the number of a SELECT-list expression
View 3 Replies
View Related
Sep 9, 2008
I have some issues in passing array values to IN clause.
I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query
cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;
trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'
and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)
but the query does not returns any result
When I try to hardcode the values in the query as below, its returing 4 rows
select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')
How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause
View 13 Replies
View Related
Jul 1, 2011
i have table with following data.
yearquartersales Revenue
2004Q145678
2004Q287456
2004Q356732
2004Q4120986
2005Q12398
2005Q23900
2005Q36522
2005Q42763
I want the output in following way.tell me the select query for this
yearquarterSales Revenue
2004 Q145678
Q287456
Q356732
Q4120986
2004 total Sales310852
2005 Q12398
[code]....
View 4 Replies
View Related
Oct 21, 2011
I have a requirement while A4 printing..When printing each time user have to select file -> page setup -> size(A4)..THEN printing format become A4 Size. How to achieve this one without user have to go on setting.
View 1 Replies
View Related
Aug 27, 2010
wanna to make matrix report retrieve months year and the number of weeks from dual table it supposes to the weeks number is 52 or 53 week months and weeks on the rows please find attach pic it show what i need to do for exmple
month name: January February March April May June July August September Nov October December
no of week : 1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-20-21-22....... TO 52 week
View 5 Replies
View Related
Oct 9, 2012
I have a requirement like getting list of values from one table and inserting them into another table.I have tried with sub querying but didn't worked out because the select query is returning multiple values.
how to proceed further and the ways how can I write this requirement.
View 1 Replies
View Related
Sep 9, 2013
i have a table named actual_qty,
in that table i have data store in the following columns:
MaterialCode Location Qty
------------ -------- ---
1111 vizag 2
2222 vizag 1
3333 vizag 4
1111 hyd 4
2222 hyd 6
3333 hyd 2
1111 sec 9
2222 sec 1
3333 sec 0
i want query to display data data in the following form
MaterialCode vizag hyd sec
------------ ----- --- ---
1111 2 4 9
2222 1 6 1
3333 4 2 0
View 5 Replies
View Related
Feb 16, 2012
I am making a report in hrd regarding gross, deduction and there difference. I hv an attribute in the table as indicator whose value is addition and deduction. i want the sum of both in two diff column in a single rep. i want the report dept wise.
but i m getting fatal error at run time. which i don't understand.
View 3 Replies
View Related
Jul 6, 2013
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...
View 4 Replies
View Related
Dec 3, 2010
In oracle report how do we execute query2 if query1 doesn't retrieved any records.
View 4 Replies
View Related
Aug 15, 2012
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.
View 3 Replies
View Related
Jun 13, 2013
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)
View 6 Replies
View Related
Feb 1, 2012
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
