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

SQL loader group by value if duplicate recordes found

Hi
I want to load the data from a text value, the field values are delimited by pipe line. But in my text value i have duplicate records, and i want to add the value of amount if some duplicate record is there. for example:
prod_id|security_id|amount|date
12|AB|130|20091231
12|Ab|40|20091231
where prod_id and security_id combined is the primary key in my target table. I want insert a single record in the target table like flollowing:
prod_id security_id amount idate
12 AB 170 20091231
How would i write in control file for that. Following are my loader parameters I am using
OPTIONS (SKIP=1)
LOAD DATA
INFILE *
INTO TABLE my_table
REPLACE
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
prod_id position(1:2) "to_number(:prod_id)",
security_id position(4:5) "trim(:security_id )",
amount position(6:10) "to_number(:amount )",
idate position(12:20) "trim(:idate)"
)
Can anyone help me how would i group by amount values
Thanks
shaveta 
You wouldn't write a control file as this is not SQLloader functionality. SQLloader cannot aggregate data, as documented (as all documentation is comprehensive, what is not documented, is not there).
You need a staging table, allowing alll junk in the file to be processed, and SQL code to aggregate your data.
It is also bad practice to directly load into a target production table.
------------
Sybrand Bakker,
Senior Oracle DBA 
user10996835 wrote:
Hi
I want to load the data from a text value, the field values are delimited by pipe line. But in my text value i have duplicate records, and i want to add the value of amount if some duplicate record is there. for example:
prod_id|security_id|amount|date
12|AB|130|20091231
12|Ab|40|20091231
where prod_id and security_id combined is the primary key in my target table. I want insert a single record in the target table like flollowing:If your table has a PK defined on what you don't want duplicate, then you're already safe, the 2nd loaded row will be rejected by the PK.
Nicolas.

saving duplicated records into a new table

hi all,
I have a table with duplicate rows.
I can remove these rows by using DUPLICATOR in OWB.
But i need to save thoese rows into a new table.
How to proceed for this???
Thank u
SAI 
Point A. Avoid using duplicator, there must be something wrong in your join condition if you are getting duplicate rows. Try to fix that.
And if you are convinced that you will always get duplicate rows then :
1. table A with duplicate rows==>Deduplicator operator==>table B(load type to be set insert/update or whatever) this will insert unique records into your table B.
Now for duplicate records==>
Table A==>insert into Table C ==> procedure which will delete records which have only one row.i.e say if your table has empno then delete all those records where count(empo) = 1, that will leave you with duplicate records in table C 
Use an aggregator after your target table and implement the following logic in it :
select col_a,col_b
from table_src
group by col_a,col_b --> in group by clause of aggregator
having count(1)>1 --> in having clause of aggregator
Feed the outgrp of aggregator in the required table. 
Hi Sai,
Please follow below step for capture duplicate records into a new table.
1.     Drag source table.
2.     Drag expression operator, drag all column into source
3.     In expression operator output attribute one new column and give name to rec_cnt1(note : add all source columns)
Ex: suppose source table have 3 columns, then you can add 3 column in output attribute.
4.     Click rec_cnt1 column and type below syntax
count(*) over ( partition by INGRP1.COL1 , INGRP1.COL2 ……. INGRP1.COLN)
note: you have 3 column give code like count(*) over ( partition by INGRP1.COL1 , INGRP1.COL2, INGRP1.COL3)
5.     And validate , validation results show like
Line 0, Col 0:
     The expression is not properly formed.
     Ignore validate result
6.     Drag SPLITTER operator and drag all source columns and rec_cnt1 (expression operator to SPLITTER) .
7.     Click on SPLITTER OUTGRP1 and give below statement.
     INGRP1.CNT_REC = 1 , this means all unique records.
8.     Click on SPLITTER OUTGRP2 and give below statement.
INGRP1.CNT_REC != 1, this means all duplicate records.
9.     Link OUTGRP1 to target table (tab2), all unique record stores this table.
10.     Link OUTGRP2 to target table (tab3), all duplicate records stores this table.
Your mapping layout like this
source ---> expression operator --> splitter operator -----> tab2 (unique records)
------> tab3 (duplicate records)
Regards,
Venkat

using of trim function in an update statement to remove spaces in a column

