SQL & PL/SQL :: Retrieve Data With Multiple Select Statements

Jun 3, 2012

I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.

select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",

[code].....

View 3 Replies


ADVERTISEMENT

Oracle 10g - Loading In Computer Select Statements Say No Data Found?

Jun 7, 2011

I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show

if a column name select * from table name where duty_date = '05-JAN-11'no data found

View 1 Replies View Related

Application Express :: Retrieve All Data From One Multiple Checkbox To Another?

Jul 11, 2012

How do you retrieve all data from one multiple checkbox to another? The method I'm using returns one choice even though multiple have been chosen.

View 2 Replies View Related

Forms :: Select Table Name And Retrieve Its Related Data

May 29, 2012

I wanna have a form which has a list box which is including of all table names, by selecting the required table by user all column name and its related data should be retrieve.I have a cursor which give me all field names related to the table by I do not know how can I retrieve table data:

1. How to add item through block base on number of column which selected table has.

2. How to set value of table column in these mentioned column which added in the block.

3. And at the end retrieve the table data.

View 1 Replies View Related

Running Multiple Update Statements At Once

Feb 1, 2012

It started out pretty simple where I had to update about 40 contacts in the database and I would have 40 separate update statements I would run. The task has jumped to about 300 contacts and I don't want to run 300 update statements. I would like to run this all at once. For example:

update contact
set name = 'Name1'
where row_id = 'row_id1'

update contact
set name = 'Name2'
where row_id = 'row_id2'

update contact
set name = 'Name3'
where row_id = 'row_id3'

My DB is oracle 10, and TOAD is version 9.

View 1 Replies View Related

Concatenating Two Select Statements

Oct 23, 2009

Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.

Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;

Select to_char(sysdate,'YYYY')"Year" FROM DUAL;

View 1 Replies View Related

SQL & PL/SQL :: Using $ Substitution In Select Statements?

Oct 11, 2011

Is it possible to have $ substitutions in Select statements ?

For example

Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00

For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?

View 2 Replies View Related

Cannot View Database Or Run Select Statements

Jun 7, 2011

I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.

I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.

View 3 Replies View Related

Auditing Unsuccessful Select Statements

Sep 3, 2013

I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.

I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.

View 3 Replies View Related

Select Statements - Use A Join - Getting Error?

May 2, 2010

We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.

I need to write a select statement that shows what customers are taking what drugs and how many mgs they take

customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit

i think i need that information for the select but I cant seem to write a select statement that runs without errors.

View 6 Replies View Related

SQL & PL/SQL :: Combining 2 Select Statements In A View?

Apr 8, 2012

1. I Wnat to analyze the buffer cache hit ratio. This is what i did.

DECLARE
bufcac NUMBER(10, 2);
BEGIN

[Code]....

2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad

SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,

[Code]....

Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.

I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.

View 10 Replies View Related

SQL & PL/SQL :: Insert And Update Through A Select Statements

Jul 7, 2010

how can i insert and update to a table in oracle database 10g through a select statement. not using merge.

View 2 Replies View Related

SQL & PL/SQL :: Won't Continue To Other Stuff Like Select / If / Then Statements?

Oct 9, 2013

if i have a function

function test_1
begin
if....then ...end
SELECT...
BULK COLLECT INTO..
FROM ....
[code]...

obviously this is not the whole code, but lets say the first if...then..end is true , does this mean it will exit the block? like it won't continue to the other stuff like select, if ...then statements?

View 15 Replies View Related

SQL & PL/SQL :: Combine Two Independent Select Statements?

Dec 10, 2010

ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.

1)

SELECT
COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL,
COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,

[Code]....

2)

SELECT
SUM (CHEQUE_TOTAL) CHEQUE_TOTAL
FROM RI_CHEQUE_VOUCHER_REFUND refund
INNER JOIN CH_CASE case ON (case.id = refund.id)
INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED')
WHERE
case.creation_time<= SYSDATE
AND
case.creation_time>= SYSDATE-7

Again I need to combine the resultset.So the result would look like

Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total

View 3 Replies View Related

PL/SQL :: Procedure To Execute Multiple Statements As A Batch Script

Jul 16, 2012

here's my question.

