Home » SQL & PL/SQL » SQL & PL/SQL » SQL problem (oracle9.2.0.3)
SQL problem [message #439567] Mon, 18 January 2010 07:42 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SQL> select * from test601;

       BID          PAGE       C    STATUS
---------- ---------- ---------- ------------------------------
       101          1          1
       102          1          2
       102          2          2
       103          1          3



for a book(BID) there are pages(PAGE), c is the total count of pages


if C matches with total count of pages for a book then update the status


select * from test601;

       BID          PAGE       C    STATUS
---------- ---------- ---------- ------------------------------
       101          1          1       Y
       102          1          2       Y
       102          2          2       Y
       103          1          3       N





How can i get the result?

[Updated on: Mon, 18 January 2010 07:51]

Report message to a moderator

Re: SQL problem [message #439571 is a reply to message #439567] Mon, 18 January 2010 07:54 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Use the analytical version of sum, combined with decode
Re: SQL problem [message #439572 is a reply to message #439567] Mon, 18 January 2010 07:54 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Use count in its analytic form (you can find out the syntax from the documentation that you hav been pointed to many, many times.
Re: SQL problem [message #439577 is a reply to message #439572] Mon, 18 January 2010 08:05 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SQL> select case when count(*) over (partition by a order by a) =c then 'Y' else 'N' end from test6
1;

C
-
Y
Y
Y
N


This way? But how I can update?

[Updated on: Mon, 18 January 2010 08:16]

Report message to a moderator

Re: SQL problem [message #439580 is a reply to message #439577] Mon, 18 January 2010 08:15 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Stop trying to do everything at once. bring in all of your columns and add in the analytic into your select list.
Lose the order by, lose the where clause, start using column names as described in the original example. Partition by BID and please learn how to post a test case, and how to use a more appropriate title for your posts.

[Edit: Of course, some of the above advice may seem pointless as it was posted whilst you were editing the first version of the above post]

[Updated on: Mon, 18 January 2010 08:19]

Report message to a moderator

Re: SQL problem [message #439581 is a reply to message #439580] Mon, 18 January 2010 08:21 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SQL>   update test601 a set status=
  2     (select (case when count(*) over (partition by b.a order by b.a) =c then 'Y' else 'N' end) e
 from test601 b
  3     where b.a=a.a and b.b=a.b
  4    )
  5  /

4 rows updated.

SQL> select * from test601;

         A          B          C STATUS
---------- ---------- ---------- ------------------------------
       101          1          1 Y
       102          1          2 N
       102          2          2 N
       103          1          3 N

SQL> 



But I am not getting the result I expected. What needs to be corrected?

[Updated on: Mon, 18 January 2010 08:36]

Report message to a moderator

Re: SQL problem [message #439582 is a reply to message #439581] Mon, 18 January 2010 08:36 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Re-read my previous post please.
Re: SQL problem [message #439583 is a reply to message #439582] Mon, 18 January 2010 08:40 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I tried this way:


SQL> select a,c, case when count(*) over (partition by a order by a) =c then 'Y' else 'N' end e from
 test601;

         A          C E
---------- ---------- -
       101          1 Y
       102          2 Y
       102          2 Y
       103          3 N

SQL> select * from test601;

         A          B          C STATUS
---------- ---------- ---------- ------------------------------
       101          1          1 
       102          1          2 
       102          2          2 
       103          1          3 




[Updated on: Mon, 18 January 2010 08:59]

Report message to a moderator

Re: SQL problem [message #439584 is a reply to message #439583] Mon, 18 January 2010 09:04 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
 update test601 a set a.status=
 (select case when count(*) over (partition by b.a order by b.a) =b.c then 'Y' else 'N' end e from
 test601 b where b.a=a.a and a.c=b.c);

Not being able to figure out where is the problem!

[Updated on: Mon, 18 January 2010 09:05]

Report message to a moderator

Re: SQL problem [message #439585 is a reply to message #439584] Mon, 18 January 2010 09:12 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
How can I solve the problem?


SQL> update test601 a set status= (select  case when count(*) over (partition by a order by a) =c th
en 'Y' else 'N' end e from test601 where a.a=a and a.c=c);
update test601 a set status= (select  case when count(*) over (partition by a order by a) =c then 'Y
                              *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL> 
Re: SQL problem [message #439586 is a reply to message #439585] Mon, 18 January 2010 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>How can I solve the problem?
Only return 1 row
Re: SQL problem [message #439588 is a reply to message #439586] Mon, 18 January 2010 09:22 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I tried using correlated query but did not help!
Re: SQL problem [message #439590 is a reply to message #439588] Mon, 18 January 2010 09:32 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
update test601 a set status = ( select case when count(*) over (partition by b.a order by b.a)
=b.c then 'N' else 'Y' end e from test601 B where b.a=a.a and b.b=a.b and b.c=a.c)
/
Re: SQL problem [message #439592 is a reply to message #439590] Mon, 18 January 2010 09:39 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
SQL> ed
Wrote file afiedt.buf

1 UPDATE test601 a set a.status=
2 (SELECT STATUS FROM ( select A,B,C ,case when count(*) over (partition by a order by a)
3* =c then 'Y' else 'N' end STATUS from test601 )sq where sq.a=a.a and sq.b=a.b and sq.c=a.c)
SQL> /

4 rows updated.

SQL> select * from test601;

A B C STATUS
---------- ---------- ---------- ------------------------------
101 1 1 Y
102 1 2 Y
102 2 2 Y
103 1 3 N

SQL>
Re: SQL problem [message #439593 is a reply to message #439567] Mon, 18 January 2010 09:40 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks to Pablolee Madam and Thomas G sir
Re: SQL problem [message #439596 is a reply to message #439593] Mon, 18 January 2010 09:56 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Thanks to Pablolee Madam
How about you look at my profile and correct your mistake?

Thanks
Re: SQL problem [message #439604 is a reply to message #439596] Mon, 18 January 2010 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe an effect of your traditional clothing?

Regards
Michel
Re: SQL problem [message #439633 is a reply to message #439604] Mon, 18 January 2010 13:28 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Very Happy Last time I wore the full regalia (Kilt, Argyle jacket and waistcoat, brogues and skean dubh was my wedding reception I think (about 4 years ago) and my kilt now doesn't fit me Sad
Re: SQL problem [message #439664 is a reply to message #439633] Tue, 19 January 2010 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did it shrink in the washing-machine?
Re: SQL problem [message #439669 is a reply to message #439664] Tue, 19 January 2010 00:56 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
May be.
Or '
Before wedding he was so fat...But now he is fit so the thats(kilt) not fit to him

sriram Smile Very Happy
Re: SQL problem [message #439674 is a reply to message #439664] Tue, 19 January 2010 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Did it shrink in the washing-machine?

I don't see any other cause. Very Happy

Regards
Michel

[Updated on: Tue, 19 January 2010 01:01]

Report message to a moderator

Re: SQL problem [message #439681 is a reply to message #439674] Tue, 19 January 2010 01:14 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Laughing Yep, that must be what it is!
Re: SQL problem [message #440440 is a reply to message #439567] Sun, 24 January 2010 17:54 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
i hope you are fine.

try this one. i hope it will help you.

======================
UPDATE TEST601
SET STATUS = (SELECT DECODE(B,C,'Y','N') FROM TEST601 T
WHERE TEST601.ROWID=T.ROWID)
======================
i think you requirement was as following.
The above mentioned query will solve your problem.
A B C S
------ ---------- ---------- -
101 1 1 Y
102 1 2 N
102 2 2 Y
103 1 3 N
========

Regards

Muhammad Shahid Mughal
Oracle Developer
Faisalabad Pakistan
Re: SQL problem [message #440457 is a reply to message #440440] Mon, 25 January 2010 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since your outcome does not resemble the desired outcome in the original post, I don't think that you understood the requirements.
Besides, the original poster already posted back his solution.
Previous Topic: "How to get datwise data for whole month" (merged 3) and "feature not enabled error&q
Next Topic: help required in SQL date condition (merged 3)
Goto Forum:
  


Current Time: Wed Sep 28 02:20:54 CDT 2016

Total time taken to generate the page: 0.19556 seconds