Hi
one of the column is paddidng extra spaces on the end of the values in order to attain a fixed lenth field.so in order to remove the extra spaces i decided to go for the trim function usage.But i did not know how to use the trim function in an update statement to remove the spaces in the same table for a specific column
Thanks 
This?
UPDATE table_name
SET column_with_extra_space=TRIM(column_with_extra_space)
WHERE column_name_and_condition 
Hi,
your update could be like this,
UPDATE table name set col1 = TRIM(col1) where ...
Could you please tell what is the datatype of the column that you are trying to update.
thanks 
hi
the datatype pf the column which i want to update is varchar2(20).But is it necessary to mention the where condition when i want to update all the values.
the table has the following fields,
dt date,
id varchar2(20)
upd number,
upd2 number,
ex1 number,
ex2 number
can i used this to update all the values
update tablename set column_name=TRIM(columnname);
Thanks 
Lets say if you want to update al the rows for the table column ex1
then
UPDATE table_name
SET ex1=TRIM(ex1);
Note: This will updated all the rows for this column.
if you want to updated specific rows then you have to put where condition. 
Thanks 
In your original post you have mentioned that you want to remove trailing blanks.
but TRIM function remove blank space from both side of the value.
if you want to remove ONLY trailing blank then use RTRIM function
update tablename set column_name=RTRIM(columnname);

Need Help on Insert Statement

Hi,
I want to insert the data into table dynamically. insert into number of columns depends on number of selected column statements.
Table in which data needs to be enetered is table with generic coulmn.
THanks
--K                                                                                                                                                                                                                                                                                                                                                                                                                                                                
OK...so what's your question? 
Hi,
I think, you just need to select values into variables and use those variables while inserting into other table. If there are no values for certain columns, then NULL will be inserted
Thanks 
for ex. I have a table with generic colums. col1,col2,col3.col4,col5,col6.......................col255
I want the number of column selected while inserting depends upon the select stmt.
like
insert into t1(col1,col2,col3)values
(select a,b,c from t2)
but if if I have select a,b,c,d,e from t2 then
insert into t1(col1,col2,col3,col4).
THanks 
Sounds like you need dynamic sql. Since you'll need to evaluate the select statement you'll need some sort of parser to associate 'a' = 'col1' or even 'a'='1' and just concatenate the number with 'col' when building your dynamic list. Then piece the statement together into a string and execute immediate.

SQL Loader to append data in same table but using differnet WHEN clauses

In my data file i have a header record and a detail record identified by Record_type = 1 and 2 respectively.
The Database table has all the columns to capture detail records but i want to capture jus one column of header record now also in my existing table. So i have added that column (DATA_DATE)in my table but how to capture that value ?
im writing my control file using two WHEN clauses, something like -
load data
into table t_bdn
append
when RECORD_TYPE = '2'
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
SEQUENCE_NO
, RECORD_TYPE
, DISTRIBUTOR_CODE
, SUPPLIER_CODE
, SUPPLIER_DISTRIBUTOR_CODE
, DISTRIBUTOR_SKU
, SUPPLIER_SKU
)
when RECORD_TYPE = '1'
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
SEQUENCE_NO FILLER
, RECORD_TYPE FILLER
, CREATE_DATE FILLER
, DATA_DATE "NVL(to_date(:DATA_DATE, 'YYYY/MM/DD'),to_date('9999/12/31', 'YYYY/MM/DD'))"
)
im getting error " expecting INTO and foung WHEN RECORD_TYPE = '1' "
if i give iNTO second time it will append a new row altogether in my table but i want the same row to be updated with this DATA_DATE value coming from RECORD_TYPE =1 and header record has 4 delimited data text fields only and i am interested in fetching just the 4th column..
KIndly suggest what to do ? 
Ravneek, I could be wrong but sqlldr is a 'load' program, that is, it inserts data. I am unaware of any ability to update existing rows as you seem to want. What you appear to want to do is more the job of a merge statement.
I would look at writing a pro* language, a .net, or a java program to perform inserts where some or all of the newly inserted rows are also to be updated.
From the manual: (Oracle® Database Utilities 10g Release 2 (10.2) Part Number B14215-01)
Updating Existing Rows
The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
1. Load your data into a work table.
2. Use the SQL language UPDATE statement with correlated subqueries.
3. Drop the work table.
HTH -- Mark D Powell -- 
Hi,
Thanks for your response. And There seems to be no way out to append in same table using two different when clause, according to my requiremnet
So, i have created a new temp table and m doing the second append into that table using the oher WHEN clause. And then In my procedure at back end, im updating that column value from this table. using plsql techniue.

Categories

Resources