I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
I have a page with two regions the top region is an html region where users enter query criteria and generate an interactive report in the second region. The second region only displays if there are results from the query in the first region.
I would like to display a "No Results Found" message rather than a blank screen if there are no results found. However, I don't know exactly how to do that. I tried to do it in the Interactive Report region, however, once I added the Conditional display to only when exists rows in the Interactive Report from the query criteria.
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME) WHEN 'ALERT' THEN SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A WHERE A.ALERT_ID = IN_PARENT_NODE_ID; INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
I'm starting to Oracle and am having a doubt, need to check if a variable is null if I need to make an appointment if you do not need to make another appointment. I thought I was doing fine, but Oracle is pointing this error: "ORA-00900: invalid SQL statement."
- Case
CASE WHEN 1 = 1 THEN select * from BANANA WHEN 1 = 2
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
Im trying to make a query to get the grades of students, I already figured out how to get the average from the exams. Now I would like to make like a select case or if statements to get the actual grade.
Something like this: average > 89 then grade = 'A' average > 79 then grade = 'B' average > 69 then grade = 'C' average > 59 then grade = 'D' else grade = 'F'
I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.
call get_all_customer_orgs;
The get_all_customer_orgs procedure is defined as.
create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor) is begin open p_recordset for select * from customerorgs; end get_all_customer_orgs;
I don't understand what the invalid character could be.
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex 2664good stuff310069< because pbk_price is like 67 from that period 2664good stuff310071< because pbk_price is like 50 from other period
and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.
I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:
SELECT trunc(R_DUR/6)*5+1 as range_start_rdur, trunc(R_DUR/6)*5+5 range_end_rdur, sum(noofan) as no_of_an, sum(sumofrdur) as sum_of_rdur, sum(sumofchdur) as sum_of_chdur, [Code] ...........
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system. Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual union all select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual) select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value 01-01-1970 31-12-9999 30
I tried below query
SELECT * FROM ( with t1 as ( select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual union all select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
SELECT audittimestamp + interval (SELECT EXTRACT(TIMEZONE_HOUR FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work): select (case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product from tbl_product where productid= '123'; If one record exists it should produce: 'My Widget' (or whatever) If zero records exist it should produce: 'N/A'
select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and ( employee_status in(Case employee_status when {?Status}=1 then 'A' when {?Status}= 2 then 'T' When {?Status}= 3 then 'A'||','||'T')) OR ( end_date >= {?START_DATE} AND end_date <= {?END_DATE} ) )
Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.
4 records for employee_status 'A' 3 RECORDS for employee_status 'T' 7 records for employee_status 'A' AND 'T'
I am facing a problem while retrieving data from table using DECODE/CASE.
Table: PARAM_MSTR
MIN_VALMAX_VALPARAM_CODE DESCRIPTION DATATYPE AB1000 HARD PARAMETERTEXT CN1000 SOFT PARAMETERTEXT 0501001 CRYSTAL PARAMETERNUMBER 512001001 STONE PARAMETERNUMBER
Now I want to get the parameter description based upon the PARAM_CODE and a value passed which should be in range of MIN_VAL and MAX_VAL. Means when I pass PARAM_CODE=1000 and :parameter=A, then it should check the DATATYPE of the PARAM_CODE, in our case it is 'TEXT' so it should check the passed value between MIN_VAL and MAX_VAL like
:parameter BETWEEN MIN_VAL AND MAX_VAL and should return 'HARD PARAMETER'. If I pass PARAM_CODE=1001, then the DATA_TYPE is 'NUMBER', so it will check the :parameter value as Number. Like :parameter BETWEEN to_number(MIN_VAL) AND to_number(MAX_VAL)
For example: PARAM_CODE :parametr Result 1000 A HARD PARAMETER 1000 C SOFT PARAMETER 1000 P NULL 1001 25 CRYSTAL PARAMETER 1001 99 STONE PARAMETER 1001 201 NULL
I have written a query using DECODE and CASE statement but it is not working properly.
SELECT * FROM param_mstr WHERE PARAM_CODE=1000 AND :parameter BETWEEN DECODE(DATATYPE,'NUMBER',CAST(MIN_VAL as NUMBER),MIN_VAL)AND DECODE(DATATYPE,'NUMBER',CAST(MAX_VAL as NUMBER),MAX_VAL)
Can we use the sequence.nextval,sequence.currval inside case block.
CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER); INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL); INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL); CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;
Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)
I have tried like this
UPDATE EQUALITY_TEST SET SEQ= CASE WHEN NUM1=NUM2 THEN SEQ_TEMP.NEXTVAL ELSE SEQ_TEMP.CURRVAL END ; SELECT * FROM EQUALITY_TEST;
