Hi Mike, All,
Hope you all are doing well.
I need your help with the same issue that was resolved.
*Say if the table has data like below(2 rows with same data).
Below query give "No data found", but I need to trap the TOO_MANY_ROW
Exception. How can I get this done?*
Insert into TAB
(C1,
C2, C3, C4)
Values
*(1, 2, 3, 4);*
Insert into TAB
(C1,
C2, C3, C4)
Values
* (1, 2, 3, 4);*
COMMIT;
set linesize 132
define var1 = 1
define var2 = 2
define var3 = 3
define var4 = 4
SELECT c1,
case when (c1c = 1) then null else c2 end c2,
case when (c1c = 1 or c2c = 1) then null else c3 end c3,
case when (c1c = 1 or c2c = 1 or c3c = 1) then null else c4 end c4
FROM (SELECT c1,
c2,
c3,
c4,
COUNT (*) OVER (PARTITION BY c1) c1c,
COUNT (*) OVER (PARTITION BY c1, c2) c2c,
COUNT (*) OVER (PARTITION BY c1, c2, c3) c3c,
COUNT (*) OVER (PARTITION BY c1, c2, c3, c4) c4c
FROM tab t1)
WHERE (c1 = &var1 AND c1c = 1)
OR (c1 = &var1 and c2 = &var2 AND c2c = 1 and c1c != 1)
OR (c1 = &var1 and c2 = &var2 AND c3 = &var3 AND c3c = 1 and c2c != 1)
OR (c1 = &var1 and c2 = &var2 AND c3 = &var3 AND c4 = &var4 AND c4c = 1
and c3c != 1);
On Tue, May 25, 2010 at 2:51 PM, Michael Moore <[email protected]>wrote:
> Glad it worked. Make sure you test it well.
> Mike
>
>
> On Tue, May 25, 2010 at 2:32 PM, reach manju <[email protected]> wrote:
>
>> Perfect Mike!.. You are great. Just modified a bit to fit in.. It
>> works..:-)
>> I can trap, no_data_found as well.
>>
>> Thanks Tons!
>> Thanks for all the help.
>>
>> ~Manju
>>
>>
>> On Tue, May 25, 2010 at 1:00 PM, reach manju <[email protected]> wrote:
>>
>>> You are right Andrej, I forgot to mention INTO in the statement,
>>>
>>> I am working on the suggestion given by Mike. Shall post the results
>>> soon. m sure it should work for the scenario I have..:-)
>>>
>>>
>>> On Tue, May 25, 2010 at 12:31 PM, Andrej Hopko <[email protected]>wrote:
>>>
>>>> it's okay - I hadn't even read the error details and just shout what I
>>>> saw
>>>>
>>>> now I found, that NO_ROWS_FOUND may be used
>>>> I use only NO_DATA_FOUND for handling this kinf og exception
>>>> (and have no idea of difference between them =) )
>>>>
>>>> regards,
>>>>
>>>> Andrej Hopko
>>>>
>>>>
>>>> On 25. 5. 2010 21:26, Michael Moore wrote:
>>>>
>>>> Yes, my example was for straight SQL. I assumed that Manju would know
>>>> how to put it into a PL/SQL context.
>>>> Regards,
>>>> Mike
>>>>
>>>> On Tue, May 25, 2010 at 12:21 PM, Andrej Hopko <[email protected]>wrote:
>>>>
>>>>> in PL/SQL context this select syntax does not work
>>>>>
>>>>>
>>>>> On 25. 5. 2010 18:56, reach manju wrote:
>>>>>
>>>>> select...from tab1
>>>>> where col1=c1.col1 and col2=c1.col2
>>>>>
>>>>>
>>>>> select...from tab1
>>>>> *INTO variable*
>>>>>
>>>>> where col1=c1.col1 and col2=c1.col2
>>>>>
>>>>> you forgot INTO statement which is mandatory!
>>>>>
>>>>> Andrej Hopko
>>>>> --
>>>>> You received this message because you are subscribed to the Google
>>>>> Groups "Oracle PL/SQL" group.
>>>>> To post to this group, send email to [email protected]
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected]
>>>>> For more options, visit this group at
>>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>>
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Oracle PL/SQL" group.
>>>> To post to this group, send email to [email protected]
>>>> To unsubscribe from this group, send email to
>>>> [email protected]
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google
>>>> Groups "Oracle PL/SQL" group.
>>>> To post to this group, send email to [email protected]
>>>> To unsubscribe from this group, send email to
>>>> [email protected]
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>>>
>>>
>>>
>> --
>> You received this message because you are subscribed to the Google
>> Groups "Oracle PL/SQL" group.
>> To post to this group, send email to [email protected]
>> To unsubscribe from this group, send email to
>> [email protected]
>> For more options, visit this group at
>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
