I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type 123Amount 123Percent 456Amount 789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg: ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10 7 lmn 10 . . . so on... (till some count) Result should be Query 1 should result ---> ID name age 1 abc 10 1 def 20 2 ghi 10 2 jkl 20 2 mno 60 3 pqr 10
query 2 should result --> 4 rst 10 4 tuv 10 5 vwx 10 6 xyz 10 6 hij 10
query 3 should result --> 7 lmn 10 . . 9 .. .. so on..
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%') from v$database; * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
I am dealing with a table containing millions of records. I have table loans_list table and he data looks similar to this..
LOAN_IDSEQUENCE_NUMCOMPLETE_DATE 1237000 1237005 1237010 5237010 6/23/2010 10:07:02 AM 5237000 6/23/2010 10:07:02 AM 12237000
I am trying to select only those loan_id from this table which contain all these 3 sequence_num = 7000,7005,7010 and containing null compelete date. I tried different way to write the query but can't think of efficient way of writing this query yet.
Since this table contain million of records, i dont prefer to call this table more than once in a query. I am just trying to avoid the longer time delay for the execution of this query..
--insert into t1(col1, col2, flag) values(1, 'a', 'Y'); --insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT * FROM t1 x WHERE col1 = 1 AND col2 = 'a' -------------- condition1 AND 0 = -------------- condition2 NVL ( (SELECT COUNT (1) FROM t1 y WHERE y.flag = 'N' AND x.col1 = y.col1 AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
I'd want to select multiple records in my multi-record block. I'd also want to do that with checkboxes. When the user clicks on a particular checkbox, that should be selected and whatever record the user wants to check it should be added to selected records.
I'm updating a large piece of legacy code that does the following type of insert:
INSERT INTO foo_temp (id, varchar2_column) SELECT id, varchar2_column FROM foo;
We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:
INSERT INTO foo_temp (id, clob_column) SELECT id, clob_column FROM foo;
This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem? -> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
I need to export large number of records from select into the text file. It's about 2milion records.I can do it by PLSQL (see below) where executing of process takes time too much. How to export to text file faster?
I have a column SIX_NO with numbers and null values in it and I would want to display the column with the highest number first and others, in a descending order. I have used the below code
select * from (select six_no from table_name order by six_no desc ) where rownum <=5;
i have a table in my PD database which have more than 30,000 records .some records in a column say p_code is not tagged with code like '9876543'while other records are tagged in this column with code such as '19022345678'.
Now i want to update these records with tagging 1902 with each one .
this is the table i have in which repeated itemids for different dates,for example itemid 205 and 203 comes 2 times because that item was consumed again and again in a different date. like this the table data has more than a 20 thousand records;
In which i need to select the last updated dates itemid from this table, because same item id could be consumed 'n' number of times so i need to select it as per the last date or latest dates itemid from this table for each and every item, so how can i select it?
If there are 100 colums or above in a table and we dont want select a single(any one) column from those 100 columns. is there any command or function to do this in oracle.
I can do it by writting names of all 99 columns but as this method is lenghty, so need some efficient way to do this.
like in SQL server we have a "except" keyword through which we can rule out the columns .
ex: select * except (column_name1,column_name_2) from Table_name.
