Reports & Discoverer :: Ignoring Null Values?

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


ADVERTISEMENT

Reports & Discoverer :: Matrix Report And NULL Values

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

Reports & Discoverer :: Count Records With Not Null Column

Jun 25, 2013

i want to count group above report records! want to count department group employees there in time is not null!

my report query

SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....

im doing it with formula column!

function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......

View 18 Replies View Related

Reports & Discoverer :: Substring Of Values

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

Reports & Discoverer :: Sum Of Values Of Formula Column?

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

Reports & Discoverer :: How To Compare (lookup) Values Between Worksheets

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

Reports & Discoverer :: Get Unique Values With Formula Column

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

Reports & Discoverer :: How To Get Profile Values To Display In RDF Report

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

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 View Related

Reports & Discoverer :: Between Clause To Compare Two String Values

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

Reports & Discoverer :: Inserting Values When Running A Report?

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

Reports & Discoverer :: Select List Of Values From Parameter Form?

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

Reports & Discoverer :: How To Return Values In A Text Or Boiler Plate

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

Reports & Discoverer :: Lexical Parameters Are Used To Substitute Multiple Values At Run Time

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

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I 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

View 3 Replies View Related

Reports & Discoverer :: How To Run Discoverer Reports In A Forms Menu

Jul 5, 2010

how to Run a Discoverer Reports in a Forms Menu.

View 2 Replies View Related

Sequence In Select Ignoring Where Clause?

Sep 28, 2011

I want to use a sequence to populate a field for my insert statement. Should be simple right?

So here is an example
insert into sometable ( seq, else)
select
myseq.nextval
,somethingelse
from sometable@dblink
where somethingelse = 10

now if i remove my sequence call for nextval it correctly uses the where clause but when I use the sequence in the select statement it is ignoring the where clause completely. I've never had this issue before...is this because i'm using a dblink now?

I'm using toad 10 on oracle 11g in multi-schema environment.

View 2 Replies View Related

Select Into With Null Values?

Apr 20, 2007

i have a stored proc where i am selecting a value into a variable like so:

SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;

Now, my problem lies in where there is no value returned (oracle will throw an error).

View 3 Replies View Related

Handling NULL Values?

Jan 23, 2013

I have 2 tables, AFF_TEMP and COUNTY
AFF_TEMP has the following columns FNAME, LNAME, EMAIL and COUNTY
COUNTY has 2 columns COUNTY_ID and CNAME

Both tables have the following test data
AFF_TEMP
Joe, Bloggs, joe@gmail.com, ''
Ann, Bloggs, anne@gmail.com,Donegal

and COUNTY column in AFF_TEMP can contain a NULL value

County table has the following Test data,
1, Dublin
2, Donegal
3, Tipperary,
4, Galway

I am trying to select the following from both tables FNAME, LNAME, EMAIL, COUNTY_ID.Tried the following queries
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A LEFT OUTER JOIN COUNTY C ON A.COUNTY=C.CNAME
OR (A.COUNTY IS NULL)
select a.FNAME, a.LNAME,a.EMAIL, C.COUNTY_ID FROM temp_aff A, COUNTY C
WHERE C.CNAME IN (SELECT UPPER(A.COUNTY) FROM TEMP_AFF A)

[code]...

View 3 Replies View Related

Replacing Null Values

Apr 23, 2010

Is there a way to replace a field with another if the particular value returned is null?

View 11 Replies View Related

SQL & PL/SQL :: Columns With NULL Values?

Dec 17, 2010

We are working on a migration project and we need to move 75 million rows from source system to target system.

Total number of columns in source system - 90 cols.

Out of the 90 columns 10 cols are system fields and rest 80 are properties for each record.

We are required to migrate all system cols and some required properties. In total we will migrate around 25 columns[10+15] for each record.

Before actaul migration , we need to do a data cleansing activity and hence we move the data to a staging table.

To create the staging table, we considered the below appraoches.

1. Create the staging table with around 30 coloumns so as to fit the data from source system[map the columns based on datatype]

2.Create the staging table with actual columns[90 columns] and import only the required properties. The rest all columns will remain NULL.

Do the data cleansing and move to target system.

My question here is, if we go with approach 2, We will not mix the data, as there will be a one-to-one mapping. But many columns will not have data and remain NULL. Will it affect the performance since we deal with 75 million rows.

View 3 Replies View Related

SQL & PL/SQL :: Replace Zero With Null Values

Jul 28, 2010

I have a table abc with two column (marks,id) both can have any value.

Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.

how to replace zero with null.

View 22 Replies View Related

PL/SQL :: How To Restrict Null Values

Jul 20, 2012

I have main query..And i written a Query for Issued Quantity. The query is retrieving exact data. And when i add the Query in main query the data is coming with extra null values.

for ex. Query for Issued Qty fetch 16 records. and i run the main query it fetch 20 records...there r 4 records which issued Qty is null. How can i restrict that records.

View 14 Replies View Related

Query Ignore Null Values

Aug 5, 2010

My query is ignoring the null value

CODEselect count(*) as COUNT from EMP where EMP_ID in '1001,1002,1003'

returns the result as

CODECOUNT
----------------------
0
1
2

EMP_ID '1001' has 0 records(i.e., EMP_ID 1001 doesnt exists in the table)
EMP_ID '1001' has 2 records
EMP_ID '1001' has 3 records

where as the query

CODEselect count(*) as COUNT, EMP_ID from EMP where EMP_ID in '1001,1002,1003' group by EMP_ID

returns the result as

CODECOUNT | EMP_ID
---------------------
1     | 1002
2     | 1003

I want the null value also to be counted when I run the second query i.e., the output should be like

CODECOUNT | EMP_ID
---------------------
0     | 1001
1     | 1002
2     | 1003

View 2 Replies View Related

SQL & PL/SQL :: Order By DATE Which Has NULL Values

Jun 11, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..

SELECT d.event_code_dt, count(d.event_number) count1
FROM main_event a,sec_event d
WHERE a.event_id = d.event_id(+)
GROUP BY d.event_code_dt
ORDER BY d.event_code_dt, count(d.event_number) desc

Results are like this

11-MAY-10
21-MAY-10
22-MAY-10
NULL

And I want results to be like this:
NULL
11-MAY-10
21-MAY-10
22-MAY-10

Same thing happens with Event_month
NULL
Feb 2009
Aug 2009

View 2 Replies View Related

SQL & PL/SQL :: How To Use Or Condition To Check If 6 Col Values In A Row Is NULL

Aug 23, 2010

How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.

select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL

View 1 Replies View Related

SQL & PL/SQL :: Count Function Over NULL Values

Jun 17, 2010

There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.

But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.

View 3 Replies View Related

PL/SQL :: String Having Null Values Between Commas

Dec 29, 2012

My DB version is 10.1.0.5.0

I want extract the values from the string using below query but i am unable to bring the correct result.

WITH t  AS ( select '123,1,3,22' col FROM DUAL
        UNION ALL
        SELECT '123,,2,1' FROM DUAL
        UNION ALL
        SELECT '5,1,2,,' FROM DUAL
        )

[Code]...

My required result like below.

123     1      3      22
123            2       1
5       1      2       

get required result using regular expressions.

View 3 Replies View Related

Forms :: Data Block Ignoring Order By Clause?

Mar 6, 2012

I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.

View 6 Replies View Related

SQL & PL/SQL :: Interval Partitioning And NULL Column Values

Mar 12, 2010

Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.

If you create a range partitioned table; using interval partitioning, say something like this:

CREATE table range_parti (
CODE NUMBER(5),
DESCRIPTION VARCHAR2(50),
CREATED_DATE DATE)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

but you try to insert a null value as the partition key, you get the following error:

SQL> INSERT INTO range_parti VALUES (1,'one',NULL);
INSERT INTO range_parti VALUES (1,'one',NULL)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Elapsed: 00:00:00.07

Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.

It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved