OraFAQ Forum: SQL & PL/SQL » Getting the next data with loop

Home » SQL & PL/SQL » SQL & PL/SQL » Getting the next data with loop (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting the next data with loop [message #587370]

Fri, 14 June 2013 08:58

wolfking
Messages: 99
Registered: May 2013

Member

Hello everyone,

Please do not merge this one to my previous topic, this is another issue.

This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its adress.
However, I want to execute this query for all owners so it should jump the previous owner it found.
Lets say for that example that the loop ends at 4.
How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...
also I should increase the sequence value for each situation
thanks

thanks

SELECT
  DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
  DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
  DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
  INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
FROM NAME INV
  LEFT JOIN CASENAME BREINV ON BREINV.NAMEKEY = INV.NAMEKEY
  LEFT JOIN GLOBALATTRIBUTE FUNCINV ON INV.NAMEKEY = FUNCINV.PARENTKEY
  LEFT JOIN NAMEADDRESS ADDINV ON ADDINV.NAMEKEY = INV.NAMEKEY
  LEFT JOIN NAMEINDIVIDUAL INDIVIDU ON INV.NAMEKEY = INDIVIDU.NAMEKEY
  LEFT JOIN TABLECOUNTRY PAY ON INDIVIDU.NATIONALITYCOUNTRYKEY = PAY.COUNTRYKEY
WHERE
  FUNCINV.Attributekey = 509 AND
  ADDINV.addresstypekey = 1000 AND
  BREINV.nametypekey = 209 AND
  BREINV.SEQUENCE = 1 AND
  BREINV.CASEKEY = 15479601

[EDITED by LF: switched topic title & version]

[Updated on: Fri, 14 June 2013 15:24] by Moderator

Report message to a moderator

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #587374 is a reply to message #587370]

Fri, 14 June 2013 09:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

why do you refuse to follow ALL the Posting Guidelines?

how is it that you write about LOOP but your posted code never actually contain any LOOP?
Do you have an infinite LOOP between your ears?

[Updated on: Fri, 14 June 2013 09:09]

Report message to a moderator

Re: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production [message #587421 is a reply to message #587370]

Fri, 14 June 2013 11:33

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

This query that I pasted is working correctly.

I disagree:

SQL> SELECT
  2    DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
  3    DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
  4    DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
  5    INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
  6  FROM NAME INV
  7    LEFT JOIN CASENAME BREINV ON BREINV.NAMEKEY = INV.NAMEKEY
  8    LEFT JOIN GLOBALATTRIBUTE FUNCINV ON INV.NAMEKEY = FUNCINV.PARENTKEY
  9    LEFT JOIN NAMEADDRESS ADDINV ON ADDINV.NAMEKEY = INV.NAMEKEY
 10    LEFT JOIN NAMEINDIVIDUAL INDIVIDU ON INV.NAMEKEY = INDIVIDU.NAMEKEY
 11    LEFT JOIN TABLECOUNTRY PAY ON INDIVIDU.NATIONALITYCOUNTRYKEY = PAY.COUNTRYKEY
 12  WHERE
 13    FUNCINV.Attributekey = 509 AND
 14    ADDINV.addresstypekey = 1000 AND
 15    BREINV.nametypekey = 209 AND
 16    BREINV.SEQUENCE = 1 AND
 17    BREINV.CASEKEY = 15479601
 18  /
  LEFT JOIN TABLECOUNTRY PAY ON INDIVIDU.NATIONALITYCOUNTRYKEY = PAY.COUNTRYKEY
            *
ERROR at line 11:
ORA-00942: table or view does not exist

Regards
Michel

Report message to a moderator

Re: Getting the next data with loop [message #587448 is a reply to message #587370]

Fri, 14 June 2013 16:57

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

atirtil wrote on Fri, 14 June 2013 15:58

How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...

I'm taking a wild guess into the dark void of absolutely no relevant information on the actual data model, since you seem to be unwilling to provide any information that would enable anybody to actually come up with a real answer:

Change

  BREINV.SEQUENCE = 1 AND

  BREINV.SEQUENCE in (1,2,3,4) AND

It might work or it might not work. I can't tell, since you refuse to provide a test case.

Report message to a moderator

Previous Topic:	SQL Query
Next Topic:	Creating a loop (merged 2)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 24 14:39:05 CDT 2025