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?
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?
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,
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.
SELECT field1, COUNT(x) AS COUNT FROM my_table GROUP BY field1;
For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.
So if I have the following: ABC1 | 5 ABC2 | 10 XYZ1 | 3
I want results like this: ABC | 15 XYZ1 | 3
I've tried using some case statements like
SELECT CASE WHEN field1 LIKE 'ABC%' THEN 'ABC' ELSE field1 END AS field1, COUNT(x) AS COUNT FROM my_table GROUP BY CASE WHEN field1 LIKE 'ABC%' THEN 'ABC' ELSE field1 END;
but this just gives me ABC | 5 ABC | 10 XYZ1 | 3
How can I combine record 1 and 2 from the last record set example above?
I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data. Consider the following:
CREATE OR REPLACE PROCEDURE TESTSPROC2(c_test out sys_refcursor) AS BEGIN open c_test for select * from test_table order by id_no; END TESTSPROC2;
to view this I need something on the order of:
DECLARE cc sys_refcursor; r cc%rowtype; BEGIN TESTSPROC2(C_TEST => cc); loop fetch cc into r; exit when cc%notfound; DBMS_OUTPUT.PUT_LINE('C_TEST = ' || r.data_element); end loop; close cc; END;
But this is weakly typed so I get all sorts of errors associated with the row definition of line 3. create the record based on the table (something like: r test_table%rowtype).
What I really want is a generic reader than can be ported around to output any sproc I put together.
On a more generic note, why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:
CREATE OR REPLACE PROCEDURE TESTSPROC2 AS select * from test_table order by id_no; GO
and viola, a nice result set spits out in Query Analyzer (or a .net application).
I am trying to create a csv file with summarized data. We have a huge table with claim details that is constantly being updated. I am pulling a subset of records that match my criteria into a tempory table (not technically an Oracle temporary table, a regular table that will only exist until I drop it when I am done). This table has multiple entries per claim with different effective dates, paid dates and amounts paid. The result file needs to have one entry per claim with the oldest effective and paid dates and the total of all of the amounts paid on that claim.
Originally I was doing create table new_table as select claim_nbr,other data...,min(ymdeff),min(ymdpaid),sum(amtpay) from my_table group by claim_nbr,other data...
If I ran a select sum(amtpay) from my_table and select sum(amtpay) from new_table I was not getting the same results... If I ran select count (*) from (select distinct claim_nbr from my_table) and select count (*) from (select distinct claim_nbr from new_table) or select count (*) from new_table I was getting the same number of rows. So I wasn't completely losing claims from one table to the next, just some of the details. So, I tried running this:
select * from (select claim_nbr,sum(amtpay) paysum from my_table group by claim_nbr order by claim_nbr) m, (select claim_nbr,sum(amtpay) paysum from new_table group by claim_nbr order by claim_nbr) n where m.claim_nbr = n.claim_nbr and m.paysum <> n.paysum;
It came back with the claim number causing the issue. I looked at all the entries in my_table for that claim and every field was identical except the ymdeff, ymdpaid and amtpay. There were 4 records in my_table however the amtpay in new_table was only a sum of 2 of the records... I our admin look over my shoulder to see what was wrong and they wanted me to recreate new_table. So I dropped new_table and ran the exact same SQL to recreate the table. The number of distinct claim numbers was still the same in both tables and the sum of new_table was off but not by the same amount. I ran my comparison to see which claim was off and now there were two claims where the totals didn't match and neither were the same as the claim that was wrong that first time. We dropped new_table and recreated it several times and every time we got different results... No one else knows the name of my_table so no one was messing with it at the same time plus the sum of amtpay in my_table always comes back the same.
Our admin said he thought he remembered there being something "funny" with the min function sometime so he had me remove those fields. Ran the query several times and the total came out correct each time. Well I still need the dates so I came up with another way (very convoluted) using subqueries and ranking. It seemed to work at first then it started losing random numbers of claims (fewer rows in new_table than distinct claims in my_table) or keeping all the claims but dropping detail lines like I had using the min functions.
Here is the backwards way around using min that drops whole claims sometimes but works fine other times:
CREATE table new_table as (select claim_nbr,other data...,amtpay,ymdeff,ymdpaid from (select claim_nbr,other data... ,sum(amtpay) amtpay from my_table group by claim_nbr,other data...
select distinct event_number from events_total WHERE event_id = 16395493 minus select distinct event_number from event_details_ford WHERE event_id = 16395493
result of which is : 6L2Z-7861693-AAC 6L2Z-7862187-CAC
i want to put this in dynamic sql where clause :
where event_number in ('6L2Z-7861693-AAC','6L2Z-7862187-CAC'). and if the result of the query is only one number, then where event_number in (6L2Z-7861693-AAC) result of the query can be NULL also. but i think i can use IF condition for "SELECT ..WHERE event_number in " query
so how can i put the results of query in one line, so that i can use it in where clause.
While I was debugging the code of already existing application (Oracle apps fnd_global package), I copied a package to make changes in it.
It was strange that with Original package fnd_global I am getting below error when I call it over database link:
"ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls."
PACKAGE CALLS ARE AS FOLLOWS:
Database 1 (Oracle Apps LSH module) CUSTOM procedure to call CDR_PUB_API_GVA has CDR_PUB_API_GVA INITIALIZATION BLOCK OF CDR_PUB_API_GVA CALLS Fnd_Global.apps_initialize (when copied to fnd_global1, then it's call don't throw any error)
Database 2 (External application) has dblink to Database1 calls custom procedure over dblink
While the copied package is not throwing any error (I copied fnd_global to fnd_globa1).
I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.
Emplid Effdt Effseq
10001 '01-JAN-99' 0
10001 '01-JUL-11' 0
10001 '01-JUL-11' 1
10001 '01-JUL-11' 2
10001 '01-JUL-12' 3
What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production "CORE 11.2.0.1.0 Production" TNS for HPUX: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
I have a problem related with hierarchical queries, I have this tree. - a / b c / | d e fI need to calculate an average per node but using always the child's results for example: Only leafs will have values:
d = 10 e = 20 f = 30 c = 40So the expected result is: d = 10 e = 20 f = 30 c = 40 b = (10(d) + 20(e) + 30(f)) / 3 (number of child nodes) = 20 a = (20(b) + 40(c)) / 2 (number of child nodes) = 30.
I have tried with recursive queries, hierarchical queries, I guess it's possible with model too but I can't produce the exact results that I need. Maybe in fact there is a very simple solution but I cannot figure it.
Here is an auxiliary WITH that you can use to start your tests: WITH tree AS ( SELECT 'd' child, 'b' parent FROM dual UNION ALL SELECT 'e' child, 'b' parent FROM dual UNION ALL SELECT 'f' child, 'b' parent FROM dual UNION ALL SELECT 'b' child, 'a' parent FROM dual UNION ALL SELECT 'c' child, 'a' parent FROM dual UNION ALL SELECT 'a' child, null parent FROM dual [code]....
I have three SQLS which I am running using "Union"
e.g
select col1,count(*) from table where type ='parts' group by col1 union select col1,count(*) from table where type ='consumables' group by col1 union select 'Total',count(*) from table group by 'Total'
I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!
I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:
select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA where PLLA.po_line_id = PLA.po_line_id and PLLA.attribute4 is not null
So my output is something like this:
RCE12 This is an item for AUL1 RCE13 This is an item for PWEILL RCE14 This is an item for AUL1
I just want either the RCE12 or RCE14 record and not both since they both have the same description.
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER) FROM CPMA.CPMA CPMA WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate') GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM') ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
