SQL*Loader-Urgent - SQL & PL/SQL

Hi,
Can anyone tell me how to use the 'WHEN' clause in an Sql*Loader
Control File to select particular rows of the same column from
Data File and insert into an Oracle Table.
eg. in SQl we give:
Select empno,ename
From emp
Where ename in('JAMES','KING');
In Control File how do I give this:
Load data infile     'c:emp.csv'
Badfile               'c:emp.bad'
Append
Into Table emp
Fields Terminated by ','
Optionally Enclosed by '"'
When .........
Thanks.
Thiru 

The following is an excerpt from Oracle on-line documentation:
Choosing which Rows to Load
You can choose to load or discard a logical record by using the
WHEN clause to test a condition in the record.
The WHEN clause appears after the table name and is followed by
one or more field conditions.
For example, the following clause indicates that any record with
the value "q" in the fifth column position should be loaded:
WHEN (5) = 'q'
A WHEN clause can contain several comparisons provided each is
preceded by AND. Parentheses are optional, but should be used
for clarity with multiple comparisons joined by AND. For example
WHEN (DEPTNO = '10') AND (JOB = 'SALES')
To evaluate the WHEN clause, SQL*Loader first determines the
values of all the fields in the record. Then the WHEN clause is
evaluated. A row is inserted into the table only if the WHEN
clause is true.

Related

Merge only when new values are not null

Hello all,How would I create merge statements that will not update fields where the new values are not null? I am using a template I created in Excel to generate scripts based on information put into the sheet from other users.  Some times they don't provide values which ends up in the script as a null value or ''. This method is used to generate 10's of thousands of scripts and if there is a way I can prevent the null values from merging over existing values, that would be superb. Thank you in advance
You can add a where clause to your matched update or not matched insert statement.... hth
Can you provide a bit more detail about what you are doing and what you want? It sounds to me that your solution actually lies in your generation of the merge statement, which I assume happens in Excel. If I had to guess at what your current scheme and problem is, it would be that the Excel row contains ID  val1      val2    1     42     (null) and you create a statement for each row, like: merge into yourtable t using  (select 1 as id, 42 as val1, null as val2  from dual) qon (t.id = q.id)when matched then update set val1 = 42, val2=null; And what really want in this case is: merge into yourtable t using  (select 1 as id, 42 as val1, null as val2  from dual) qon (t.id = q.id)when matched then update set val1 = 42; That is, you don't want to update the val2 column. But maybe you want to reject rows that have _any_ null values, in which case you could add a WHERE clause inside the using: merge into yourtable t using  (select 1 as id, 42 as val1, null as val2  from dual  where va1 is not null and val2 is not null) qon (t.id = q.id)when matched then update set val1 = 42, val2=null; However, if that is anything close to what you are doing (based on the 10s of thousands of "scripts" statement) I think you would be much better off loading the Excel data into a staging table and processing the full set of data with a handful of pre-written merge statements which dealt with nulls as required.
 In the above example, In reference 1, I use a concatenate function in Excel that gathers the parts of the scripts and uses key cells for values to complete an entire merge script, including the value in reference 2.  The problem occurs when I get to a section such as reference 4, where the script is still going to be generated, but with the null value of reference 3.  As I mentioned above, this method is used to generate 10's of thousands of scripts very quickly.  However, in the event there is a null value, I would like to catch it before it becomes an issue
