I only want get one record with all columns, only have one clause MAX(END_TIME) But the other column have difference value. when i use MAX(END_TIME) and GROUP BY USER_ID,PROCESS_ID,CASES,... the sql didnot give one record, It give many records
ID AMOUNT ID_TYPE APPROVE_FLAG A1 2 A N B1 100 B N A2 3 A N A3 100 A Y
The Select Query should be..All the records should be considered and if the (amount is greater than 50 and approve_flag is N )then except that records all records should be considered.The output should be 3 rows; 2nd row (B1) should not come. writing a select statement for the above conditions.
I want to use Analytical function instead of group by clause for below query..
select CASE WHEN ADMT.SOURCESYSTEM ='CLU' THEN COUNT(ADMT.TOTAL_COUNT)*5 ELSE COUNT(ADMT.TOTAL_COUNT) END TOTAL_COUNT from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT group by ADMT.SOURCESYSTEM
I am trying to use decode funtion with GROUP BY ROLLUP.
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB)); SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB)); But when i use Decode funtion so that i can combine above two scenarios it is not working SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
My requirement is Data from a TableA has to be provided as an overall view
TABLEA ID ENTITY REQ_FLG PAR_FLG EXT_FLG CONV_1 ACCNT Y Y Y CONV_1 PROD Y Y N CONV_1 ADDR Y N N CONV_2 DID Y N N CONV_2 ORDER Y N N
Required to show the data in report as
ID Expand View_Report Populate ENTITY QRY_STATUS CONV_1 Expand Report Populate ACCNT Y Y Y PROD Y Y Y ADDR Y N N CONV_2 Expand Report Populate DID Y N N ORDER Y N N
Where "Expand", "Report", "Populate" are provided as Hard coded values in query.
Sample Query. SELECT ID ,'Expand' AS EXPAND ,'Report' AS VIEW_REPORT , 'Populate / Reset' AS POP , DECODE(MN_TBL.ENTITY,NULL,NULL,ENTITY) AS ENTITY , REQ_FLG || ' ' || PAR_FLG || ' ' || EXT_FLG AS QRY_STATUS FROM TABLEA GROUP BY GROUPING SETS ((ID), (ENTITY, REQ_FLG , PAR_FLG , EXT_FLG )) ORDER BY CONVERSION_ID, ENTITY
Above query works fine, where single ID is present
ID Expand View_Report Populate ENTITY QRY_STATUS CONV_1 Expand Report Populate ACCNT Y Y Y PROD Y Y Y ADDR Y N N
But when more than one ID is present the entire thing collapses
I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.
I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.
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 table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as
select asigno,count(*) from angdata77 group by asigno order by asigno;
Is there any other query for retrieving the data from angdata77
formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
I have a report with 2 groups Gheader and Glines.The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.However I only want to select this in the lines query so I do not get repeating records at the header query.
Actually I have one query which contains two parameters from_Date and To_date.
if user pass the values for the parameter like 1-dec-2010 to 30-dec-2010 query will works fine and it fetches the values according to the query selected. but now I am going to add one more parameter, the value is 01-apr-2010 now my query should run for 01-apr-2010 to 30-dec-2010.
Here my question is how should I make this scenario with only one query and at the same time the query should work for to scenarios.
I have 11.1.0.7 instance running and got more 300G of storage available for the database. I would like to know what are the steps to add these disks/storage to the existing DATA asm disk group?
I'm trying to debug this function to get the desired results. See attachment for the function code and the test data insert script.
----Create Test Table CREATE TABLE VC_WORKINGDAYS ( WK_ID number NUMBER(10,0), WK_DATE DATE, );
-- Insert test Data INSERT INTO VC_WORKINGDAYS_1 VALUES (308, '25-MAR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (316, '06-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (324, '18-APR-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (332, '03-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (340, '13-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (348, '25-MAY-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (356, '06-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (364, '16-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (372, '28-JUN-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (380, '08-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (388, '20-JUL-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (396, '01-AUG-11'); INSERT INTO VC_WORKINGDAYS_1 VALUES (404, '11-AUG-11');
SQL ----- Result Should be WHY SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),1)) FROM DUAL 14/JUL/10 15/JUL/10 is 1 working day from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),2)) FROM DUAL 14/JUL/10 16/JUL/10 is 2 working days from today SELECT (VC_CALC_WD_DATE(LAST_DAY(TRUNC(SYSDATE)),3)) FROM DUAL 14/JUL/10 19/JUL/10 is 3 working days from today
Attached File(s)
create_Function.zip ( 6.39K ) Number of downloads: 1
Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.
Table name: mr_test Columns: id number seo blob
CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2 IS len NUMBER;
[Code]....
ORA-01489: result of string concatenation is too long
when I replcae the
"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;" with SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id; myvar1 := myvar1||myvar; ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.
So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.
with xx as( select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual ) select UTL_URL.unescape(col) x1, regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2 from [code].......
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD( iQIFlag NUMBER(1), iIPFlag NUMBER(1), iRiskIndicator NUMBER(1), iDenominator NUMBER(8), iNumerator NUMBER(8) [code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE f2_data qi_test.typ_qi_data_tab; BEGIN EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;' USING OUT f2_data; [code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