(I'm using PL/SQL Developer with Oracle 10g)

I have an table 'tbcontrol' where for each row I can have one or more sql statements (DML or DDL) in an column.

Example:

task | sql_scripts
1 | create table t1 (c1 number); insert into t1 (c1) values 100;
2 | create table t2 (c2 number); alter table t1 modify c1 not null default 0;
3 | alter table t2 modify c2 not null default 0; alter table t1 add c10 varchar2(10); create table t3 (c3 number not null default 0;

I need to create a procedure where I can pass an task number to execute the sql statements which are in the column 'sql_scripts'. Something like this:

create or replace sp_run_scripts (v_task number)
is
v_scripts varchar2(2000);
begin
select sql_scripts into v_scripts from tbcontrol where task = v_task
execute immediate v_scripts;
end;

But, here I'll have some problems:

- The script will have semicolons (EXECUTE IMMEDIATE doesn't support);
- If I put a begin/end like a PL/SQL script, I can only run DML statements because DDL statements have implicit commit.
- I can't use a cursor for execute each statement at a time because the scripts have multiples-rows. Even if I search for semicolons to 'separate' the scripts in the cursor (using SUBSTR and INSTR functions), I'll have problems with the semicolons between '' (quotations marks).

Now, I admit I'm lost.

Is there another way to make this process work?

View 20 Replies View Related

SQL & PL/SQL :: Bulk Collect And For All Statements To Select And Insert

Feb 19, 2013

I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.

here modified the code and provided only one cursor.

Create Or Replace Procedure Sales_Hist_Update_Bkp Is
Type Type_Name Is Record(
Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
[code]...

View 11 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

SQL & PL/SQL :: Merge Two Statements (Select ID) Into Single Output

Aug 11, 2013

I want to merge the following two sql statements into single output.

select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;

select id,code from dbo;

View 11 Replies View Related

PL/SQL :: Avoid Multiple UNION Statements And Get Static Values In Single SQL?

Dec 31, 2012

Version oracle 11g

Is there a way to avoid multiple UNION statements and get statitc values in single SQL?

select 'Week1' as term from dual
UNION
select 'Week2' as term from dual
UNION
....
....Some functionality like selecting from dynamic table column-rows.... like .. "select term from table(term(rowvalues('Week1', 'Week2'...'Week5')) from dual" or something like that

View 4 Replies View Related

Server Utilities :: Possible To Configure Auditing To Get Number Of Rows Returned By Select Statements

Aug 22, 2011

I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.

We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.

View 8 Replies View Related

SQL & PL/SQL :: Unable To Retrieve Multiple Values / Possibility From A Table?

Jun 1, 2010

I have a table called table1 and its structure is as follows:

rtrn_id number,
entp_abn number,
litm_line_item_nbr number
country_code varchar 2(10)

I am trying to find retrieve value/row where for a particular rtrn_id , the possibility of having a country code of "CA"(for Canada) and "US" or "HK" (US or Hong Kong respectively) exists.

I framed my query the following way and it does not retrieve any result in Production and I believe there are a few values/rows in the "table1".

The query framed is:

Query 1:

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010 and prog_program_cd = '01'
and sched_nbr = '000' and (litm_line_item_nbr = '023' and country_code 'CA')
and (litm_line_item_nbr = '016' and country_code = 'CA')

The above query does not retrieve any rows

Where as if I run a query individually it retrieves tons of rows

(Individual query :

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code = 'CA')

-----Retrieves tons of rows as the country code = Canada for line item # 16

SELECT rtrn_id,entp_abn,litm_line_item_nbr,country_code
FROM table1
where schd_version_yr = 2010
and prog_program_cd = '01'
and sched_nbr = '000'
and (litm_line_item_nbr = '016' and country_code 'CA')

-----Retrieves tons of rows as the country code IS NOT Canada for line item # 23.

There is a possibility that there are rows that have country Code as Canada and not Canada for a particular ID whose line item numbers could be 16, 23.. is there any better way to frame my Query1 to retrieve values/rows?

View -1 Replies View Related

SQL & PL/SQL :: How To Print The Multiple Output Statements In Single Output

Jul 1, 2013

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
SELECT *
-- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID
FROM TARGET_UCM ;
[code].......

this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.

View 2 Replies View Related

SQL & PL/SQL :: Preparing Statements To Return Data Set?

Mar 10, 2011

I have a proc/func which has a string having comma separated numbers :

'1,2,3,4,5'

I have a query where I need to use the string in way like :

SLEECT * FROM ABCD WHERE ABCD.A IN (1,2,3,4,5);

..and whatever is selected has to be returned as a ref cursor or as a table type object.

How can we prepare a statement to do so

View 3 Replies View Related

Retrieve Info From Multiple Databases / Insert Into Central Database Via Database LINKS

Jun 10, 2013

I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.

However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:

DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....

View 1 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

PL/SQL :: How To Retrieve The Data In Where Clause 

Dec 28, 2012

SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

Note mkct_group_code column is varchar2(10) type.

It has values as
20
10
30
20'
30'

View 3 Replies View Related

Retrieve Data From A Table For Certain IDs

Apr 7, 2013

I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.

If the input is 'All' -> the variable :SELECTION is blank so if the SQL :

select name, address, ID from employee :SELECTION

will be

select name, address, ID from employee

If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'

so the SQL:
select name, address, ID from employee :SELECTION

should be

select name, address, ID from employee where ID in ('1234','9999')

However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.

View 1 Replies View Related

Fast Way To Retrieve All Data Blocks

Apr 19, 2012

I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:

select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one

my question is can retrieve all the data in one block more quick than this query.

View 5 Replies View Related

SQL & PL/SQL :: Retrieve Hierarchical Data From Same Table

Nov 10, 2011

Look into the below table:

TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|

Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format

TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|

I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Data That Does Not Exists In A Table

Dec 13, 2011

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.

2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.

My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

View 2 Replies View Related







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