SQL & PL/SQL :: How To Select Last Code From A Column
Jun 27, 2012
i have a table name item.
itemid itemdate Amt
200 17/06/2012 2000
201 18/06/2012 3000
202 19/06/2012 1000
203 20/06/2012 6000
204 21/06/2012 9000
205 22/06/2012 1000
205 24/06/2012 2000
203 26/06/2012 1000
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?
View 6 Replies
ADVERTISEMENT
Dec 7, 2010
I got table with column of XML codes like this:
select xmltype ('<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="result"><value>success</value></parameter>
<parameter name="showBirthday"><value>false</value></parameter>
<parameter name="_wrCommand"><value>clearCacheBefore</value></parameter>
</parameters>'
) val
from dual;
And I need to select records from table where xml code complies with requirement:
name="result" equals success.
name="result"><value>success</value>
View 25 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
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Aug 4, 2011
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
What is the sql code for the RESULT-column?
View 3 Replies
View Related
Jul 18, 2013
I have written this code below in Disco Admin 10g but get Ora-00918 column ambiguously defined error even though I have used alias in the table name.
Select
porh.segment1
,Porh.creation_date
,Porh.last_update_date
,porh.closed_code
,porh.authorization_status
,porh.description
,porh.note_to_authorizer
,porh.cancel_flag
,porh.enabled_flag
,porl.line_num
[code]....
View 4 Replies
View Related
Jul 31, 2013
I have a INSERT query which is happening with a SELECT query.
===================================================
INSERT INTO tbl_fact_effort_lvl_data ( ............... )
SELECT ria.report_id,report_status: :,
((SELECT lov_num_val
FROM tbl_reference_data
WHERE lov_type = 'FREQUENCY' ) * (SELECT SUM(pph_task)
FROM tbl_ri_process
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id
FROM tbl_fact tf LEFT JOIN ......... ;
====================================================
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
View 8 Replies
View Related
Feb 12, 2009
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies
View Related
May 30, 2012
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
View 5 Replies
View Related
Feb 13, 2012
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
View 7 Replies
View Related
Sep 4, 2012
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;
It displays the results as NULL.
View 12 Replies
View Related
Sep 2, 2009
I want to find out all the tables that contains a known column name?
I know a column name called 'receiving_number', but don't what what table it's in.
View 2 Replies
View Related
Feb 16, 2011
i need a Select * from tablename just 1 Row for each distinct ActionMode column value
CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),
[code]...
View 5 Replies
View Related
May 10, 2012
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.
View 6 Replies
View Related
May 7, 2012
By select I got records (see table below) and I need to make select where result will be just one row with all values by payer_flag=Y.
There is a table:
CREATE TABLE XXX_MAPE_CC
(
CK_CUSTOMER_CODE VARCHAR2(24 BYTE),
[Code].....
And result should look like:
--------------------------------------------------------------------------------------------
ck_customer_code | invoice_media | personal_no | cnt_active | cnt_deactive | payer_flag
--------------------------------------------------------------------------------------------
6.107441.10 | electronic | 0 | 663 | 128 | Y
---------------------------------------------------------------------------------------------
View 6 Replies
View Related
Feb 14, 2013
How to select max value of date column which tables are having date coulmn.
View 1 Replies
View Related
Nov 28, 2011
I have a requirement of getting different column value for every select clause. my selection is so much freequent. For example.. 10 Select statement /second.
But when if I am running more than select in a second its is showing the same record which is last dequeue.and also tried with RANDOM(). how to get different value for every statement which is running in single point of time.
View 9 Replies
View Related
Jun 20, 2011
I have created a MV here but i cant select the columns from the MV.
create materialized view WGA_ODB_VALID_ATT_Q1_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS
select
a.userid "Userid",
COL10 "Order_Action",
DUEDATE "Date_Processed",
COL7 "FEIN",
[code].......
Error : ORA -00904: "ORDER_ACTION":invalid identifier
View 1 Replies
View Related
Aug 11, 2011
I need to know if it is possible to hide one or more of the columns in my SELECT statement as I only need them in there where clause.
View 6 Replies
View Related
Sep 10, 2013
Is there any way in PL/SQL to select the values from all columns of a table record into an array?
For example:
C1|C2|C3
0 |1 |2
v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2
or
v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2
But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
View 10 Replies
View Related
Aug 29, 2012
just i want add diiferece column in my select statement.For Example:-
select
Name,
Salary,
Designation,
date_first_payment,
date_last_payment,
(date_first_payment-date_last_payment) Datediff
from My_table
then its working
select *,(date_first_payment-date_last_payment) Datediff
from My_table
its not working..
View 7 Replies
View Related
Jan 24, 2012
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.
Create table Test1(IDNumber(10),
Namevarchar2(100),
Descriptionvarchar2(1000),
Sitevarchar2(100));
Insert Into Test1 values(10,'Thomas','Manager','LA');
Insert Into Test1 values(11,'George','Chairman','LA');
declare
MTest1Test1%RowType;
str1VarChar2(1000):='Name';
MValue1Varchar2(100);
begin
Select * Into MTest1 from Test1 where ID=10;
str1:='Select MTest1.'||str1||' from dual';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;
/
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
View 5 Replies
View Related
Dec 11, 2012
explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.
A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.
View 2 Replies
View Related
Mar 6, 2012
I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
View 6 Replies
View Related
Nov 21, 2011
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:
idCoupon Type
123Amount
123Percent
View 6 Replies
View Related
Sep 12, 2013
I'm writing a VPD function to be used for column masking. The predicate (WHERE-clause) it generates may take many different forms. In particular, it may contain inner-selects; for example,
"exists(select '*' from B where B.VAL = '123' and A.KEY = B.KEY)"
where A is the table that is associated to the VPD function, and B is some other table.
if this is OK for column masking? If not, my VPD function may sometimes work and sometimes fail, in unexpected ways.
The Oracle Database Security Guide (11g Release 1) says
Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates".
This seems to indicate that there are cases where a VPD function works for row-level security, but not for column masking.
an example of a 'regular Oracle VPD predicate' that doesn't work for column-masking?
View 5 Replies
View Related
Feb 5, 2011
select numeric values from a varchar column
For Example:
select * from t1 ;
ID
----------
00300
ABCXY
04230
xyzab
i need to fetch only numeric values from column id
My output should be
00300
04230
View 8 Replies
View Related
Jan 27, 2012
In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. sort it out?
Create table Test1(ID Number(10),
Name varchar2(100),
Description varchar2(1000),
Site varchar2(100));
[Code]...
I am getting the error:
ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9
My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names.
I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database.
View 3 Replies
View Related
May 1, 2011
I have a problem executing a function.
There are two tables,
1. Table with column names of a second table mapped to certain variables.
ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
View 2 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 Replies
View Related
