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
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.
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.
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]....
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.
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.
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.
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.
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.
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 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.
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)
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.
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.
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
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
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.
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.
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.
