SQL & PL/SQL :: Return Results Based On 2 Columns?
Jul 15, 2013
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.
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.
Is there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).
Example case:
Procedure have multiple ref cursors as out parameters. .... ... if exp1=exp2 then open v_ref_var1 for select ...from ... ; end; [code]..........
If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.
EMPLOYEEIDNUMBER(12,0) PUNCHDTM DATE TIMEZONEIDNUMBER(12,0)
I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
Id Country city 1 US 2 US Boston 3 Boston 4 US Newyork 5 London 6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row If i pass country null and city Boston it should return row3 If i pass country UK and city Boston it should return row3 If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
create type emp_obj_dtl as OBJECT (ename varchar2(50),mgr NUMBER) create type emp_dtl_obj_typ as TABLE of emp_obj_dtl
Using the these object i have created on function as
CREATE OR REPLACE FUNCTION emp_test_func (peno NUMBER) RETURN emp_dtl_obj_typ AS lv_emp_dtl emp_dtl_obj_typ := emp_dtl_obj_typ (); BEGIN SELECT emp_dtl_obj_typ(emp_obj_dtl (ename, mgr)) INTO lv_emp_dtl FROM emp WHERE empno = peno;
RETURN lv_emp_dtl; END;
Now if i am executing query as
SELECT empno, emp_test_func (empno) emp_dtls FROM emp
I have a table with Column A, B, C. I want to write a query to retrieve the top row of A, B combination. i.e, for every unique value of A,B combination I want the row having highest value for C. I tried using rank() function but am not able to get the top row with combination of A,B.
I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.
Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...
Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...
Quote: SELECT REGION, SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1, SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,
[code]...
My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...
SQL> SELECT FISCAL_TIME_ID, DATA_ID, M_VALUE, 2 SUM(m_value) OVER (PARTITION BY fiscal_time_id, data_id 3 ORDER BY FISCAL_TIME_ID) AS YTD_VALUE 4 from test11;
Desc Table A (account) Account1_id Account2_id name, empid
Table B (Bill ) BillNo Advertiserid agencyid total vvalue
I need to pick up total value from table B where the unique combination of advertiser-Agency id is the same as the given account1_id -Account2_id combination in table A for each employee id.
In other words my output should be like
Empid | Account_id (should be same as advertiserid)| Account2_id (same as agencyid) | sum(total_value) for this adv-agency combination.....
objective: Get the total value from table B for each unique account1-account2 combination (advertiser-agency in other words) .
I am not sure if I should use a correlated subquery or how to handle the situation....Right now I am just checking the two columns separately like this:
select.......from a,b where b.advertiser_id = a.account1_id and b.agencyid = b.account2id
Is it correct to do so? I have a feeling that I am missing something if I join them separately like this.
My issue is with a calculation using columns as variables. below is the code to create and attached is the code (INSERT_data.txt) to fill-in the required tables....
what's required here?
I need to update (through update or simply create new table statement)
BPF(i), BPC(i), BPY(i) of table root_tbl (A) based on InF_IDX, InC_IDX & InY_IDX and OutF_IDX, OutC_IDX & OutY_IDX using BPF(i), BPC(i), BPY(i) from table pvt_vectors_tbl(B).
Join Clause: A.P_NODE=B..P_NODE and A.C_NODE=B.C_NODE and A.P_NODE_Date=B.P_NODE_Date and A.C_NODE_Date=B.C_NODE_Date
the formula are:
BPF(i): if A.InF_IDX<=A.OutF_IDX then FOR i=1 to A.OutF_IDX+1 if i<InF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(OutF_IDX + i - InF_IDX); else A.BPF(i)=0; i=i+1; else FOR i=1 to A.InF_IDX+1 if i<OutF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(InF_IDX + i - OutF_IDX); else A.BPF(i)=0; i=i+1; idem for BPc and BPY.
in real word:
* root_tbl table has ~2 million records, 50 BPF(i) columns, 50 BPC(i) columns and ~475 BPY(i) columns * pvt_vectors_tbl has ~50.000 records
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I want to delete dups from a table based on 3 columns
with sample_table as ( select '101' as ID1, '201' as ID2, '4' as weight from dual union all select '101' as ID1, '201' as ID2, '5' as weight from dual union all select '105' as ID1, '205' as ID2, '6' as weight from dual union all select '105' as ID1, '205' as ID2, '6' as weight from dual union all [code].........
Desired Output
with sample_table as ( select '101' as ID1, '201' as ID2, '5' as weight from dual union all select '105' as ID1, '205' as ID2, '6' as weight from dual union all select '110' as ID1, '215' as ID2, '9' as weight from dual ) select * from sample_table
when parameter is YES, column having values when Parameter is NO, column having no values
In this scenario, how can i skip the column with space when parameter is NO because the column exist in middle of columns in report and Present with space when parameter is YES.
I've got a report with two lov's, where the user is able to change the lov value and submit it. After submit the status of the item P100_status will be changed in Disable. Based on this value the lov's must be disables with apex_disabled. How can I disable these columns based on the value of P100_status?
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty, a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':' || b.mnfst_qty FROM vw_acao_critical a JOIN vw_acao_critical b ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr [code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
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
