Oracle sqlldr when clause question

I have a fixed record formatted file originating on a Tur64 box and being sql loaded into a 9.2.0.3 instance on another Tru64 box. I would like to apply the when clause to restrict the row loading based on the value of an integer(4) column, but can not get it to work. I have tried many variations of ...

Oracle sqlldr WHEN clause with function call ?

I have a requirement to load data from a file with each row being checked for good data.But that check is wrapped inside a function, as it is somewhat complex.So I woul like to load the data with a WHEN clause like thisWHEN ( "CHECK_IF_GOOD(:field1, :field2,.. )" = '1' )But sqlldr compains that colu ...

Oracle SQLLDR when clause with numeric values.

Im loading some data and I want to strip values in a column that are 0 (zero)I'm using when rel_conc != '0' but it's not working. It runs through but all the 0 values are imported. I think it's interpreting the number wrong. In the log file it has -Table SITE.DMS_AM_NRT, loaded when REL_CONC != 0X30 ...

referencing value in where clause

is it possible to reference a value in a where clause?example:select * from staff where salary > 5000; 'staff' is a view and depending on the comparison value (here 5000) which is placed in the above select statement I would need to calculate another value. At the moment I solved the problem by a ...

Character to Integer conversion in where clause

How to convert a character column to integer in the where clause.Example:select * from tabname where int(colname) between 1 and 50;In the above example the int(colname) is not working but it works if specified as select int(colname) from tabname. ...

multiple values for single column in where clause

how does one specify multiple values for a single column in a where clause?example:SELECT fname, lname FROM tblContacts WHERE (state = 'MI','CA','AZ','TN','NJ') if my memory serves me there is an IN() value list operator but I can't remember the syntax ...

Using Parms with IN clause

Is there a way to use a parm in the 'IN' of a where clause?Example (integerValue is an int datatype):declare @list as varchar(max)set @list = '1, 2, 3, 4'select *from table1where integerValue IN ( @list ) ...

Clarification on RETURNING Clause usage

I am trying to get an equivalent for SQL Server's OUTPUT clause in Oracle .I believe RETURNING Clause will best suits for it .By using RETURNING Clause ,Is it possible to return the inserted values to a database table.Quote: In SQL Server it is possible by using OUTPUT clause .SQL SERVER – 200 ...

IDA 7.5.2: omitting table phys. attrib/storage clause generating Oracle DDL

I'm looking for the possibility to omit the physical attribs/storage clause in generating DDL for a Oracle 10g tabel.Clearing the storage parameters for a certain table doesn't do the trick, it only expands the storage clause:Example:creating a table without specifying ANYTHING but the tablename TES ...

OpenForm with Where clause fails

When opening a form using the Openform statement and Where clause in VB the form opens apparently ignoring the Where Clause.Example;DoCmd.OpenForm "frmCustomers", , , "Customer_ID = " & 99The form will open and display all the records in the Customers table, not just those wi ...

Openform and Where Clause is ignored

When opening a form using the Openform statement and Where clause in VB the form opens apparently ignoring the Where Clause.Example;DoCmd.OpenForm "frmCustomers", , , "Customer_ID = " & 99The form will open and display all the records in the Customers table, not just those wi ...

Openform - Where clause is ignored

When opening a form using the Openform statement and Where clause in VB the form opens apparently ignoring the Where Clause.Example;DoCmd.OpenForm "frmCustomers", , , "Customer_ID = " & 99The form will open and display all the records in the Customers table, not just those wi ...

Oracle IN Clause

Hi, I am trying to pass in a string of ids into an IN clause using a parameter. Example of the IDS are ABCD10, EFGH40, QRST80 , etc. The user will select multiple IDS from a drop down list in our web interphase. These IDs are then stored in a temp table. In my PL/SQL code I call a function GET_STRIN ...

Having problems building a select list with an aggregate and Group by Clause

Having problems building a select list with an aggregate and Group by ClauseUsing VWD and mssql server express 2008Below I have listed two conditions one that works and one that does not. I have named them example 1 and 2Example 1: A working Select List----------------------------------------------- ...

Oracle code to update a table using sqlldr

can anybody give the code to update a table using sqlldr with an example ...

Oracle variables in contains clause?

