PL/SQL :: Retrieve Item With None Of Its Char Values As Null?
Oct 12, 2012
I have a table something as below
Things_t
Things Characteristic Char Value
Item 1 Colour Red
Item 1 Packaging
Item 2 Shape Square
Item 2 Brand Spunk
Now i want to reterive an item with none of its char values as Null. Using the query “ select distinct things from things_t where char value is Null ” will fetch the item 1 also together with item 2. i want to fetch a record from thing for which none of the char values are Null such as Item 2.
Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .
I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.
ex:
the output should look like this.
owner schema table_name total# recs in the table, column_name, ------ ------ ---------- ------------------------- -----------
# of records not null # of records null ---------------------- --------------------
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 would like to let the customer edit the item help text. Is it possible to retrieve item help text from my own custom database table based via a PLSQL function call or something similar?
I want to pass english character as a parameter and search a string that having swedish character, this needs to be done for all the swedish characters.
test _t table having the below 3 values
päiväp metervara flerfärgad
1. If user searches based on english char like below then they should get all the 3 values bcz fist & last have swedish ä and second one having english a.
Query : select name from test_t where name like '%a%'
Regsult: päiväp metervara fräg
2. If user searches based on swedish char like below then they should get only 2 values bcz fist & last have swedish ä and second one having english a.
select name from test_t where name like '%ä%'
päiväp fräg
Is any in-built function available, for Text alternative string search. If not how to search the string based on the query.
I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.
I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.
I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause) LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
Is what I am asking possible in a View or Materialized View?
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30); insert into temp_acronyms values(1, 'ABC'); insert into temp_acronyms values(2, 'DEC//NOFO'); insert into temp_acronyms values(3, 'CBK//FO TO USA'); insert into temp_acronyms values(4, 'DEC//NO ENTRY'); insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM --- -------- 1 ABC 2 DEC//NOFO 3 CBK//FO TO USA 4 DEC//NO ENTRY 5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.
For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.
I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.
Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.
I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0
I have select list which has null and other values. I have created a blank HTML region with the following text
Report for the year &P2_YEAR. and for geography &P2_GEOG.
Now P2_YEAR works fine because, it is always populated with a year. However, &P2_GEOG has 'All' as null value since we can see the report for all geographies. However, if the all option is selected, the above &P2_GEOG. shows as null. Since its not a PL/SQL proc, I cannot put in a NVL. I am guessing one possible way is to write a PL/SQL code before region and then check if its null and then try to populate a new hidden field and show, something like that. Wondering if there is a way to do it directly in the HTML region?
I have a table called table1 and its structure is as follows:
rtrn_id number, entp_abn number, litm_line_item_nbr number country_code varchar 2(10)
I am trying to find retrieve value/row where for a particular rtrn_id , the possibility of having a country code of "CA"(for Canada) and "US" or "HK" (US or Hong Kong respectively) exists.
I framed my query the following way and it does not retrieve any result in Production and I believe there are a few values/rows in the "table1".
The query framed is:
Query 1:
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code FROM table1 where schd_version_yr = 2010 and prog_program_cd = '01' and sched_nbr = '000' and (litm_line_item_nbr = '023' and country_code 'CA') and (litm_line_item_nbr = '016' and country_code = 'CA')
The above query does not retrieve any rows
Where as if I run a query individually it retrieves tons of rows
(Individual query :
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code FROM table1 where schd_version_yr = 2010 and prog_program_cd = '01' and sched_nbr = '000' and (litm_line_item_nbr = '016' and country_code = 'CA')
-----Retrieves tons of rows as the country code = Canada for line item # 16
SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code FROM table1 where schd_version_yr = 2010 and prog_program_cd = '01' and sched_nbr = '000' and (litm_line_item_nbr = '016' and country_code 'CA')
-----Retrieves tons of rows as the country code IS NOT Canada for line item # 23.
There is a possibility that there are rows that have country Code as Canada and not Canada for a particular ID whose line item numbers could be 16, 23.. is there any better way to frame my Query1 to retrieve values/rows?
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
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.
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.
I use a cursor to select records from a database table into a C structure as follows...
{ int iLoop = 0; int iResult = 0;
[Code]....
The otc_multiplier field is NULL. As is the to_date sometimes. However, when I output the records later, the entries where the to_date is NULL come out fine (no value). But the otc_multiplier is getting output as 0.0 using...
// this is output later in another fuction using the following.. sprintf(newRecord, "%.1f",daServiceRecs->itemMultiplier);
If using c structures in this manner, what is the method for ensuring that numeric values are set to NULL when required?
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
