Home » SQL & PL/SQL » SQL & PL/SQL » SQL_QUERY (10g)
SQL_QUERY [message #577992] Fri, 22 February 2013 08:21 Go to next message
tabj
Messages: 20
Registered: June 2012
Junior Member
Hi All ,

I need help on SQL Query.

For a given ID, S_Type values should be same.
If 'IB' string is mentioned, then S_Type can be either 'IB' or 'RT' for a given ID. If not it is an invalid record.


CREATE TABLE TEST_1(ID NUMBER,S_TYPE VARCHAR2(20));
INSERT INTO TEST_1 VALUES(1,'IB');
INSERT INTO TEST_1 VALUES(1,'RT');
INSERT INTO TEST_1 VALUES(1,'IB');
INSERT INTO TEST_1 VALUES(1,'RT');
INSERT INTO TEST_1 VALUES(1,'UI');
INSERT INTO TEST_1 VALUES(1,'WE');
INSERT INTO TEST_1 VALUES(2,'CI');
INSERT INTO TEST_1 VALUES(2,'CI');
INSERT INTO TEST_1 VALUES(2,'TY');
INSERT INTO TEST_1 VALUES(2,'RF');
INSERT INTO TEST_1 VALUES(3,'KKK');
INSERT INTO TEST_1 VALUES(4,'OOO');

REQUIRED DATA:

ID S_TYPE
1 UI
1 WE
2 TY
2 RF

Please help me on this.

regards,
tabj.
Re: SQL_QUERY [message #577994 is a reply to message #577992] Fri, 22 February 2013 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

I don't understand how this:
tabj wrote on Fri, 22 February 2013 14:21
For a given ID, S_Type values should be same.
If 'IB' string is mentioned, then S_Type can be either 'IB' or 'RT' for a given ID. If not it is an invalid record.

Leads to this:
tabj wrote on Fri, 22 February 2013 14:21

REQUIRED DATA:

ID S_TYPE
1 UI
1 WE
2 TY
2 RF


Why isn't IB or RT in the result? Why isn't id 3 or 4 in the result? What does invalid record mean in this context?
Re: SQL_QUERY [message #577995 is a reply to message #577994] Fri, 22 February 2013 08:42 Go to previous messageGo to next message
tabj
Messages: 20
Registered: June 2012
Junior Member
Hi,

3,4 are only one record, but i need result more than count(id) > 1

regards,
tabj.
Re: SQL_QUERY [message #577996 is a reply to message #577995] Fri, 22 February 2013 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the COMPLETE specifications? You didn't answer to ALL cookiemonster's questions.
I feel this will be one of these we will waste our time with new conditions at each answer.

Regards
Michel
Re: SQL_QUERY [message #578003 is a reply to message #577996] Fri, 22 February 2013 10:29 Go to previous messageGo to next message
tabj
Messages: 20
Registered: June 2012
Junior Member
Michel,

iam sorry.


1) id ,s_type should be same, example
id s_type
1 IB
1 IB
1 IB
1 IB
Example:2
id s_type
1 IB
1 IB
1 RT
1 RT
S_Type can be either 'IB' or 'RT' for ID.

2) Required data is:
example:
id s_type
1 IB
1 IB
1 your

For this required data is:
id s_type
1 your

example:2
ID S_TYPE
1 IB
1 IB
1 IB
1 RT
1 RT
1 GH

For this required data is:
id s_type
1 GH



i need data only for cout(id) >1 .

please help me on this.

regards,
tabj.
Re: SQL_QUERY [message #578004 is a reply to message #578003] Fri, 22 February 2013 10:44 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Express the specifications with words and only words, no example.
Example comes after when you already express the specifications with words and want to illustrate them.
2 examples can't be a specification as 2 examples cannot cover ALL cases.

Is the following a correc specification for what you want?
"Return all rows that have not type IB or RT and have at least 2 rows with same id"

Regards
Michel
Previous Topic: Inserting data using DBLINK
Next Topic: FIND INVOICE NOS AGAINST BALANCE VALUE
Goto Forum:
  


Current Time: Sat Aug 30 05:14:34 CDT 2014

Total time taken to generate the page: 0.10008 seconds