I got a requirement to check if a particular range of values are present , if few values missing then have to add null for the values. This has to be a SQL statement.
This is the table. the fiscal month id denotes that the values is from month Oct(20101000) to May (20110500). The fiscal year cycle for me is from OCT to SEP. Hence when we select this table, the select should retreive the below data
SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep WHERE ID =
[code].....
here is the completed query
select EAG.AUDIT_NUMBER Audit_Nbr, ( SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep
[code].....
when I try select from this query I get ORA-00904: "DATE1": invalid identifier.
the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
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'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select c.Event, c.code, count(1) as calls, total.total_count, count(1) / total.total_count * 100 as pct_of_total from table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query
select emp_id from employee where emp_id in (1, 2, 3, 4, 5);
It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.
& want output like below
EMP_ID 1 2 3 4 Not Found 5 Not Found
Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"
I need to check somewhere in the code as " Check the variable with in 20 varaiables , if not excute somes statements"
Example
a1=20,a2=30,a3=40......a20=200; (Like this way i have 20 varaibles with values) v1=150; if( v1<>a1 AND V1<> a2 AND V1 <> a3...... AND V1<>20) execute some insert block; End if;
But it's writting very diffcult as i need to compare with 20 varaiables.Was there any alternative sloution like taking some VARRAY and checking in IF condition like v1 NOT IN(Va) (Where 'Va' is like VARRAY)
Below is my query Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table
SELECT TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( '' AS VARCHAR(20)), NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC), TAB1.SUB_HEAD_CODE, DECODE( 'INFENG' ,CAST( '' AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC , TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC)
[code]...
select null ID,null crncy_code,TAB2.SCHEME_CODE , NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1';
I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition
var1 := {'A','B','C'}
and use in my statement like :-
Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?
I would like to use REGEXP_LIKE condition expression with '[:alnum:]' operator in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 for to check the string is alphanumeric or not.Why do i need: I have xinput field [RAW(24)] which not sure to contains always an alphanumeric string. We should use only when xinput field is an alphanumeric string.
x varchar2(50); x:=rtrim(ltrim(utl_raw.cast_to_varchar2(xinput)));
how should i use REGEXP_LIKE
select 'TRUE' from dual where REGEXP_LIKE( 'True', '[:alnum:]') select 'TRUE' from dual where REGEXP_LIKE( 'False%*^tt123.***', '[:alnum:]')
I'm passing some parameters from java code to a query as follows:
select * from table1 where col1 = 'yes' --want to make this part dynamic *$[variable]*
where the $[variable] = 'and col2 like ''some%'' and col3 = ''good'''
So this query is giving me an error saying that it cannot construct such query because i guess what it's trying to run is:
select * from table1 where col1 = 'yes' *'and col2 like ''some%'' and col3 = ''good'''*
I need to remove the first and last single quote... I tried using double quote for my string and replace all single quotes with empty but the "LIKE" function only works with single quotes (like 'some%')..
I also tried using:
select * from table1 where col1 = 'yes' and trim (both '''' from 'col2 like ''some%'' and col3 = ''good''')
but i get a "ORA-00920: invalid relational operator"...
I want to run multiple sql select command after loggin to sqlplus and need to pass the value to variable to check the condition.
When I am running the below script I am getting the below error ---------------------- SP2-0734: unknown command beginning "return :va..." - rest of line ignored. SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
I have a query with order by clause, which takes 30 sec to execute with order by clause. And if i remove the order by clause it executes within 1 sec.
The column in the order by condition has index. but when i see Explain plan output. it doesn't show this index is being used. I tried to execute query with INDEX hint but still explain plan is not showing this index.
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
