Pull All Degrees Into Table Based On Which Institution Is Selected?
Mar 26, 2013
I'm trying to pull all the degrees into a table based on which institution is selected. If institution is 'AAA' or 'BBB' then pull ACAD_PLAN, DESCR by ACAD_PROG where ACAD_PROG >= some value and <= some other value.
If institution is 'CCC' then pull ACAD_PLAN, DESCR by institution regardless of ACAD_PROG.
Something like
INSERT INTO table
SELECT
'value_a'
[Code].....
I don't have this formatted right cause it keep telling me missing keywords.
I'am having a query regarding population of a list item based on another selected list item that is whenever i select the state from the combo box all the city's under that state should be automatically populated.
I am working on a simple form which will get the user to fetch required columns of employee master.
For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.
As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.
To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.
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.
Suppose There are Four options in a Radio Group. From this whichever option I select using the Mouse that option must be Inserted in the Database table field.
How to do this Using Radio Buttons.
Also If I want to select more than One Option Then I use Check Box. For this Also When I select two or three options simultaneously then these values must be Inserted in the Database Table Fields.
I want to start by saying I am brand new to SQL. I have an access database linked to my oracle and am trying to query a very specific set of data and I can't seem to narroe it down. I have 244,000 lines in the DB and I'm trying to find items on a specific trype of vendor agreement. I may have the same item on multiple agreements. ex 1-, 1a-, 2-,2a-,3-,3a-,4-,4a-,5-,5a-,6-,6a-,7-,7a-,8-,8a-.
I need to find a way to select just items that appear on a 4- or 4a- and no other agreement reference. The query I did so far pulls all of the 4- and 4a- agreements but will also pull items,like #233 in the example above, but not showing the 1a- agreement. I need it to overlook that item eventhough it does appear on the agreement I am looking for but also has an agreement I am not. the statement I am using right now is:
SELECT item, ven_item, ven_agrmt_ref, base_cost, vendor FROM "all items by agreement" WHERE ven_agrmt_ref >= '4' ORDER BY item
I am having trouble trying to pull the next value from a query based on a where clauseThe query I am using is:
Declare nextID NUMBER(22);BEGINselect lag(ref_contact_id, 1,0) OVER (ORDER BY ref_contact_id)into nextID from (select ref_contact_id,lead(ref_contact_id, 1,0) OVER (order BY ref_contact_id) as "NextNbr", lag(ref_contact_id, 1,0) OVER (order BY ref_contact_id) as "LastNbr"from (select rc.ref_contact_id from REF_CONTACT rc order by FIRST_NAME ))where ref_contact_id = 793 ;END; The returned value is 0.
I understand why but not how to pull the next value base on a particular ref_contact_id.
In the database we use for transfer articulation, there are numerous tables delivered with the product. The institution decided not to use certain fields, and all instances of those fields have no data. In other words, there might be a field in the table called INSTCD, but no records in the table have ever inserted any data into that particular field. In the table there are thousands of records, and we don't necessarily know which of the fields have never been used (no list has been retained and no one who initially was involved in the decisions is available to ask), as there are multiple fields in each table. How can I write a query that pulls only the fields in the table that contain data. In the example below, the SHRTRIT table contains a field called ACTIVITY_DATE, but there is no data in any record in that particular field, so I don't want it to show up on the output. In this particular case, I KNOW not to pull this field in a SELECT, but in a case where there might be 130,000 records and I DON'T know if a field has records in it, how could I do that?
if the question I'm asking doesn't make sense and I'll attempt to word it better.
I want to move data between two instances and recommended we create a local database link to PULL data from remote database located here (supplier on site) but they want to PUSH data to us. I thought you could only PULL data over a database link but then read the link [URL] where PUSH is considered ? I was going to use standard creatas like create table A as select * from table A@<remote_db_link> which works well and fast ( tried and tested) but some are saying they think PUSH quicker/better ?
we do have data "PUSH" already but this does not use a db link - effectively it calls a local proceedure here and passes a row of data and is slow ie for a 1000 row table to be pushed to us we have our local proceedure called 1000 times.
I have always suggested a PULL with db_link as the fastest method - any proof OR info on a fast PUSH method ( that is quicker than PULL ) ? can you REALLY push ?
I'm needing to pull data into a cursor, then split this data into 3 different tables, each having the same number of rows and a select number of columns from the original. i can pull the data, but then i can only access it one row at a time via FETCH, then i can't load into the 3 new CURSORS one row at a time.
I am writing a query where I'd like to pull one year's worth of data. Ideally I want to prompt for the END DATE and have the query go back in time one year from that date.
Here is what I've got after doing some research online... but It's not quite working for me.
select * from mrtcustomer.profile where reg_type = 'B' and contact_type = 0 and active_ind = 'Y' [code].....
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
SQL Plus version Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production PL/SQL Release 8.0.5.1.0 Production Forms Version : 6i Reports Version: 6i O/S : Microsoft Windows Xp professional Version 2002 Service Pack 3
With regards to the above version description here is my query. I have a form which calls report it accepts various parameters like date between, appeal and so on . I want the report to be restricted to the date parameter as passed by the user.Here is my coding which runs report
Declare Pl_id ParamList; where_cond varchar2(2500); Begin ------------Appeal------------------ if upper(ltrim(rtrim(:appeal)))<> 'ALL' then where_cond:= where_cond ||'and tbl_donation.appeal_code='||ltrim(rtrim(:blk_ihelp.appeal_code)); else where_cond:= where_cond||' and tbl_donation.appeal_code is not null'; end if;
-------------Date Option---------------- if :date_option is not null then if :date_option = 'BETWEEN'then where_cond:=' and tbl_donation.donation_date between '''||ltrim(rtrim(:fdate))||''' and ''' ||ltrim(rtrim(:tdate))||''''; else where_cond:=' and tbl_donation.donation_date '||:date_option||''''||ltrim(rtrim(:fdate))||''''; end if; end if;
--------------Country------------------- if upper (ltrim(rtrim(:country))) <> 'ALL'then where_cond:= where_cond||'and tbl_donation.country_code='||ltrim(rtrim(:blk_ihelp.country_code)); else where_cond:= where_cond||'and tbl_donation.country_code is not null'; end if;
-------------Contact Code--------------- if :contact_code is not null then if :contact_code = 'BETWEEN'then
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
I used to have two tables to store details of PROPERTIES e.g UNIT_COMMERCIAL , UNIT_RESIDENTIAL. I need to combine both of them into one table called UNIT. I moved the data to the new table, but now I am stuck how to update the values of the child table which called MARKETING.
---
Now, I have two tables:
MARKETING (ID NUMBER PK , OLD_UNIT_ID NUMBER FK ...) UNIT (NEW_ID NUMBER PK , OLD_UNIT_ID NUMBER ... )
I need to update the value of OLD_UNIT_ID in Marketing table to be equal to NEW_ID in the table of UNIT. As you can see the values of OLD_UNIT_ID in both tables are the same.
I used this statement
update ( SELECT distinct M.UNIT_ID , U.OLD_ID , U.ID FROM MARKETING M INNER JOIN UNIT U ON (M.UNIT_ID = U.OLD_ID)) set unit_id = idBut I got this error:
ORA-01732: data manipulation operation not legal on this view??
I have partitioned the table based on field.But when I am selecting by Partition or by the field I am getting Explain plan as Table Access full.I am pasting the sql and Explain Plan here. The table has two partition by BOOKING_DT_WID. One less than 20100801 and other less than 99991231.
CODESELECT * FROM WC_BOOKING_SALESREP_F WHERE BOOKING_DT_WID >= 20100801; SELECT * FROM WC_BOOKING_SALESREP_F PARTITION(SALESREP_LESS1_99991231); Here is the Explain Plan for the same. CODESELECT STATEMENT ALL_ROWSCost: 1,501 Bytes: 293,923,641 Cardinality: 809,707 4 PX COORDINATOR [code]....
How do I know if the sql is doing partition prune.
in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function
CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2) RETURN VARCHAR2 IS v_old_table user_tab_columns.table_name%TYPE; v_where VARCHAR2(4000); v_first_col BOOLEAN := TRUE;
[code]....
but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?
Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger
CASE:
Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60
i am trying to create Procedure which will create the partitions based on the other table date values one per each day.
CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2) AS V_PART_NM VARCHAR2(20); V_PART_CNT NUMBER; V_DATE DATE; V_SCHEMA_NAME VARCHAR(15);
[Code]..
It is not creating the partitions and even not giving any errors.
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
I am new to PL/SQL, worked mostly on SQL server, I have to change the table name dynamically based on the parameter.and used a ref_cursor to display the results in a report. when I execute it throws me an error.
create or replace procedure test1 ( p_eod_date IN VARCHAR2, p_link IN NUMBER, c_rec IN OUT SYS_REFCURSOR) [code]....
ITEMNUM STORELOC lastyear currentyear AM1324 AM1 need sum(quantity) here need sum(quantity) AM1324 AM2 need sum(quantity) here need sum(quantity)
We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.
we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.
How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table
