Current Position:Home > How to use group function in insert or update

How to use group function in insert or update

Update:10-11Source: network consolidation
Advertisement
Hai All
How can we use group function in insert or update statement
I am generating an attendance so i have different set of timing for example
0800,1200,1230, 1700 and i need to insert into these data into table with min value to intime and max value to
outtime and othere to inertval time in or out
Pls tell me with some example
For example
For INSERT
insert into T2 (barcode,empcode,intime,attend_date)
               values(r2.cardn,r2.enpno,MIN(r2.ptime),r2.pdate);
For UPDATE
update dail_att set outtime= MAX(r2.ptime) where empcode=r2.enpno and barcode=r2.cardn and
attend_date=r2.pdate;
Here instead of where i need to use having so pls tell how to use
Thanks & Regards
Srikkanth.M

The Best Answer

Advertisement
Hai Man
R2 is not a table name its a record
Let me explain clearly
I have to generate daily attendance for lot of employees So i have two table t1 and t2
T1 consist of three column empno,date,time
T2 consist of empno,name,date,intime,outtime,intrin,introut
So now i need to give the T1 Min value Of time to T2 Intime and T1 Max value of Time to T2 Outtime fields so there so many records while i am using
max(time) it gives the max value of all so i seperated by group function so now i have an error in subquery ie it is an single row subquery so i need to use multiple row subquery how i can use multiple row subquery in update statement
Thanks In Advance
Srikkanth.M
  • How to use group function in insert or update Update:10-11

    Hai All How can we use group function in insert or update statement I am generating an attendance so i have different set of timing for example 0800,1200,1230, 1700 and i need to insert into these data into table with min value to intime and max valu

  • Error while using group function Update:10-11

    Oracle forms6i Hai While i am compile my coding it compile successfully, but when i tried to executes i shows error in group function my coding is if (cnt<>0 ) then select BARCODE,INTIME,OUTTIME into today_bar,today_in,today_out from dail_att where

  • Nested group function without group xmlagg Update:11-30

    I am getting nested group function without group by xmlagg when using the xmlagg function inside another xmlagg function. Find the table structure and sample data here, CREATE TABLE "TEST_TABLE"    ("KEY" NUMBER(20,0),     "NAME&q

  • Getting an error - group function not allowed here Update:11-30

    Below is the merge statement... getting an error - group function not allowed here WHY???? merge into summary using (select a.user_id, min(a.start_time_utc), max(a.end_time_utc), sum(a.duration_seconds), /*total_upload total_download total_traffic,*/

  • ORA-00937:not a single-group group function|ORA-06512:at"schema.procedure n Update:11-30

    ORA-00937:not a single-group group function|ORA-06512:at"schema.procedure name)? The details of this procedure are the emp table is used in the emp1 which is shown in line 19 1 DECLARE 2 cur_emp sys_refcursor; 3 TYPE t_tab IS TABLE OF emp%ROWTYPE; 4

  • Nested Group Function without Group By Problem Update:11-30

    Hey everyone, I have 3 tables as below: TABLES ITEM (Item_no, Item_price, desc) DeliveryItem (delivery_no, item_no, quantity) Delivery (delivery_no, delivery_date) SELECT desc, MAX(SUM(quantity)) FROM DeliveryItem, Item, Delivery WHERE Item.item_no =

  • Using functions in insert statement Update:10-11

    Hi, I'm trying to insert data into an object view and I have an object date_object in one of the tables i'm inserting from. Here is the specification for date_object: create or replace type date_object as object      (quarter     number,      year   

  • PL/SQL equivalent of T-SQL - "group function is not allowed here" Update:10-11

    Hi all, hope someone can give me a hand as I'm pretty stuck! I have been trying to convert some MS SQL Server T-SQL statements into Oracle PL/SQL and am stuck on the below one: SELECT CA.AssessmentID, (SELECT ProductName + ISNULL(' - ' + PrincipalBen

  • Is there a way to create a group function? Update:10-11

    I want to create a group function like max,sum,count for a pl/sgl function. This group function will be something like max (let's call it max2) but it will return the second highest value of a group. Any ideas anyone?Since you might not completely un

  • How to create a user_defined grouping function? Update:10-11

    I need to create a user_defined function and use it in my sql statement. I know the user_defined function can be used anywhere the build-in function can be used.In oracle,there are grouping functions. How can I create a user_defined grouping function