SQL & PL/SQL :: Conditional Select - Query To Returns Results Based On Both City And Country Passed
Sep 17, 2010
Table A
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.
I'am having a query regarding population of a list item based on another selected list item that is whenever i select the state from the combo box all the city's under that state should be automatically populated.
I want to get a list of vendors based on country and campaign.I first get all vendors based on country then i subtract the vendors i shouldn´t see (the vendors i should not see minus those i should see).Below you see my query - is there a better way to get the same result?
3 tables vendor - vendorInformation vendorcountry_spec - A vendor can be located in different countries with different information. vendorcountry_campaign - In a country there are different campaigns. If there is a row in this table, only those with the campaignID should see the vendor else everyone in that country.
The query:
SELECT v.vendorID FROM vendor v, vendorcountry_spec vs WHERE v.VENDORID = vs.VENDORID AND v.status = 'A' AND vs.STATUS = 'A' AND vs.ISCORPORATE = 1 AND v.ISNOTHIGHLIGHTED = -1 AND vs.COUNTRYCODE = 'SE' GROUP BY v.sortNr, v.vendorID
I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.
Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID; SetPackageValueProc(PkgID,'YearlyBasePay',N); And I'd like to do something along the lines of this: SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
a tabular form with two "select list (query based LOV)" fields. The first one must affect elements in the second one. And so the second query based LOV field should has a "where attr1 = first_list_selected_value" in the query.
how to do a conditional range in a query. For example, I have two tables:
table A: a1 char(1) a2 char(1)
table B: b1 char(1) b2 int
In my query I want to range on a different value in the b2 field depending on what is in the fields in the A table. For example, if the a1 field is Y, I want to look up the record in B with a b2=20. If a2 is Y, I want to look up b2=5. Otherwise I want to look up b2=1. I attempted the following:
select b1 from b, a where b2=(if a1='Y' then 20; elsif a2='Y' then 5; else 1)
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 a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select x,y,z from mytable@dblink
returns 788,324 rows
while select x,y,z from mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
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 am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order" FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id LEFT JOIN order_details ON orders.order_id=order_details.order_id LEFT JOIN items ON order_details.item_id=items.item_id ORDER BY Customer_name
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as SELECT ctry, id, MAX(DECODE(tp,'EN', RN)) EN, MAX(DECODE(tp,'RN', RN)) RN, MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
We are running on Oracle 10g. The following script results in ORA-00928: missing SELECT keyword. what causes this error? Both 'select' statements when run by themselves, complete successfully.
[code]WITH A1 AS WITH A1 AS ( SELECT MIN (VAPS_RPT_INTV_DMSN.INTV_DT), VAPS_RPT_INTV_DMSN.RPT_ID, VAPS_RPT_INTV_DMSN.RPT_INTV_ID FROM APS.VAPS_RPT_INTV_DMSN, APS.VAPS_RPT_CL_INTV_DMSN WHERE APS.VAPS_RPT_INTV_DMSN.RPT_INTV_ID =
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION FROM AIMUSER.SEGMENT_JOBS WHERE SEGMENT_JOB_ID NOT IN (SELECT SEGMENT_JOB_ID FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ; [code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
