SQL & PL/SQL :: Update Display Name Based On Format?
May 3, 2013
I have a table which has columns like First_Name,Last_Name and Display_Name. Now the every entry in the table have first name and last name populated .
I would like to populate the display name based on the format Display Name = First Name+ " " + Last Name.
For eg. If the first name John and last name is Doe , then the display name should be John Doe.write such a queries which dynamically picks up all the rows and populate the display name.
I have a query on displaying data as per my requirement. I have created a table called sales it has four columns
create table sales(country,state,district,sales); and am inserting some same data
insert into sales('india','TN','Chennai',100); insert into sales('india','TN','KPURAM',120); insert into sales('india','TN','Bangalore',35); insert into sales('india','ANDR','Guinder',100); insert into sales('india','ANDR','Nellai',76); insert into sales('london','city-a','xstreet',89); insert into sales('london','city-a','binroad',100);
select * from sales;
country state district sales india TN chennai 100 india TN KPURAM 120 india TN Bangalore 35 india ANDR Guinder 100 india ANDR Nellai 76 london city-a xstreet 89 london city-a binroad 100
the data is displayed in this format. How i am trying to display data.
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
I have wells that can have multiple statuses (one record for each status). I need to create a column to display a Y or N based on whether or not a given well has only certain types statuses. If all records for a given well are Susp and/or Abd, then I want to display a Y. If the well records include Susp or Abd, but also have other statuses (or do not even have a status of susp or abd) then I want to display a N.
So:
Well Status ident 12345 SUSP Y 12345 SUSP Y 12345 ABD Y 98765 SUSP N 98765 PROD N 98765 ABD N 45678 SUSP Y 45678 SUSP Y ASDFG ABD Y ASDEG ABD Y TTTTT PROD N TTTTT TEMP
I have an issue trying to execute some queries using a dblink. When i run any query with numeric fields only display 4 digit and int the source database the fields have 5 digit. The dblink work between ans MSSQL database to an Oracle Database
Example: MSSQL select cardnumber from card cardnumber 19121 19122
Oracle (with dblink) select cardnumber from card@dblink1 cardnumber 1912 1912
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 00094 122 153 145 224 245 545 114 544 444 111 555 222 00095 233 123 145 224 205 545 114 444 444 111 555 222 00096 163 123 145 224 215 545 114 551 444 111 555 222 00097 163 0 145 224 215 545 114 551 444 111 555 222 conditions:
where condition: where year = 2007
Table Structure:
create table HR_PAYSLIP ( EMP_NO VARCHAR2(6) not null, YEAR NUMBER(4) not null, MONTH NUMBER(2) not null, BASIC_PAY NUMBER(9,2), )
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY) VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups. CREATE TABLE target_data ( T_LINK VARCHAR2(50) , t_mon varchar2(6) , t_grp varchar2(30) , t_views NUMBER
I have a select box with products, and a dynamic action that updates a display only element with the price, based on the product selection.After the selection is made and the page is submitted, I have noticed that the price is not stored in the database. If I change the display only value to a text box, the data is saved. Is this expected behavior? If so, can I add something to the text box to make it not editable?
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 have the following table and data , So i want to update this table(word column) with words that has same soundex with one word of them, the word must be that has the long length if the two words has same length So, update by any of them.
drop table t1; create table t1 (id number(10) ,word varchar2(100));
insert into t1 values(1,'Londn'); insert into t1 values(2,'Egypt'); insert into t1 values(3,'London'); insert into t1 values(4,'Gorgey'); insert into t1 values(5,'Michal'); insert into t1 values(6,'Michel'); insert into t1 values(7,'London'); insert into t1 values(8,'Egpt'); insert into t1 values(9,'Londan'); insert into t1 values(10,'Gorgy'); insert into t1 values(11,'Gorge');the results must be as following id word 1 London 2 Egypt 3 London 4 Gorey 5 Michal 6 Michal 7 London 8 Egypt 9 London 10 Gorey 11 GoreyNote: this is the results of soundex
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
UPDATE event_request SET REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi') WHERE eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
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?
Table LEAVE Column Type Null Description APP_NO Number(6,0) Not Null PK Leave Application Number ECN Number(6,0) Not Null FK Employee Code Number APP_Date Date Not Null Date of Application From_Date Date Not Null Date from which the leave starts TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved Remark VARCHAR2(100) Nullable Reason to be put if status is rejected [code]....
What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.
For example if 3 rows are inserted in the LEAVES table as INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null); INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');
Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'
I tried with the following trigger, but is compiling with a warning (not showing what the warning is)
CREATE OR REPLACE TRIGGER leaveentitle AFTER INSERT ON LEAVES FOR EACH ROW BEGIN UPDATE LEAVEENTITLE LVE SET LVE.SL_USED = SL_USED+(CASE WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED' THEN :NEW.NO_OF_DAYS SL_UNUSED=SL_ENTITLED - SL_USED ELSE 0 END), [code]....
Let's say that we have an order entry app and the price and quantity items are already populated. We now want to show the user the extended price (the product of the first two items). the extended price would automatically display after both the quantity and price items are entered. I'm assuming that would require some kind of client-side processing. I imagine server-side processing is simpler, so I'll lean in that direction. I'm sure we've all seen web sites that require you to click a button for the order totals to be updated. I'm okay with that approach, but I haven't quite put all of the pieces together. Here's what I came up with.
A region button was added to trigger the updating of the extended price. The button's "Action When Button Clicked" is to submit the page. Under "Page Processing", a process was created to do the calculation with the recipient of the product being a page item. From debug code in the procedure, I can see that the item containing the extended price was, indeed, modified. But the new value doesn't display in the field and the Session window shows that the value of the item didn't change.
I must be missing something. The debug code says the item value was updated. The Session window says it wasn't updated. What gives? What did I do wrong and what needs to be done to correct this?
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE: CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT ( CALENDAR_KEY INTEGER NOT NULL, DAY_TIME_KEY INTEGER NOT NULL, SITE_KEY NUMBER NOT NULL, RESERVATION_AGENT_KEY INTEGER NOT NULL, LOSS_CODE VARCHAR2(30) NOT NULL, PROP_ID VARCHAR2(5) NOT NULL, [code].....
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]......
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 a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
