Home » SQL & PL/SQL » SQL & PL/SQL » problem with one up number
problem with one up number [message #355757] Mon, 27 October 2008 07:10 Go to next message
asmani
Messages: 47
Registered: February 2007
Member
Hi there
I am writing following sql but having odd problem(may be my requiremnt is odd). Highlighted select statment alway return same value for more then one results.

SELECT skbhins_numhus c1, student_pidm c2,
(NVL ((SELECT MAX (skrhrae_seq_no) + 1
FROM skrhrae
WHERE skrhrae_numhus = skbhins_numhus
AND skrhrae_pidm = student_pidm),
1
)
) c3,
(SUBSTR (rae2008uoa_1, 1, 2)) c4, (SUM (uoapcnt)) c5
FROM szvhrae t1
WHERE error_message IS NULL
AND rae2008uoa_1 IS NOT NULL
AND student_pidm = 154676
GROUP BY skbhins_numhus, student_pidm, rae2008uoa_1

Is there any thing which I can do to get one plus number for each row?
Please see the attached file for more details.
Thanks
Usman

[Updated on: Mon, 27 October 2008 07:12]

Report message to a moderator

Re: problem with one up number [message #355759 is a reply to message #355757] Mon, 27 October 2008 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could this change as you always have the same value each time a row query it.

Have a look at ROWNUM.

By the way, many of us can't or don't want to download files, so always post it online/
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).


Regards
Michel
Re: problem with one up number [message #355762 is a reply to message #355759] Mon, 27 October 2008 08:00 Go to previous messageGo to next message
asmani
Messages: 47
Registered: February 2007
Member
Thanks Michel

Main query based on view. When I try to add rownum of main query to max value, it doesn’t allow and messes up everything?
Any suggest how to use rownum in sub- select which be great.

Kind regrads
Usman
Re: problem with one up number [message #355763 is a reply to message #355762] Mon, 27 October 2008 08:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the subquery in a "with" clause.

Of course, I bet your design is wrong and you should use a sequence.

Regards
Michel
Previous Topic: XML search
Next Topic: sort order based on multiple conditions
Goto Forum:
  


Current Time: Sun Dec 04 04:36:53 CST 2016

Total time taken to generate the page: 0.08961 seconds