PL/SQL :: Have A Function In Oracle To Select Not Null Columns At Beginning
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.
Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 13 Replies
ADVERTISEMENT
Jul 12, 2012
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
View 8 Replies
View Related
Sep 19, 2006
I have a table containing hundreds of columns and I would like to be able to qualify my select statements so that only those columns containing a value are returned. Something like:
Select (non null columns) from tablename where columnX = 'whatever'
View 7 Replies
View Related
Jan 25, 2013
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
View 2 Replies
View Related
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 Replies
View Related
Jul 20, 2011
I am trying to run a dynamic select statement form a function and return the result into a variable, everything goes fine but the return is always null!
CREATE TABLE AFESD.MAJOR_ACCOUNT
(
NUMBER0 NUMBER(2) NOT NULL,
SHORT_NAME CHAR(35 BYTE) NOT NULL,
FULL_NAME CHAR(50 BYTE)
)
--Actually any table can do
CREATE OR REPLACE FUNCTION F_GEN_SELECT_INT
(S_APP_USER IN VARCHAR2, I_MODULE_ID IN NUMBER, S_TABLE IN VARCHAR2, S_COLUMNS IN VARCHAR2)
RETURN NUMBER
AS
I_RETURN NUMBER;
S_SQL VARCHAR2(300);
--S_DB_ERROR VARCHAR2(100);
[code]....
B.S. I didnt delete the commented lines to have your review comments.
View 21 Replies
View Related
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Jan 21, 2011
I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
View 5 Replies
View Related
Jul 3, 2012
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?
View 5 Replies
View Related
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
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Sep 3, 2010
i am getting following error while trying to insert value into columns of table.
ORA-01400: cannot insert NULL into ("demo"."col1"."col2")
View 2 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Nov 10, 2010
I am experiencing somewhat same issue...but have been unable to resolve it(new to Oracle) I am getting the infile from flat file(data dump from SQL) using sqlldr to upload data to the Oracle table...since the data is already in the flat file...I cannot do anything in the SQL to pre-format the data...
Sample of ERROR I am getting - Column CREATE_DATE which has date and time - happens to other date time columns also if remove the CREATE_DATE from Control file(happens to every single line of record):
==========================================
Record 2: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
[code]...
Flat file: (3 lines of data)
5|Annie|1|AR|84601D0A-6D9D-4D0F-86EB-2FDD9D7E680B|0|0|1|1|1|0|1|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Annie|VAXP60|2008-10-03 16:54:59.583|2008-10-03 16:54:59.583
11|Beverly|1|BA|9A2D6304-E997-4B40-96E5-2221E521B077|1|0|0|1|0|0|0|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|BEVERLY|VAXP60|2008-10-03 09:39:33.973|2008-10-03 09:39:33.973
29|KGarbin|1|KG|B229FCF9-BED0-4E50-9804-83324B677C67|0|0|1|1|1|1|0|1|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Gfoote|VAXP60|2008-09-08 10:05:01.690|2008-09-08 10:05:01.690
View 1 Replies
View Related
Mar 17, 2009
i have a function which takes in two variables and return a varchar.
ex: Function(var1,var2) return as varchar2.
in the function,i query a table for var1 and var2 and concatenate the result set to return a varchar. But if either var1 or var2 is null,then my query in the function fetches the result set for the other variable.
My question is,how would i pass a null value through the function and handle it in the function.
View 1 Replies
View Related
Feb 18, 2011
I have a sql queries in that i saw one sql querie like this
select columnname,null columnname from tablename
what is the meaning of this which i marked with red (null columanmea)
View 2 Replies
View Related
Sep 25, 2012
I regularly upload various tab-delimited text data files with SQL Loader. The control files always specify TERMINATED BY X'09'. Certain columns in those data files may be null for some rows, i.e. there is no character between two subsequent tabs. It always works like a clock.
Now, I have run into a specific case where I have to strip a data column of double quotes that may or may not enclose the actual data (side effect of a text export from Excel).
I tried simply adding OPTIONALLY ENCLOSED BY '"' behind the terminated delimiter and it did work for the file and column in question. Still, with this new option, files with null values are no longer decoded correctly. The loader seems to simply skip those values, which provokes column shifts and results in either a corrupt load, or a load failure.
For now, I've got a workaround dropping the new option and executing an SQL script eliminating double quotes directly in the database, but that obviously cannot last.
View 3 Replies
View Related
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
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
Feb 2, 2011
My Database is in Oracle 11g.
My query returns the 3rd field from a CSV string. If the third field in the string is empty I want the select to return a null but it returns the 4th field :
SELECT REGEXP_SUBSTR( 'A,B,,D,E','[^,]+',1,3) from dual;
.. this returns 'D'.
Can we somehow make the REGEX_SUBSTR return a NULL for the third field ?
View 10 Replies
View Related
Oct 20, 2011
I got a requirement to check if a particular range of values are present , if few values missing then have to add null for the values. This has to be a SQL statement.
table 1 :
fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25
2011020026382681.4
2011030030992751
2011040027662753.14
2011050029732780.625
This is the table. the fiscal month id denotes that the values is from month Oct(20101000) to May (20110500). The fiscal year cycle for me is from OCT to SEP. Hence when we select this table, the select should retreive the below data
fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25
[code].....
How to do this in a select statement ?
This fiscal_month_ID is actual from another table where all time level information are kept.
View 4 Replies
View Related
Feb 29, 2012
I am using Oracle SQL developer 3.0.0.4. I have tables starting with @. For eg @45412_USERS
I am getting the below error when i run select * from @45412_USERS;
It says ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:
Error at Line: 7 Column: 14
I also tried with select * from <Owner>.@45412_USERS this also didnt work.
View 4 Replies
View Related
Sep 22, 2010
Trying to get a number out of an error backtrack
06512: at "SCHEMA.PROCEDURE", line 4
I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.
The database languages might differ so I can't do this using
v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)
As it would not find any 'line' inside the string.
View 7 Replies
View Related
Jun 15, 2010
I'm working on a Oracle Database, and I'm gettin incorrect results when including a date field in the select list which is NULL in the table.
This works correctly and returns exactly one row:
SELECT firstField FROM table WHERE firstField = 'value'
while this doesn't and returns no rows:
SELECT firstField, secondField FROM table WHERE firstField = 'value'
Where secondField is of type date and its value is NULL (00-000-00). Note that the only thing that changes is the select list.
View 6 Replies
View Related
May 3, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
If a row looks like this:
EID,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,DATE13
68,NULL,11/10/2009,5/3/2010,4/22/2012,NULL,NULL,4/14/2010,NULL,NULL,NULL,NULL,4/14/2010,4/14/2010
The the result set would look like this:
EID,MaxDate,Within_Last_3_Years
68,4/22/2012,'YES'
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related
Sep 17, 2010
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
View 3 Replies
View Related
Jan 31, 2013
In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query
SELECT * from TABLE(SPLIT('bbb003,bb004'));
out put is
bbb003
bb004
now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.
SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));
View 7 Replies
View Related
Aug 15, 2013
I have a table which stores Employees and their Phone numbers. Each employee can have multiple numbers e.g.
Employee, Number
Adam, 123
Adam, 456
John, 123
John, 456
I am trying to write a select statement that will split the rows into columns and group by each employee e.g.
Employee, Number1, Number 2
Adam, 123, 456
John, 123, 456
View 19 Replies
View Related
Aug 16, 2013
I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
View 1 Replies
View Related