I was wondering if you could use two variables in a contains clause? Example:(where var1 = name, var2 = Mary)select * from table1 where contains (:var1, ''''|| :var2 ||'''');I have played around with the quotes and I continue to get errors like "Attempt to set a parameter name that does not occur in ...

Oracle is there a way to use dynamic built string in the "from" clause

all, im having one problem and now, im not sure how to solve it easily at all... Is there someone that would be so kind and put a eye on it? ..thxI have plsql proc, in which i have a list of table_names. For each of that table i need to run a query that will retrieve me a list of values and for each ...

Oracle Order data as in IN clause

Is there a way to order the result set to display in the same order as data supplied in IN clauseexample: select * from t_cu where cno IN ( 220, 120, 900, 350, 99, 1, 34 );   I would like to display the data in the same order as 220, 120, 900, 350, 99, 1, 34 I know this can be done using UNION ...

Oracle evaluation of condition clause in whehe clause using case statement

I have a scenario where in I have to evaluate two different conditions based on the parameter value. Is it possible to evaluate the conditiond based on the parameter value with case statement in where clause.Example:select A,B,C,D from X,Y,Zwherecond1 andcond2 andcase when param='T' then cond3 else ...

MySQL Limit on matching results only?

Is there a way to do a limit with a starting and ending value that works like a: LIMIT 10, 20 only it pulls the 10 resulting rows that matched the where clause of the query vs. just showing the 10 rows regardless of the where clause? Example: select product_name from products where product_count ...

Oracle Passing Parameter for sorting in a stored proc

Hi, Can any one help me how can I pass a parameter(column name) for a stored procedure to use in order by clause.example:PROCEDURE GET_emp_SP(dept IN number,ename in varchar2, PCUR OUT ENG_CUR)ISBEGINOPEN PCUR FORSELECT * from emp_tbl where dept = dept order by ename;end get_emp_sp;Here in the above ...

MySQL subquery-restrictions.html

on http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html it relies ============================================================== .... Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example: UPDATE t ... WHERE co ...

Interactive SQL works/JDBC not working [alias/order by]

I am having trouble with SQL that works using interactive or Perl/PHP interfaces, but fails using JDBC. This seems isolated to queries with ORDER BY in them. If I use an alias for a column name or a LEFT JOIN with ORDER BY I get an error;I have tried both the following drivers, org.gjt.mm.mysql.Driv ...

Add WHERE to query

I'm having a bit of a nightmare and was hoping for some help please :) I currently have a simple form with list/menu and options that determine how records in a table are ordered by: <form action="view_food.php" method="get" name="reorder" id="reorder"> ...

Openform where argument fails

When opening a form using the Openform statement in VB and using the where argument, the form opens and apparently ignores the Where clause.Example;DoCmd.OpenForm "frmCustomers", , , "Customer_ID = " & 99,The form will open and display all the records in the table Customers, ...

Using "Like" as a Variable in an SQL Statment

In the example below I set a variable for m_dealer and m_optIn my SQL statement below I am finding all the dealers that are "Like" m_dealer, and as coded it works fine.m_dealer = "990201" m_opt = "Like"SQL = "SELECT * INTO tmp_r " & _"FROM D_RULES ...

Oracle Declare Cursor with Variable

How do I declare a cursor using a variable for the SELECT clause? Example: the variable "tabl" contains the value of the table I wish to declare a cursor upon. The following code will not work (invalid cursor error):declare cursor cursor_table is SELECT * from tabl;The reason I am using the variable ...

MYSQL and WHERE

Is there a way to pull multiple records using where clause.. EXAMPLE::SELECT * FROM name WHERE age=12, age=35, age =15Or do I have to use seperate select for each type or record i wanna pulll... ...

2 quick ?'s about redirecting

1. is header the only way in php to *redirect (i guess is the word) to a new page with no vars in memory.2. i know headers wont work in the middle of a php doc. so how exactly do i make em work in a if then clause? example:IF form filled out {header ('gohere.php'); }ELSE{post form again}anyone got s ...

Oracle Optimizer

I have following problem:I have table with 40 attributes and it has about 300.000 rows. I would like to select my table with WHERE and ORDER BY clause. And I would like to fetch only few rows. My select is following:select*frommy_tablewhere center_id = 1and state_id = 2order by purchase_date descThe ...

auto updating a table field

I wanted to execute the query stored in a table with given intervals.I have a table called "calculations". it has the following structure:id | period | sql | result |now, in the sql field, I store the sql clause to be executed. In the period field, there is time interval for the query to r ...

Oracle Simple Question: How to search for a date value in SELECT

Probabily a simple question, but in a SELECT statement, how do you do a search for a date value in the WHERE clause?example: Select * From ExampleWhere date = 01/01/2001I know its not as simple as that, but what change has to occur to make it do what that example implies? ...

Oracle type table of type object... bulk collect. Its possible?

Hi, I have an structure:create type permission is object (id number);create type permissionList is table of permission;create type user_type is object (id number, name varchar2(10), permissions permissionList);how to populated this type in SQL clause????example:declareut user_type;beginselect user.i ...

Index Usage from SQL query in Oracle Forms

Would using LIKE/OR in where clause (of an indexed column) will force the query to NOT use INDEX. We have these where clause in Oracle Forms Records Group. Below are two examples...1. If we have a where clause with LIKE would that NOT use the index? Example: ColumnName like :block.Column||% 2. How a ...

Index Usage from SQL query in Oracle Forms

Using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX? We have these where clause in Oracle Forms Records Group. Below are two examples...1. If we have a where clause with LIKE would that NOT use the index? Example: ColumnName like :block.Column||% 2. How abo ...

Index Usage from SQL query in Oracle Forms

Using LIKE/OR in where clause (of an indexed column) will force the the query to NOT use INDEX? We have these where clause in Oracle Forms Records Group. Below are two examples...1. If we have a where clause with LIKE would that NOT use the index? Example: ColumnName like :block.Column||% 2. How abo ...

Oracle WHERE vs JOIN which is the best

I learned to make join between emp table and dept table using the where clause.Example :select e.ename,d.dnamefrom emp e, dept dwhere e.deptno = d.deptno;One of my friends told me that join using where clause is not good in performance, instead one has to use join clauseexampleselect e.ename,d,dname ...

Oracle query

i am having one query and i want output to be in same sequence mention in where clauseexample.select empno from emp where deptno in (88,422,388,66)output should give me data in order of 88,422,388,66 ...

Oracle Best Way to code parameter search

I am creating a curser for data, but normally I want only so many days of data, but if I want all the records, how do I pass a parameter to skip the rest of the where clause.EXAMPLE:p_refresh_all IN VARCHAR2 DEFAULT NULLCURSOR a_cur ISSELECT m.org_code,m.postcard1_due_dttm ,m.ccard2_due_dttm ,m.ccar ...

Oracle How to Handle Dynamic Pivoting with a single SQL?

I was searching for a single SQL who can dynamically understands the pivoting members in the data, I saw several ways of doing Pivoting depending on the version, some are really hard to understand but just two options upto now seams to be flexable enough to do dynamic pivoting, right?1- For this opt ...

Oracle count(*) VS count(indexed_column_name)

Hi, after a lot of reading here in the forum and the "ask tom" site, I can see that count(1) and count(*) are on the same, count(*) prove to be even faster sometimes. My question is: This is the same for indexed columns declared inside the count clause? Example:select count(*) from Person p, (lots o ...

Oracle SQLLDR - spec for "fields terminated" clause for tab delimited files

Does anyone know what the spec for tab delimited files is for the "fields terminated" clause in SQLLDR? See the below example for a comma delimited file:FIELDS TERMINATED BY "," ...

Oracle SQLLDR and NULL WHEN clause

I can't make my sqlldr control file load only records where the first column is null.The data file is comma delimited and the records I wish to load start with a commai.e.HEADERRECORD,Hello World,,12345.67,and in the above I wish to only load the second record.The control file used:OPTIONS (BINDSIZE ...

Oracle example of loading systimestamp in a timestamp field using sqlldr

can anyone provide me with an working example of loading a default value of systimestamp in a timestamp field using sqlldr - control file ...

Oracle SQLLDR with When clause

Can anybody help me!I am using oracle sqlldr utility and tried to load into two tables, but I wants to load the data if a specific condition is satisfying. For that I am using this WHEN SUBSTR(SOLICIT_ID,INSTR(SOLICIT_ID,'-',1,2)+1,1) = 'T' but it is failing . I was not able to use functions along w ...

Oracle sqlldr control file WHEN clause--how to achieve LIKE '%'

I'm on 10g and trying to exclude some records during a sqlldr call. So I turn to the WHEN clause and find out that is pretty limited. I cannot use functions in it and I cannot use the LIKE operator with it. Here is a sample of what i'm trying to do:LOAD DATAINFILE *INTO TABLE vc_tWHEN account_name l ...

Oracle SQLLDR: Using WHEN clause to check column data

HelloI want to check the data for a column consisting of a semi-colon using the WHEN clause for SQL loader. For example, I have this data "AAAAAA:BB" for the field MY_DESC, I want to check that the string has a semi-colon after the 6th character using substring as shown in the control file before in ...

Oracle How to use WHEN clause in sqlldr

I want to insert RECORDS in table which are having orgno column values greater then 100 in my datafile using sqlldr.In WHEN clause in my control file if i mention WHEN (orgno > '100')it gives me error :Illegal combination of non-alphanumeric charactersWHEN (orgno < ')^I am able to insert recor ...

Oracle example sqlldr for xmltype using external files

From the 9.2.0 document, we should be able to use sqlldr to load xml type, but I tried with the following control file and failed with the attached error message, any working examples? thanks in advance. load dataINFILE 'my.data'INTO TABLE my_tableAPPENDFIELDS TERMINATED BY '\t'(id,ext_fname FILLER ...

Oracle sqlldr - 8i - SEQUENCE clause in column description

I'm having trouble getting a handle on how SEQUENCE works in 8i whenyou're loading multiple tables from each logical record. The documention says a unique sequence number is generated for each logical record, but also describes the COUNT and MAX options as depending onTABLE values - num row in COUNT ...