PL/SQL :: Select Statement For Last Name Added

Nov 12, 2013

i want a select statement to retrieve the last name added into the "ename" column .i mean :if i insert "jack" into "ename" column , i want to write  a select statement to retrieve that last name while i do not know it is jack or any other name ? 

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

If Statement In Select?

Mar 22, 2013

simply select and works great:

select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,

substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,

case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2

from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

where UPPER(ISSUE_STATUS) like '%OPEN%'

Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2

if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?

View 4 Replies View Related

Select Statement Errors

May 5, 2010

I have a question about select statements, as I am new to them and don't know how to work all the commands yet.

I'm making a select statement that is about half right... it is shown below:

select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);

I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.

View 2 Replies View Related

Format Of Select Statement

Jun 15, 2007

decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,

After executing this statement. I get negative values if cancel time is null

I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time

View 1 Replies View Related

How To Set Timing On For Every SELECT Statement

Aug 23, 2008

how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.

View 6 Replies View Related

SQL & PL/SQL :: Using Alias In Same SELECT Statement

Sep 1, 2011

I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).

Ex:

Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;

Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?

View 12 Replies View Related

SQL & PL/SQL :: Variable In Select Statement

Jul 5, 2011

I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:

SELECT
Location,
LEVEL,

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.

this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.

FROM
S_ORG_EXT

where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'

View 7 Replies View Related

SQL & PL/SQL :: Select In Case Statement

Feb 11, 2011

I have the following tables:

create table lookups (code varchar2(20), amount number);

insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);

create table products (id number, cost number, year varchar2(4));

insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');

commit;

I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100

I have written this query:

SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;

This returns

ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N

The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.

ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N

I am not sure how to do this. Or is there a better way of doing this than using CASE.

View 4 Replies View Related

SQL & PL/SQL :: How To Add Two More Columns Into Select Statement

May 6, 2011

Below is the query that I have:

Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');

How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:

Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');

View 5 Replies View Related

SQL & PL/SQL :: Cursor Select Statement?

Apr 28, 2010

I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below

CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,

[code]...

What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.

View 1 Replies View Related

SQL & PL/SQL :: How To Use CASE In SELECT STATEMENT

Jun 24, 2011

select GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),

[code]...

i am getting error like this
ERROR at line 9:
ORA-00911: invalid character

View 4 Replies View Related

PL/SQL :: Execute Immediate On SELECT Statement

May 3, 2013

Execute Immediate on SELECT Statement

declare l_stmt VARCHAR2(1000);
begin
l_stmt := 'Select * from mytable' ;
EXECUTE IMMEDIATE l_stmt;
--- dbms_output.put_line( l_stmt);
end;

it is not returning any output. I have hardcoded the table name here where as in real time Mytable name will differ.

View 14 Replies View Related

PL/SQL :: Variables In SELECT Statement

Feb 19, 2013

I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like

SELECT :var1 AS var1
FROM t1
WHERE
:var1 = 1

In the past I have seen where variables can be used in the WHERE clause like

SELECT c1
FROM t1
WHERE
c1 = :my_column_valueand this makes sense to me.

Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???

The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?

The only thing I can gather that the first query does is something along the lines of

SELECT 1 as var1

so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.

So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.

View 5 Replies View Related

SQL & PL/SQL :: Function With DML In Select Statement?

Sep 14, 2013

How to use a function with DML operations in it in a select statement.

View 11 Replies View Related

PL/SQL :: Single Select Statement

Apr 22, 2013

I am trying to find the best solution for the following problem

I have a table called HIERARCHY with 3 columns.

col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8

I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.

For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.

How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!

View 5 Replies View Related

Using Declared Variable Within Select Statement?

Apr 5, 2011

here is what i am trying to do: im as using oracle 8 with sqltool

i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them. for example:

select SomeID from SomeTable;

i want SomeID to be put into a variable. but i still want to be able to get a normal select query at the end so that i can see the returned value:

i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;

but could not get it to work.

View 2 Replies View Related

