-----
SELECT SUM(s1.PRODUCT_QTY) INTO anz
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID
AND s2.REGION = 'ANZ'
AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
[Code]..
The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.
The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like
-----
SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean
FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3
WHERE ..................
-----
im as using oracle 8 with sqltools 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;
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.
assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause
declare usergrade varchar(4) := 'pass'; user_unitcode number(6) := ;--needs three unit codes to equal pass
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure. --------------------------------- create or replace PROCEDURE emp_test( c_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN c_cursor FOR SELECT emp_name, emp_doj, emp_role FROM emp_table ; END; ---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
------------------------------------------- Error starting at line 18 in command: execute frm_lst Error report: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'emp_test' ORA-06550: line 1, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action: --------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work. ____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1 (e_name OUT emp_name%TYPE, e_dob OUT Edob%TYPE) IS BEGIN SELECT emp_nam, Edob INTO e_name, e_dob FROM emp_table END emp_test1; End; ______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=p1; END; ------------ Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS v1 varchar2(100):=p1; BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=v1; END;
I have more 100 partition in a table, I would like to query 10 partitions alone in single statement, Hope it could be possible like query data for single partitions, provide the syntax for the same.
Because if I try to query for all the partition then the query is Hanging due to the large no of data, then I can query single partition by partition then it takes more than a day. so, I would like query data for 10 partition in a single select.
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob from patients join schedule using (key_patien) join adtmirro using (key_patien) where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
I have data such as 'hours', 'date' when and employee worked on the project. What I need is to select the total amount of hours per month of March, April, May, etc...
I know how to select data per single date but wonder how to do it per multiple dates. How does one select total amount of hours per multiple date ranges (March, April...)?
I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:
Table1 code date name aaaa 1/1/2003 billy bob bbbb 2/2/2004 louis lewis cccc 3/3/2005 joe crab dddd 4/4/2006 mary little eeee 5/5/2007 joe black
Table2 code date name aaaa 2/2/2004 larry cole aaaa 3/3/2005 nat king bbbb 2/2/2004 louis lewis cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson dddd 7/7/2009 missy muffet dddd 5/5/2007 joe black eeee 8/8/2010 elton rocket
desired output results from select
aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket
Here is the select that I have so far:
select table1.rowid, table1_code, table1_date, table1_name, table2.rowid, table2_code, table2_date, table2_name from table1, table2 where table1_code= table2_code order by table1_code;
The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping.
I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
My table has the follwoing 3 columns (in addition to others)
Col Name = active ; type=number ; values=1 (true) or 0 (false) col name start_date ; type=date; format=dd-mmm-yy col name end_date ; type=date; format=dd-mmm-yy
I need to select all rows where all active=1, start_date=<today and end_date=>today
my sql is: SELECT id, start_date, end_date FROM offers WHERE (active='1' AND start_date<='14-SEP-09' AND end_date>='14-SEP-09');
However the results are not right. Example, the first row returned is: Offer5000312 01-JAN-09 11-DEC-08
This is not correct. Due to the end_date this row should not be part of the results.
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 want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..
#select name from v$database; #select log_mode from v$database; #select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID'; #select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID'; #select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID'; #select count(*) "Broken Jobs" from dba_jobs where broken!='Y'; #select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B left join C on (C.ID_A = A.ID and C.ID_B = B.ID) [... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
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'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'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.
I'm working on a query that will show how many differents SKUs we have on-hand, how many of those SKUs have been cycle-counted, and how many we have yet to cycle-count.I've prepared a sample table and data:
What I also want to do is select another column that will group by sku.abc and count the total number of A, B, and C SKUs where the lot.qty is > 0:
SELECT sk.abc AS "STRATA", COUNT (DISTINCT sk.sku) AS "Total" FROM sku sk, (SELECT sku FROM lot WHERE qty > 0) item WHERE item.sku = sk.sku(+) GROUP BY sk.abc
Finally, I need the last column to display the DIFFERENCE between the two totals from the queries above (the difference between the "counted" and the "total"):
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE v_mydept1 TEST1.DEPT_CD%TYPE; v_mydept2 TEST2.DEPT_CD%TYPE; CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR; [code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF" I am not sure what is the exact problem when we want to update multiple columns of different tables.
I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number. sample:
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG AFTER UPDATE ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
Below is the code I am facing problem using tablename as variable.
I have five tavble is scheme Emp1,Emp2..Emp5
CREATE OR REPLACE procedure emp_up as tablename1 varchar2(30) ; Begin For x in 1..5 LOOP tablename1 := 'EMP' ||to_char(x); EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')'; END LOOP; End;
The following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.
DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt); DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
SQL.Append(" WHERE DATE >= :beginDTParameter "); SQL.Append("AND DATE < :endDTParameter");