It would be good to see your concatenated statement.  Not very clear of reference 2 and reference 3 etc in your statement. In general  oracle has a very good in built function named NVL to handle null values e.g UPDATE EMP  SET ename  = NVL(lv_name, ename)WHERE empid = 100; Basically in this query if the input value lv_name is NULL then table will be updated with the same value (ename) else ename column will be updated with the value of lv_name variable.  RegardsArun
Concatenated statement is below.  The value of 5126 is what is sometime empty; so just '' merge into table pd using ( select profile_id, property_id, important_id, '5126' value from db.profiles, db.properties, db.important                                       where profile_id = 398                                       and property_id = 10001                                       and important_id = 78783) new on (pd.profile_id = new.profile_id and pd.property_id = new.property_id                                                                                          and pd.important_id = new.important_id)                                                                                          when matched then   update set value = new.value                                                                                          when not matched then   insert     (profile_id, property_id, important_id, value)                                                                                          values (new.profile_id, new.property_id, new.important_id, new.value); I like idea of using NVL though, from my understanding of it, I would need to have the existing values of every record I would be updating.  It isn't unheard of for this method to have over 100,000 scripts and these changes are being made almost weekly.  Is it possible to nest a select statement into the NVL statement that would grab the existing value and pass it into the NVL statement?  I realize this is in NO WAY an optimal solution but it is an idea. I have been considering just building an app that will take these spreadsheets and build out the scripts on it's own.  That would be able to check for null values and not write scripts where they don't need to be.
It is still unclear what you are doing, though continuing to look like you going about it completely back asswards. What are db.profiles, db.properties and db.important? Are they three different tables? If you  have those three values in your excel sheet that is producing the "script", which does look like it produces oodles of single row merge statements, then why do you have to retrieve them from a table, why not just "select 398 property_id,10001 property_id,78783 important_id,5126 value from dual" as your source data? Your whole scheme is akin to trying to build a sandcastle one grain at a time, wrapping each one up carefully to transfer it from the beach onto your castle and unwrapping each one, but ignoring that... If you have a null "value" I understand you do not want to update the row if it exists, but would you insert a row with a null "value" if the key values did not yet exist in pd? If not, then, in excel all you need to do is not produce any statement at all for lines where value is null. If you want to insert a row with a null value, but not update it, then add a "where new.value is not null" qualifier to the WHEN MATCHED section. I don't understand why you think you need to know anything about the existing values in the pd table. I thought i was when the new value was null that you had a problem?
Hello JonWat,db.profiles, db.properties and db.important are dynamic values (columns in the same table) that change throughout the spreadsheet.  You are seriously underestimating the size of this undertaking.  If it were so easy as what you suggest, that would be how it is done.  However, due to those values not being static throughout; that is not a viable solution.  That would result in hand coding potentially thousands of unique merge statements due to the number of different combinations.  Thus, using the excel sheet to do it the way that I am. "If you have a null "value" I understand you do not want to update the row if it exists, but would you insert a row with a null "value" if the key values did not yet exist in pd? If not, then, in excel all you need to do is not produce any statement at all for lines where value is null."          I am trying to eliminate the amount of user interaction to get this to work.  What you are suggesting is the current method that I am trying to improve upon.  I      currently seek out the null values and and don't use the scripts that have them.  It is time consuming which is the opposite of ideal. "I don't understand why you think you need to know anything about the existing values in the pd table. I thought i was when the new value was null that you had a problem?"     I don't need to know anything about the existing values in the table.  At all.  You are correct in saying that I just don't want to update the existing value when      new.value is null. <- that's the entire point.  Is there a way to have a merge statement that knows it is being passed a null value and have it just do nothing?
I just don't want to update the existing value when new.value is null
 Arun already told you how to to it
SET ename  = NVL(lv_name, ename)
whenever your value (in this case lv_name) is null then keep the existing value. You simply have to wrap a NVL around each of your columns WHEN MATCHED THEN UPDATESET col1 = NVL(new.col_1,col1)   ,col2 = NVL(new.col_2,col2) RegardsMarcus
You are seriously underestimating the size of this undertaking.I think you only have a problem with size because of the way you are approaching the problem.I think if you let the database handle the data (which it is good at) you will find the "size" to be irrelevant. What I think you are doing is this: You have a spreadsheet which has four values in four columns. Three of them (profile_id, property_id and important_id) identify the record, and a fourth column called value. For each row, you are constructing (using concatenation in excel) a merge statement that will update or insert a single row in an existing table (pd) in the database. You have a thousand rows in the excel table, you have a thousand merge statements. What I am suggesting is: 1. You load your four excel columns, (all ten thousand rows) into a staging table in the database, let's call it STAGE. Easy to do, for example, using SQL Developer. Or, if your excel sheet is on the server, you could maybe treat it as an external table.2. You execute a single MERGE statement which deals with the whole table at once. It would look like: merge into table pd using      ( select profile_id, property_id, important_id,value      from STAGE   ) new on (pd.profile_id = new.profile_id and pd.property_id = new.property_id and pd.important_id = new.important_id)when matched then   update set value = nvl(new.value,pd.value)when not matched then insert (profile_id, property_id, important_id, value) values (new.profile_id, new.property_id, new.important_id, new.value); It deals with all the rows in the stage table at once. The bolded bit is how Arun and Marwim have shown that you use the existing value in the table when the new value is NULL. If you have any other criteria that would make you decide not to use a row, you can add them to the select query in thee USING clause. I'll point out that you didn't really answer any of the questions I asked: What are db.profiles, db.properties and db.important? Are they three different tables? Your answer: db.profiles, db.properties and db.important are dynamic values (columns in the same table) that change throughout the spreadsheet.So, is the spreadsheet being treated as an external table (called db)? My point still stands: why would you write something like:select nbr from nbrslessthan1000where nbr = 1instead ofselect 1 from dual? Why does it have to look in a table when you already have the value you want in that particular row?I asked: "If you have a null "value" I understand you do not want to update the row if it exists, but would you insert a row with a null "value" if the key values did not yet exist in pd? I think you answered that you would eliminate scripts from all rows where value is null, regardless of whether they might be inserted as new rows or update existing ones. In that case, why would you not wrap the excel formula you already have in something like IF(istext(<valuecell),<your concatenation to make the script>,""), so you just get a blank line for that row? (or isnumber(), whatever makes sense for the value column (you treated it as a character in your merge statement)

small problem  in updating values

Structure of a table :
Column Type
name name Length Scale Nulls
EMPNO INTEGER 4 0 Yes
ENAME CHARACTER 20 0 Yes
CNTY_VISITED CHARACTER 100 0 Yes
I want to insert a row in the table such that the data in the table shud look as :
EMPNO ENAME CNTY_VISITED
1001 JACK 'IND','JPN'
I guess you understood the req. It should not be IND,JPN.. It should be ' IND ' , ' JPN '
in the table. 
duplicate the ' in the insert like that
insert into demo
(n,x)
values (99,'''a''')
select * from demo
N X
--------- -----
99 'a' 
just write
INSERT INTO <tablename> VALUES (100,abc,'IND,JAPAN'); 
The answers are not upto my requirement. 
then explain more details of your requirement, and perhaps someone could help you. 
Hi,
try this
--not tested
INSERT INTO <tablename> VALUES (100,'abc',''''IND'''',''''JAP'''');
thanks 
I want to insert a row in the table such that the data
in the table shud look as :
EMPNO ENAME CNTY_VISITED
1001 JACK 'IND','JPN'That's a terrible design to concatenate country codes in a field like that. Queries to retrieve individual countries will be needlessly complex and expensive.
So my first suggestion is to use a normalized data model, something like this:
EMP(empno,ename)
COUNTRIES (code,name)
COUNTY_VISITS(empno,country_code), where both columns are foreign keys to the previously named tables.
If you decide to ignore this advice, you have to insert like this:
insert into ... values (....,'''IND'',''JPN''')Note: they are all single quotes.
Regards,
Rob. 
Hi Rob
Thanks a lot for the reply.
The syntax you sent is working and displays the output as required.
insert into ... values (....,'''IND'',''JPN''')
but its not the same with other special operators like * and ,
any special conditions to be followed for these operators to get
the output in the format IND,*JAP*. 
> but its not the same with other special operators like * and ,
any special conditions to be followed for these operators to get
the output in the format IND,*JAP*.
The asterisk (*) and comma (,) are just regular characters. Only because a quote (') is also used to mark the beginning and the end of a string, we have to do something different to say "this is a quote and not the end of the string". This is done by using two subsequent quotes. So:
'''IND'',''JPN''')should be read as "beginning of the string" followed by a quote, the text IND, a quote, a comma, a quote, the text JPN, a quote and a marker for the end of the string. Where each "a quote" is written by two quotes inside the string.
Hope this helps.
Regards,
Rob.

blank records

I have one select statement where i need to exclude customer name is empty in the column.
but when i say in the where clause ' where cust_full_name is null' then its not displaying.
how i can write select for excluding records with empty cust_name ?
regards
Aj 
I have one select statement where i need to exclude
customer name is empty in the column.So, you want to display all names that DO include a name, right ?
but when i say in the where clause ' where
cust_full_name is null' then its not displaying.assuming that you typed
select * from tablename where cust_full_name is null;
that way you would display only the ones with nullrecords.
so, what is it that you want exactly? 
Since blank is not null, blank is space(s), you can use trim against the column before testing the nullable of that one.
Nicolas.

Using subquery in a insert statement.

Hello All,
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual)
but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries
for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
Please explain if my assumptions are correct or not. Also please clarify. 
1* insert into dates(date_col) values ((select sysdate from dual))
SQL> /
1 rij is aangemaakt.
Doesn't work?
Please notice your syntax error!
--------
Sybrand Bakker
Senior Oracle DBA 
Hi,
Welcome to the forum!
pragatimanit#gmail.com wrote:
Hello All,
I read in a book that you can't use subquery in an insert statement . E.g:Post a quote from the book, or a reference to it if the passage is online.
You probably mistook something from the context, but it's hard to explain what your source meant without knowing what your source said.
1)insert into dates (date_col) values (select sysdate fom dual)
but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries
for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
Please explain if my assumptions are correct or not. Also please clarify.There are two typesor forms of INSERT statements:
(1) statements that INSERT a single row, where the values are listed.
The basic syntax of this form is
INSERT INTO table_name (column_1, column_2, ...) VALUES (value_1, value_2, ...);This form always has the keyword VALUES, and the list of values is enclosed in parentheses. (Of course, some of the values themselves may be expressions involving additional parentheses, such as "1 + (1 / 24)".)
(2) statements that INSERT the result set of a query, which can be any number of rows.
The basic syntax of this form is
INSERT INTO table_name (column_1, column_2, ...) SELECT  value_1, value_2, ... FROM ...;This form never has the keyword VALUES, and the list of values is not enclosed in parentheses. (Of course, some of the values themselves may be expressions involving parentheses, such as "column_x + (1 / 24)".)
Both of the INSERT statements you posted are in form (1): both of them have the keyword VALUES.
In either form, any of value_1, value_2, ... may be a Scalar Sub-Query , that is, a query enclosed in parentheses that returns exactly one column. The parentheses that surround the scalar sub-query have no other purpose than to mark it as a scalar sub-query.
Your first statement is missing a set of parentheses, as Sybrand pointed out. If you're using the keyword VALUES, then there must be a set of parentheses whose only function is to enclose the list of values. That list include a scalar sub-query, but if it does, the scalar sub-query must be enclosed in another set of parentheses. 
Hi Guys,
thnks a lot!
ya it was a syntax error.
i understood the difference in both the insert statements used.

problem whit multi value in query

hi
i need create a query to send do statement very strange.
i have in input a stream of result
es.(value from 'BIN' to 'FIN') and i need create a query take all element between this 2 element
es. if in input i have ' A' and 'C' i seacrh in table all record:
select * from TABLE where FIELD like 'A%' or FIELD like 'B%' or FIELD like 'C';
exist 1 command in sql for this query(remember i not know parametrer IN
or exist 1 class or method create this string to insert into query?
pls help me tanks
null 
SQL supports less than and greater than comparison of strings.
The select statement
select * from TABLE where FIELD >= 'A' and FIELD <='C' should do the same like the one you have posted. nevertheless I am not sure, that this is what you wanted.
The selects will also return the rows where FIELD is either 'A' or 'C' . Those rows are strictly spoken not between the two values ('A' and 'C') .
If you want case independency look for upper or lower case coversion in the syntax of the database you are using.

Categories

Resources