Home » SQL & PL/SQL » SQL & PL/SQL » How to get either of two records?
How to get either of two records? [message #225100] Sat, 17 March 2007 08:42 Go to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Hi,
I have a table with following data.

RecordId PolicyId StateCode ZipCodeStart ZipCodeEnd
60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369
40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL
40041 4 IL
20050 2 AK
40050 4 AK 995 995

My question is for a given zip code(say 603) in IL state it should get records(40005,60005,20040) and for the zip code (say 606) in IL state it should return (40041,20040). Similarly for the zip code 369 in AL state it should return 60040 and 10005.

Thanks in advance
Saurabh
Re: How to get either of two records? [message #225101 is a reply to message #225100] Sat, 17 March 2007 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How to get either of two records?
Learn how to complete your own homework assignment.
For which school, class & instructor & when is it due?
Why is this Basic question need a SQL Expert to answer?

[Updated on: Sat, 17 March 2007 09:02] by Moderator

Report message to a moderator

Re: How to get either of two records? [message #225186 is a reply to message #225101] Mon, 19 March 2007 00:25 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
No Message Body
Re: How to get either of two records? [message #225215 is a reply to message #225186] Mon, 19 March 2007 03:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SELECT * FROM TABLE
WHERE statecode = :statecode
AND   :zipcode BETWEEN nvl(zipcodestart,:zipcode) AND (zipcodeend,:zipcode);

It'll perform badly on any sizable volume of data, but it's good enought for government work and homework.
Re: How to get either of two records? [message #225573 is a reply to message #225215] Tue, 20 March 2007 06:53 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
No it is not woeking in MS-ACCESS. I have tried

select RecordId,PolicyId,ZipCodeStart,ZipCodeEnd,AreaFactor
from PolicyStates
where 862 between ZipCodeStart and ZipCodeEnd
union all
select RecordId,PolicyId,ZipCodeStart,ZipCodeEnd,AreaFactor
from PolicyStates
where ZipCodeStart is null
and ZipCodeEnd is null
and 0 = ( select count(*) from PolicyStates
where 862 between ZipCodeStart and ZipCodeEnd )

but this is also not working properly. Please help.

-Saurabh
Re: How to get either of two records? [message #225577 is a reply to message #225573] Tue, 20 March 2007 07:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does it mean, "it is not working properly"? Did you get any error, or are you just not satisfied with the result?

Besides,
saurabh_12_23
it is not working in MS-ACCESS

This is Oracle forum, not MS Access one.
Re: How to get either of two records? [message #225583 is a reply to message #225577] Tue, 20 March 2007 07:29 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
I mean First is not working at all and showing error as Syntax Error (missing operator) in query expression; and second query is returning wrong data for the testing zipCodes 603, 606 and 369.

Please look properly in the question.
Re: How to get either of two records? [message #225590 is a reply to message #225100] Tue, 20 March 2007 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
saurabh_12_23 wrote on Sat, 17 March 2007 09:42
Hi,
I have a table with following data.

RecordId PolicyId StateCode ZipCodeStart ZipCodeEnd
60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369
40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL
40041 4 IL
20050 2 AK
40050 4 AK 995 995

My question is for a given zip code(say 603) in IL state it should get records(40005,60005,20040) and for the zip code (say 606) in IL state it should return (40041,20040). Similarly for the zip code 369 in AL state it should return 60040 and 10005.



Explain why you think your results should be what you say they are. I don't see it.
Re: How to get either of two records? [message #225595 is a reply to message #225583] Tue, 20 March 2007 07:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Did you read at your first post properly ? Try reading it for yourself and ask yourself whether the data supplied and the data expected are correct . I don't think so.

Supplied Data is

60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369
40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL
40041 4 IL
20050 2 AK
40050 4 AK 995 995

Why do you think for zip code 603 you should get (40005, 60005, 20040). If you see in your data 20040 zipcodestart and zipcodeend are blank. Why do you think oracle will pick that record ?

Also next time when you post a message if you could post the query and the error together it will be very useful for anybody to who looks at the problem and give a solution to you rather than saying the famour phrase

"My car is not starting please help me..."

P.S : Always ask yourself atleast 10 times why it is not working, try different ways to make it to work and still unsucessful, ask for help. By doing this you will learn more and you will not forget it easily because many of us learn by mistakes. I am not lecturing, i am telling you the fact.
Re: How to get either of two records? [message #225597 is a reply to message #225583] Tue, 20 March 2007 07:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If "Syntax Error (missing operator)" refers to JRowbottoms post, it is nothing much - lapsus calami (he forgot to write name of the NVL function). You could have seen that and fixed it.

Second query returns wrong data. How am I supposed to know that? I believe you wanted all of us to write our own "CREATE TABLE policy_states" and "INSERT INTO policy_states" statements in order to be able to run your query. Well, as far as I'm concerned, you were wrong. I'm not in the mood at the moment. It is YOU who should have provided those information, just as it is written in the Sticky topic under "if you open a new topic consider the following" section which says
  • Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.
  • Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
  • Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
  • Provide your expected result set.

Finally, look properly at the last line of my previous message: you are talking about MS Access, and I'm telling you that this is Oracle forum, NOT MS Access forum. If you apply Oracle-specific solutions to Access, you can't really expect it to work.
Re: How to get either of two records? [message #225603 is a reply to message #225590] Tue, 20 March 2007 08:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Mea culpa - I forgot the second NVL

Yes, looking at it, I think there are definitely some mistakes in the requested data.
Why should (AL,369) return no rows with blank zip codes, but (IL,603) should return one (but not both) of the rows with blank zip codes.

My code will return all the rows which have the correct state, and either have no zip codes, or have zip codes with the correct range for the specified zip code:
SQL> SELECT * FROM zip_test
  2  WHERE statecode = '&statecode'
  3  AND   &&zipcode BETWEEN NVL(zipcodestart,&&zipcode) AND NVL(zipcodeend,&&zipcode)
  4  /
Enter value for statecode: IL
Enter value for zipcode: 603

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     40005          4 IL          600        605
     60005          6 IL          600        605
     20040          2 IL
     40041          4 IL

SQL> undefine statecode
SQL> undefine zipcode
SQL> SELECT * FROM zip_test
  2  WHERE statecode = '&statecode'
  3  AND   &&zipcode BETWEEN NVL(zipcodestart,&&zipcode) AND NVL(zipcodeend,&&zipcode);
Enter value for statecode: IL
Enter value for zipcode: 606

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     20040          2 IL
     40041          4 IL

SQL> undefine statecode
SQL> undefine zipcode
SQL> SELECT * FROM zip_test
  2  WHERE statecode = '&statecode'
  3  AND   &&zipcode BETWEEN NVL(zipcodestart,&&zipcode) AND NVL(zipcodeend,&&zipcode);
Enter value for statecode: AL
Enter value for zipcode: 369

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     60040          6 AL          369        369
     10005          1 AL
     20004          2 AL          369        369


If this isn't correct, can you explain clearly why not, and what it should be.
Re: How to get either of two records? [message #225615 is a reply to message #225603] Tue, 20 March 2007 09:37 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member

My bad; I have written last line wrong. I appologize; Same data as previous.

RecordId PolicyId StateCode ZipCodeStart ZipCodeEnd
60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369
40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL
40041 4 IL
20050 2 AK
40050 4 AK 995 995

My question is for a given zip code(say 603) in IL state it should get records(40005,60005,20040) and for the zip code (say 606) in IL state it should return (40041,20040). Similarly for the zip code 369 in AL state it should return 60040 and 10005 and 20004.
Re: How to get either of two records? [message #225621 is a reply to message #225615] Tue, 20 March 2007 10:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This still doesn't explain the IL data.

Should (IL,603) return (40005,60005,20040,40041) or (40005,60005)
Re: How to get either of two records? [message #225681 is a reply to message #225621] Tue, 20 March 2007 23:03 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
(IL,603) should return (40005,60005,20040) bcoz 603 is available in IL for all policies.
The logic behind the scene is IF FIRST CHOICE IS NOT AVAILABLE THEN SELECT THE SECOND CHOICE.

And if supplied zipcode is not in the available range then I have to multiply an policy amount by a factor 2 otherwise 1.

In procedural programing the logic is:

while length of records
if policy is available for the zip code then
amountFactor=1;
else if policy is not available for the zip code but available for the state then
amountFactor=2;
end if
end while


Any help will be greatly appreciated.
Re: How to get either of two records? [message #225780 is a reply to message #225681] Wed, 21 March 2007 07:49 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
So, it took you 6 messages to explain finally how you get the output you expect. OK, I will have an answer for you on May 1st.
Re: How to get either of two records? [message #225797 is a reply to message #225780] Wed, 21 March 2007 09:31 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
It is important for my assignment; kindly look forward in this.

Thank you for understanding.
Re: How to get either of two records? [message #225804 is a reply to message #225797] Wed, 21 March 2007 09:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My script
VAR statecode VARCHAR2(2)
VAR zipcode   NUMBER

PROMPT For 'IL' and 603
EXEC :statecode := 'IL';
EXEC :zipcode   := 603


WITH yourtable AS
 (
  SELECT 60040 RecordId, 6 PolicyId , 'AL' StateCode , 369  ZipCodeStart , 369  ZipCodeEnd FROM dual UNION ALL
  SELECT 10005 RecordId, 1 PolicyId , 'AL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 20004 RecordId, 2 PolicyId , 'AL' StateCode , 369  ZipCodeStart , 369  ZipCodeEnd FROM dual UNION ALL
  SELECT 40005 RecordId, 4 PolicyId , 'IL' StateCode , 600  ZipCodeStart , 605  ZipCodeEnd FROM dual UNION ALL
  SELECT 60005 RecordId, 6 PolicyId , 'IL' StateCode , 600  ZipCodeStart , 605  ZipCodeEnd FROM dual UNION ALL
  SELECT 20040 RecordId, 2 PolicyId , 'IL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 40041 RecordId, 4 PolicyId , 'IL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 20050 RecordId, 2 PolicyId , 'AK' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 40050 RecordId, 4 PolicyId , 'AK' StateCode , 995  ZipCodeStart , 995  ZipCodeEnd FROM dual 
 )
SELECT min(recordid) recordid
FROM  yourtable
WHERE  statecode = :statecode
AND    :zipcode BETWEEN nvl(zipcodestart, :zipcode) AND nvl(zipcodeend, :zipcode)
GROUP BY policyid
/

PROMPT For 'AL' and 369
EXEC :statecode := 'AL'
EXEC :zipcode   := 369

WITH yourtable AS
 (
  SELECT 60040 RecordId, 6 PolicyId , 'AL' StateCode , 369  ZipCodeStart , 369  ZipCodeEnd FROM dual UNION ALL
  SELECT 10005 RecordId, 1 PolicyId , 'AL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 20004 RecordId, 2 PolicyId , 'AL' StateCode , 369  ZipCodeStart , 369  ZipCodeEnd FROM dual UNION ALL
  SELECT 40005 RecordId, 4 PolicyId , 'IL' StateCode , 600  ZipCodeStart , 605  ZipCodeEnd FROM dual UNION ALL
  SELECT 60005 RecordId, 6 PolicyId , 'IL' StateCode , 600  ZipCodeStart , 605  ZipCodeEnd FROM dual UNION ALL
  SELECT 20040 RecordId, 2 PolicyId , 'IL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 40041 RecordId, 4 PolicyId , 'IL' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 20050 RecordId, 2 PolicyId , 'AK' StateCode , NULL ZipCodeStart , NULL ZipCodeEnd FROM dual UNION ALL
  SELECT 40050 RecordId, 4 PolicyId , 'AK' StateCode , 995  ZipCodeStart , 995  ZipCodeEnd FROM dual 
 )
SELECT min(recordid) recordid
FROM  yourtable
WHERE  statecode = :statecode
AND    :zipcode BETWEEN nvl(zipcodestart, :zipcode) AND nvl(zipcodeend, :zipcode)
GROUP BY policyid
/

Forget the "WITH" part, that's just for me to simulate your data. Just look at the SELECT itself.

When I run it, I get:
SQL> @orafaq
For 'IL' and 603

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


  RECORDID
----------
     20040
     40005
     60005

For 'AL' and 369

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


  RECORDID
----------
     10005
     20004
     60040

SQL>


Is that what you are looking for?

MHE

[Updated on: Wed, 21 March 2007 09:52]

Report message to a moderator

Re: How to get either of two records? [message #225921 is a reply to message #225804] Wed, 21 March 2007 23:42 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
No; because your solution depends on RecordId and I need solution based on the logic I described in my second last msg which clearly says that IF FIRST CHOICE IS NOT AVAILBALE THEN SELECT SECOND(NULL) CHOICE.

Thank you for understanding.

[Updated on: Thu, 22 March 2007 05:38]

Report message to a moderator

Re: How to get either of two records? [message #225983 is a reply to message #225921] Thu, 22 March 2007 03:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saurabh_12_23 wrote on Sat, 17 March 2007 14:42
My question is for a given zip code(say 603) in IL state it should get records(40005,60005,20040) and for the zip code (say 606) in IL state it should return (40041,20040). Similarly for the zip code 369 in AL state it should return 60040 and 10005.


saurabh_12_23 wrote on Tue, 20 March 2007 15:37
My question is for a given zip code(say 603) in IL state it should get records(40005,60005,20040) and for the zip code (say 606) in IL state it should return (40041,20040). Similarly for the zip code 369 in AL state it should return 60040 and 10005 and 20004.


Hmmm...and now this is no longer your question? Fine, but I won't give up that easily. This is what I want from you:
  1. CREATE TABLE statement. Simple, and only the relevant columns.
  2. Sample data in the form of INSERT statements for the table in step 1.
  3. Expected output based on the sample data from step 2 and on what criteria you want this resultset
Use [code] and [/code] tags around the CREATE TABLE and INSERT statements.

MHE

[Updated on: Thu, 22 March 2007 03:11]

Report message to a moderator

Re: How to get either of two records? [message #225998 is a reply to message #225983] Thu, 22 March 2007 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you could include a clear and concise explanation of WHY the result set you want is the right one, it would be useful. Tell us WHY (from your initial result set) you wnt us to select recordid 20040, but not select recordid 40041, despite them having the same values for STATE and ZIPCODE.
Re: How to get either of two records? [message #226021 is a reply to message #225998] Thu, 22 March 2007 05:46 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Ok; I am restarting myself in clear cut way:

Hi,
I have a MS-Access table with following data.

The table POLICYSTATES in MS-ACCESS database is created as
RecordId(Number) PolicyId(Number) StateCode(text) ZipCodeStart(Number) ZipCodeEnd(Number)
60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369
40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL
40041 4 IL
20050 2 AK 995 995
40050 4 AK

My questions are for a given zip code say 603(available) in IL state output is:

40005 4 IL 600 605
60005 6 IL 600 605
20040 2 IL

for the zip code say 606(not available) in IL state output is:

20040 2 IL
40041 4 IL

for the zip code 369(available) in AL state output is:

60040 6 AL 369 369
10005 1 AL
20004 2 AL 369 369

and for the zip code 995(available) in AK state output is:
20050 2 AK 995 995
40050 4 AK

and for the zip code 994(not available) in AK state output is:

40050 4 AK

The logic behind the scene is IF FIRST CHOICE IS NOT AVAILABLE THEN SELECT THE SECOND CHOICE (which is NULL in my scenario).

And if supplied zipcode is not in the available range then I have to multiply an amount for policy premium by a factor 2 otherwise 1.

In procedural programing the logic is:

while length of records
if policy is available for the zip code then
amountFactor=1;
else if policy is not available for the zip code but available for the state then
amountFactor=2;
end if
end while

An attached word document(ZipCodeFactor.doc) has the above content.

Any help will be greatly appreciated if any Oracle Master can provide ANSI solution

[Updated on: Fri, 23 March 2007 00:14]

Report message to a moderator

Re: How to get either of two records? [message #226032 is a reply to message #225983] Thu, 22 March 2007 06:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Allow me to repeat myself:
Maaher wrote on Thu, 22 March 2007 09:11
  1. CREATE TABLE statement. Simple, and only the relevant columns.
  2. Sample data in the form of INSERT statements for the table in step 1.
  3. Expected output based on the sample data from step 2 and on what criteria you want this resultset
Use [code] and [/code] tags around the CREATE TABLE and INSERT statements.


MHE
Re: How to get either of two records? [message #226035 is a reply to message #226021] Thu, 22 March 2007 06:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Allow me to repeat myself as well.

Tell us WHY (from your initial result set), when selecting results for (IL,603) you want us to select recordid 20040, but not select recordid 40041, despite them having the same values for STATE and ZIPCODE.

DO you want us to select all the records matching the ZIPCODE, if there are any, and then an additional record that has no ZIPCODE? If so, tell us.
Why is the additional record the one with RECORDID 20040 and not 40041? Is it because it has a lower POLICYID? If so, tell us.

We don't know these things. We know only what you have told us.

We can give you an answer if we can just get you to tell us all of the question.


The phrase 'IF FIRST CHOICE IS NOT AVAILABLE THEN SELECT THE SECOND CHOICE (which is NULL in my scenario).' explains nothing to me. Your sample output includes anywhere between 0 and 2 rows with non-null output, so quite what is the 'first choice' and what is 'second choice' is as clear as mud.

Re: How to get either of two records? [message #226038 is a reply to message #226035] Thu, 22 March 2007 06:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, why, if the data in the table for STATECODE = 'AL' is
60040 6 AL 369 369 
20004 2 AL 369 369 
10005 1 AL 

Why should the resuts be
60040 6 AL 369 369 
10005 1 AL 

and not
60040 6 AL 369 369 
20004 2 AL 369 369 
10005 1 AL 

which is what you would expect from looking at the IL results.
Re: How to get either of two records? [message #226039 is a reply to message #226038] Thu, 22 March 2007 06:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming the assumptions I made above are correct, and you've made a mistake with the AL results, try this:
CREATE TABLE zip_test (RecordId NUMBER, PolicyId NUMBER, StateCode VARCHAR2(2), ZipCodeStart NUMBER, ZipCodeEnd NUMBER);

INSERT INTO zip_test VALUES (60040, 6, 'AL', 369, 369 );
INSERT INTO zip_test VALUES (10005, 1, 'AL',NULL ,NULL);
INSERT INTO zip_test VALUES (20004, 2, 'AL', 369, 369 );
INSERT INTO zip_test VALUES (40005, 4, 'IL', 600, 605 );
INSERT INTO zip_test VALUES (60005, 6, 'IL', 600, 605 );
INSERT INTO zip_test VALUES (20040, 2, 'IL', NULL,NULL);
INSERT INTO zip_test VALUES (40041, 4, 'IL', NULL,NULL);
INSERT INTO zip_test VALUES (20050, 2, 'AK', NULL,NULL);
INSERT INTO zip_test VALUES (40050, 4, 'AK', 995, 995 );

select recordid,policyid,statecode,zipcodestart,zipcodeend
from  (SELECT recordid,policyid,statecode,zipcodestart,zipcodeend
             ,dense_rank() over (partition by statecode order by case when nvl2(zipcodestart,0,1) = 0 then 0 else policyid end) ranking
       FROM   zip_test
       WHERE  statecode = :statecode
       AND   :zipcode BETWEEN NVL(zipcodestart,:zipcode) AND nvl(zipcodeend,:zipcode))
where ranking <= 2;
Re: How to get either of two records? [message #226070 is a reply to message #226039] Thu, 22 March 2007 08:02 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Sorry for the non-sequitor, but I'm finding this thread rather funny.
It's now been 9 posts by the OP and we still don't have an explanation as to why and why not a row should be selected.
I was curious as the the most posts by a single person to a single thread in OraFAQ.
Re: How to get either of two records? [message #226252 is a reply to message #226038] Fri, 23 March 2007 01:40 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Please read my reply in orange red.
Allow me to repeat myself as well.

Tell us WHY (from your initial result set), when selecting results for (IL,603) you want us to select recordid 20040, but not select recordid 40041, despite them having the same values for STATE and ZIPCODE.
Because there are two types of rows in the table PolicyStates; first type of rows show the insurance policy availability in particular state with particular zip code range;
second type shows two scenario (a)The insurance policy availability in a particular state but without zipcode range which means for any zipcode it is available in that state.(ii)The insurance policy is available in particular state outside of the available range. So (IL,603) is available zipcode for policies 4,6(40005,60005) with first type and available for policy 2 with second--(i) type. The 40041 is under second--(ii) type so it should come up.


DO you want us to select all the records matching the ZIPCODE, if there are any, and then an additional record that has no ZIPCODE? If so, tell us.
No, I want to select either FIRST CHOICE(first type as matching zipcode) or SECOND CHOICE(second type as NULL).
Why is the additional record the one with RECORDID 20040 and not 40041? Is it because it has a lower POLICYID? If so, tell us.
There is no connection between lower policyid and recordid.

We don't know these things. We know only what you have told us.

We can give you an answer if we can just get you to tell us all of the question.


The phrase 'IF FIRST CHOICE IS NOT AVAILABLE THEN SELECT THE SECOND CHOICE (which is NULL in my scenario).' explains nothing to me. Your sample output includes anywhere between 0 and 2 rows with non-null output, so quite what is the 'first choice' and what is 'second choice' is as clear as mud.

The FIRST CHOICE is a row which has the range for a particular zipcode(60040,20004,40005,60005,20050) and the SECOND CHOICE is a row which has NULL value for ZipCodeStart and ZipCodeEnd(10005,20040,40041,40050).

Any help will be greatly-greatly-greatly appreciated.

Thank you
-Saurabh
Re: How to get either of two records? [message #226291 is a reply to message #226252] Fri, 23 March 2007 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Will you PLEASE answer the questions I ask!

I'm starting to suspect that I've got a masochistic streak - I should have walked away from this one about 5 posts ago.

Does the query I posted above solve your problem? If not, why not - show examples where it doesn't match and provide explanations. No further help will be provided until you have done this.

Because there are two types of rows in the table PolicyStates; first type of rows show the insurance policy availability in particular state with particular zip code range;
second type shows two scenario 
(a)The insurance policy availability in a particular state but without zipcode range which means for any zipcode it is available in that state.
(ii)The insurance policy is available in particular state outside of the available range.
 So (IL,603) is available zipcode for policies 4,6(40005,60005) with first type and available for policy 2 with second--(i) type. The 40041 is under second--(ii) type so it should come up.

You state that there are two types of rows. The first type have ZIP codes (rows marked with a * below.
The second type of row is divided into two types - in your words WITHOUT a zip range, and OUTSIDE a zip range. (rows marked with a + below)
1) Of the two types in the second (+), which one do you want returning
2) How do you distinguish the two types of (+) rows
3) Basically, what values in what columns allow me to decide to return record 20040 rather than 40041. You've said it's nothing to do with the Recordid, and the Zipcodes are null in both cases.
- Do I just pick the lowest policyid?
- Do I pick a row at random?
- Do I pick a record with a policyid that isn't in the set of rows I picked that match the Zipcode?
- If it's not one of those then just tell me how to pick one of those two rows over the other.
Record Policy  State ZFrom Zto
40005  4       IL    600   605     *
60005  6       IL    600   605     * 
20040  2       IL                  +
40041  4       IL                  +


I said:
DO you want us to select all the records matching the ZIPCODE, if there are any, and then an additional record that has no ZIPCODE? If so, tell us.

You Replied
No, I want to select either FIRST CHOICE(first type as matching zipcode) or SECOND CHOICE(second type as NULL).

How is what you say different to what I did?
Your posted examples (Example for IL,603 copied below) show that you want the rows that match the given state and zipcode range.
It also shows that you want an additional record that has no values in the Zipcode.
How is what you have asked for different to what I said, and what the query I provided above provides?

My questions are for a given zip code say 603(available) in IL state output is:

40005 4 IL 600 605 
60005 6 IL 600 605 
20040 2 IL


[Updated on: Fri, 23 March 2007 03:44]

Report message to a moderator

Re: How to get either of two records? [message #226292 is a reply to message #226291] Fri, 23 March 2007 03:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've just run the query I provided in all the test cases you gave, and here are the answers.
If these aren't correct, do please enlighten us as to why they aren't, what the correct answer should be, and explain it in some considerable detail.
SQL> @c:\temp\test.sql
SQL> select recordid,policyid,statecode,zipcodestart,zipcodeend
  2  from  (SELECT recordid,policyid,statecode,zipcodestart,zipcodeend
  3               ,dense_rank() over (partition by statecode order by case when nvl2(zipcodestart,0,1) = 0 then 0 else policyid end) ranking
  4         FROM   zip_test
  5         WHERE  statecode = &statecode
  6         AND   &&zipcode BETWEEN NVL(zipcodestart,&&zipcode) AND nvl(zipcodeend,&&zipcode))
  7  where ranking <= 2;
Enter value for statecode: 'IL'
Enter value for zipcode: 603

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     40005          4 IL          600        605
     60005          6 IL          600        605
     20040          2 IL

SQL> @c:\temp\test.sql
Enter value for statecode: 'IL'
Enter value for zipcode: 606

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     20040          2 IL
     40041          4 IL

SQL> @c:\temp\test.sql
Enter value for statecode: 'AL'
Enter value for zipcode: 369

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     60040          6 AL          369        369
     20004          2 AL          369        369
     10005          1 AL

SQL> @c:\temp\test.sql
Enter value for statecode: 'AK'
Enter value for zipcode: 995

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     40050          4 AK          995        995
     20050          2 AK

SQL> @c:\temp\test.sql
Enter value for statecode: 'AK'
Enter value for zipcode: 994

  RECORDID   POLICYID ST ZIPCODESTART ZIPCODEEND
---------- ---------- -- ------------ ----------
     20050          2 AK

Re: How to get either of two records? [message #226327 is a reply to message #226292] Fri, 23 March 2007 04:49 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Hi JRowBottom,

Thank you very much for your great-great true help. Yes The query you have provided just before is my answer. I have one more problem; I want to do this in MS-ACCESS. If you can do it I will be very much grateful to you.

Thank you
Saurabh
Re: How to get either of two records? [message #226346 is a reply to message #226327] Fri, 23 March 2007 05:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Access?
Marvelous.
You do know this is an Oracle forum, don't you?

I'm afraid this solution uses Analytic functions that aren't (to the best of my knowledge) available in Access.

Re: How to get either of two records? [message #226352 is a reply to message #226346] Fri, 23 March 2007 05:59 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Hi,
Yes, I know It is an Oracle forum but where is MS-ACCESS forum?
Can you not convert the query in ACCESS syntax. It will hardly required to remove the oracle specific functions. If so please do
it.
Again thank you very much for the solution.
-Saurabh
Re: How to get either of two records? [message #226361 is a reply to message #226352] Fri, 23 March 2007 06:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
saurabh_12_23 wrote on Fri, 23 March 2007 11:59
Hi,
Yes, I know It is an Oracle forum but where is MS-ACCESS forum?



I found this one rather good: http://www.utteraccess.com/

MHE

[Updated on: Fri, 23 March 2007 06:26]

Report message to a moderator

Re: How to get either of two records? [message #226374 is a reply to message #225100] Fri, 23 March 2007 07:45 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

This thread is hilarious. "Hey, Oracle dudes, how do I do this thing in MS-Access?" I think I'll go find an MS-Access forum and ask a question about Oracle. I'm sure that will go over well. Laughing
Re: How to get either of two records? [message #226376 is a reply to message #226374] Fri, 23 March 2007 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Ericle wrote on Fri, 23 March 2007 08:45
This thread is hilarious. "Hey, Oracle dudes, how do I do this thing in MS-Access?" I think I'll go find an MS-Access forum and ask a question about Oracle. I'm sure that will go over well. Laughing


You said what we were all thinking Wink
Re: How to get either of two records? [message #226385 is a reply to message #226374] Fri, 23 March 2007 08:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ericle wrote on Fri, 23 March 2007 13:45
I think I'll go find an MS-Access forum and ask a question about Oracle.
Keep us informed Very Happy

MHE
Re: How to get either of two records? [message #226387 is a reply to message #226385] Fri, 23 March 2007 08:29 Go to previous messageGo to next message
saurabh_12_23
Messages: 13
Registered: March 2007
Junior Member
Please don't make fun of me; I have to do in MS-ACCESS; so where is MS-ACCESS forum?

I will do it in Tortoise manner some where like searchsqlserver.com or asktom.oracle.com or sqlcourse2.com
Thank you for sending link.
Saurabh

[Updated on: Fri, 23 March 2007 08:33]

Report message to a moderator

Re: How to get either of two records? [message #226388 is a reply to message #226387] Fri, 23 March 2007 08:38 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

Quote:
Please don't make fun of me; I have to do in MS-ACCESS; so where is MS-ACCESS forum?


It was just a little joke. You can't admit that asking an MS-Access question in an Oracle forum is a little bit strange? Anyway, I'm sorry that I offended you. It looks like someone provided a link to an Access forum and I'm sure there are others. Just use Google.

[Updated on: Fri, 23 March 2007 08:54]

Report message to a moderator

Re: How to get either of two records? [message #226613 is a reply to message #226387] Sun, 25 March 2007 22:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Topic locked. Please do not post non-Oracle questions on our Oracle forums.
Re: How to get either of two records? [message #227548 is a reply to message #226613] Wed, 28 March 2007 10:41 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@saurabh_12_23
Take this as a final warning and stop harassing.
It is so easy to track you down.
And you are so stupid to use a valid work email address~!.
Previous Topic: Deleting duplicate records from oracle object
Next Topic: Multiple files loading using SQL Loader
Goto Forum:
  


Current Time: Sun Dec 11 02:02:05 CST 2016

Total time taken to generate the page: 0.08989 seconds