TOAD - LOOP With A Select Statement

Oct 19, 2011

I can work with 'straight' query's etc., but now I want to make a query with a loop. I have made a simple one to demonstrate what I want but the real one is working is working by it self but I want to get it work with a loop.

This is the simpel version:

DECLARE

P_UID NUMBER;
Max_UID NUMBER := 42220;

BEGIN

P_UID := 42210;
LOOP
select *
from contract lc
where lc.UIDCONTRACT = P_UID;

P_UID := P_UID + 1;
EXIT WHEN P_UID > Max_UID;
END LOOP;
END;

The error I get is:

Line 10, column 9:

PLS-00428: an INTO clause is expected in this SELECT Statement.

So I know you can declare a variable and then CONTRACTID INTO v_contractID.

But if I have to put every field in a variable what is then the advantage of a loop. So I expect that I'm on the wrong road and not understand how this works.

View 4 Replies View Related

SQL & PL/SQL :: Make Cursor To Take Select Statement?

Jul 9, 2012

How to make cursor to take a select statement

then check the rowcount

in certain case: assign another selete statement to the cursor

View 3 Replies View Related

SQL & PL/SQL :: How To Remove Nulls In Select Statement

Apr 8, 2010

I have a sql like

select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');

[Code]....

Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?

View 27 Replies View Related

SQL & PL/SQL :: Dynamically Build SELECT Statement

May 8, 2013

We have an requirement to build (XML) Select statement dynamically based on the mapping table (which is in hierarchy format)

I have removed xml keyword, so query will not work. but need procedure which will recursively check mapping table (parent and child table) and build below given select statement.

Example 1:
SELECT dname,
(select ename,

[Code]....

View 8 Replies View Related

SQL & PL/SQL :: Execute The Expression In Select Statement

Feb 12, 2013

CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;

CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;

I want to be execute the expression should run in select statement how to do? and tried as like below,it's not working.

select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;

View 15 Replies View Related

SQL & PL/SQL :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related

SQL & PL/SQL :: Using Select Statement In Decode Function?

May 11, 2010

I am trying to use decode function in sql and inside decode function can I use select statement ?

here is my sql

select we.wf_entity_id, decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
) from wf_entity we
where

[code]....

see this

decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
)

will this work? Its not working for me?

View 2 Replies View Related

SQL & PL/SQL :: Use Collection In Select Statement As Table?

May 26, 2011

i'm trying to use a collection in a select statement as a table, but i've got an error which i don't understand.

This exemple is very simple. I'm trying to validate the solution. The object will contain the result of a query from a big table, and i have 4 request to make on it. I don't want to select 4 times the big table to get the result, but i need the whole result to make my requests (intersect, minus and union)

CREATE OR REPLACE
TYPE zy_w AS OBJECT(
CODE_INFORMATIONVARCHAR2(4),

[Code]....

****************
Rapport d'erreur :
ORA-06550: Ligne 12, colonne 3 :
PL/SQL: ORA-00947: nombre de valeurs insuffisant
ORA-06550: Ligne 5, colonne 3 :
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

View 9 Replies View Related

SQL & PL/SQL :: How To Make Procedure From Select Statement

Feb 8, 2011

i do have below query in oracle db just joining two views,

select docview.firstname as "First Name", docview.lastname as "Last Name", docview.mrn as "MRN", docview.physician as "Physician", docview.surgicalcong "Surgical Consent Missing", docview.admissionnotemissing "Admin Assessment Missing",
(case (docview.doctype) when 135 then 'Doctor Chart Checklist' else 'DS Chart Checklist' end) as doctypename,
docview.hpmissing "History & Physical Missing", docview.nursingassessment "Nursing Admission/Assessment",

[Code] ........


how can i make a procedure with below two steps,

1) load the result of above query to some temp table / or even a fixed table (which ever easier)
2) list all data from the temp table / fixed table created in first step

so in the final expecting result is

execute myprocnamehere -- will result same as running above query

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved