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.

View 9 Replies


ADVERTISEMENT

Forms :: Filter Hard Coded Values In List Item (Tlist) Based On Value Entered In Text Item

May 22, 2010

I have 2 items in my form:

1) Text Item
2) Tlist

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 .

View 1 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

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

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

Forms :: Retrieve Value From Database And Then Print In Display Item?

Jun 29, 2013

i want to retrieve a value from the database and then print in a display item. how can i do it?

this code is the retrieval part but it doesn't work:

DECLARE
num number(10);
BEGIN
SELECT ID_CD
INTO num
FROM cd_details
WHERE price=599;
end;

View 7 Replies View Related

Application Express :: How To Retrieve Item Help Text From Database

May 23, 2013

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?

View 4 Replies View Related

SQL & PL/SQL :: Search Swedish / Danish / German Char Using English Char

Oct 27, 2010

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.

View 6 Replies View Related

SQL & PL/SQL :: View Of Item Hierarchy By Eliminating NULL Levels

Aug 22, 2013

Here is the table structure I will be speaking of below:

SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;

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?

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

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.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

View 6 Replies View Related

PL/SQL :: How To Compare Two Rows And Retrieve The Values

Aug 15, 2012

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

View 16 Replies View Related

Application Express :: Possible To Display Null Item Value As All In Blank HTML Region

Jul 2, 2012

Apex 4.1
Theme 21

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?

View 0 Replies View Related

Forms :: Search Criteria - Retrieve Values In 9i?

Jul 19, 2010

I would like to know how can I retrieve both values below in rms forms 9i.

Both values pertains to one id.
id - 00012345 (with zeroes)
id - 12345 (w/out zeroes)

The id is use as one of earch criteria.

View 3 Replies View Related

SQL & PL/SQL :: How To Retrieve Values If User Entered In Mixed Case

Apr 23, 2008

how to retrieve values if user entered in Mixed case means (case insesitive)?

We don't have control when user enters values and it might be SMITH or Smith or smith or SmIth.

How can write the query to get this kind of mixed case data?

View -1 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Multiple Values / Possibility From A Table?

Jun 1, 2010

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?

View -1 Replies View Related

Forms :: Hierarchical Tree - Retrieve Values And Insert Into Table

Apr 19, 2010

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.

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

Selection Criteria Char Between Char

Jul 22, 2009

I have a table with column val1 having data's starting with 0920 and 4 digit char values.

so we have the query to find

select * from table
where val1 between '0920' and 'ZZZZ'

will it work fine?

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

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

Precompilers, OCI & OCCI :: NULL Values In C Structures?

May 18, 2010

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?

View 2 Replies View Related

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







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