I am attempting to update a single field in one table based on a select from two tables. However, I am receiving the following error.
ORA-00933: SQL command not properly ended
The sql I am using is as follows:
update PS_TRNS_CRSE_DTL
set RQMNT_DESIGNTN = 'TRN'
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
where (SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.MODEL_NBR, A.ARTICULATION_TERM, A.TRNSFR_EQVLNCY_GRP, A.TRNSFR_EQVLNCY_SEQ, A.TRNSFR_STAT, A.GRADING_BASIS, A.RQMNT_DESIGNTN, B.STUDY_AGREEMENT
FROM PS_TRNS_CRSE_DTL A, PS_STDNT_CAR_TERM B
[code]......
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
I have a table where i need to update one field values based on another field of the same table , simply as it is.I have done this using one select all check box , on clicking that all check boxes of item_trans table will get selected , then i will un select some of check box and then using one button, i will update the value of the fields which are checked only.
I have put the sample code but when i am updating its taking long time and hanging.I am also attaching the form based on the test case provided.
insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE1',40,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE2',20,NULL); insert into item_trans(TRANS_ITEM,TRANS_QTY,TRANS_ACT_QTY) VALUES ('TREE3',20,NULL);
--i want to set the value of trans_Act_qty as trans_qty
--i create one dummy or test block to keep the select all check box. for that table test script is
CREATE TABLE TEST ( C VARCHAR2(2000 BYTE), B NUMBER, A NUMBER );
insert into test (C,B,A) values ('A',1,1);
--code written in select all check box which is created on test.block.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP :M_END_YN := :M_END_ALL; [code].......
--code written in M_END_YN ( actual check boxes where i will uncheck).
IF :M_END_YN = 'N' THEN :M_END_ALL := 'N'; END IF;
--code written on button to update those values which are checked.
BEGIN GO_BLOCK('item_trans'); FIRST_RECORD; LOOP IF :M_END_YN = 'Y' THEN [code]......
In my application i have a requirement as follows.I have 3 tables table1,table2,table3.I have 4 tickets for one license number which is related to client table as follows.
table1 ===== license_nbr(pk) name address =============== ===== ======= LicNo1 test testing
so now the excess paid amount to be adjusted for the remaining tickets through tk2 to tk4.and only tk4 should remain with 400 the balance amount should be updated accordinglyand i have a license number which is a foreignkey of client table. writing a procedure for updating the table2 based on the table3.
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
i've created a schema, which contains 30 tables. the tables are accessed & updated through a web based application. i've made public synonym for all tables for some reasons. how can prevent any one other than application users from updating the tables from Sqlplus.
I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;
The output: Week 16 ---------------------------- to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?
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 the query below, I'm attempting to replace task codes with task descriptions by left joining to a multi-purpose control table.
In our corp, the description for the task code varies based on the value of change_program in each record, so rather than referencing the value of code_index.tabl statically as I do in the query below (201), I need a dyanamic variable to be defined for each record based on the value of work.change_program, which would represent the value for code_index.tabl.
For each record if change_program =1 then v_tabl = 201 elseif change_program =2 then v_tabl = 202 elseif change_program =3 then v_tabl = 203 else v_tabl = 201
how to declare and use variables.
SELECT account, change_program, task_code_01, task_code_02, task1.longdesc, task2.longdesc FROM work
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
Personally, I don't need the top value(s), but it would be nice.I was trying a few routes with rownum, and I can get it to pull 1 set of pairs with a where location= condition, but I can't seem to successfully combine the two.
I have a need to change the LOV of a page item's select list based on the selection in another page item's select list. So in other words, for a page item that is a select list, I need to use one LOV normally, and a different LOV if the value of a different page item is set to X.
A dynamic action would be useful here (on change where Select List 1 = x), but I don't know how to take the action of changing LOVsfor Select List 2 based on that action.
I need to partition a existing table based on varchar2 field (which is actaully date value but storing as character in the table). Using below statement for creating table, but getting error.
I would like to populate a text box based on selection from a LOV. If someone selects a LOV value and then tabs off off of that element, I would like the text box populated from a sql statement based on the LOV value in the predicate.Application Express 4.1.1.00.23
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
I have two master tables , one is supplier master and the other customer, the requirement is , i will pass a code as IN parameter and the result it should bring the appropriate name of that code , for example if i pass a code which exists in supplier master , it should get me the supplier name for that code , if i pass code which exists in customer ,it should bring customer name.
CREATE TABLE OM_CUST (CUST_CODE VARCHAR2(30),CUST_NAME VARCHAR2(240)); INSERT INTO OM_CUST VALUES ('1001','CUST1'); INSERT INTO OM_CUST VALUES ('1002','CUST2'); CREATE TABLE OM_SUPP (SUPP_CODE VARCHAR2(30),SUPP_NAME VARCHAR2(240)); INSERT INTO OM_SUPP VALUES ('2001','SUPP1'); INSERT INTO OM_SUPP VALUES ('2002','SUPP2');
Desc Table A (account) Account1_id Account2_id name, empid
Table B (Bill ) BillNo Advertiserid agencyid total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*) 2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR 3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER; COUNT(*) ---------- 2243 [code]...
have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )
Query-2 Xcom -------------------------------------- select null as sMO_NO, xso.created_date as SPLY_ORD_DT, xso.fk_cust_id as cust_id, cust.cust_po_no as cust_PO_NO ,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name, xsol.CARTONS_ORDERED as SPLY_ORD_QTY, [code].......
Now the requirement is One of four conditions are possible for each Supply Reorder Number:
. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls . SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records - Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE. . SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record - Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES. . SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT) from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER. - If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE. - If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE. - If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.
i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.
I'm trying to leave out results of a query that match certain conditions, basically I'm looking at two tables that each have a status field. If the status of both is equal to zero then I want the result to be left out of the search results. I'm struggling with the logic though. In the where clause I've tried:
(ACCO.STATUS != '0' AND CCO.STATUS != '0')
this, however, omits everything where the status combination isn't 1 and 1. I've also tried: ( (ACCO.STATUS = '1' AND CCO.STATUS = '1') OR (ACCO.STATUS = '1' AND CCO.STATUS = '0') OR (ACCO.STATUS = '0' AND CCO.STATUS = '1') )
I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:
exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";
is it possible to do like this, that it should prompt you to enter the start and end date?
declare i INTEGER:=0; flag boolean:= FALSE; vcInsert:='INSERT INTO CRM_DELT_IMPORT ('; if length(i_vcColumn1) > 0 then flag:=TRUE;
[code]....
i am fetching data from excel all data is of general type, here i am passing value is VTDATE varchar2(100) but actual database column filed is DATE , How can i pass '13-06-13 05:54:33' to database table.
if i put this condition vcInsert:=vcInsert|| ','||''''||i_ldata(i).to_char(VTDATE,'DD-MM-RR HH12:MI:SS') i get the error to_char must be declare.
if i remove this line
i am getting this error ORA-01843: not a valid month
