Home » SQL & PL/SQL » SQL & PL/SQL » SQL: need to get data in a different way (merged)
SQL: need to get data in a different way (merged) [message #401235] Sun, 03 May 2009 01:55 Go to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
Hi,

I have the below table in Oracle 11g database.

Product Status Source Destination
P1 In Null US
P1 In India US
P1 In China US
P1 In China US
P1 Out US India
P1 Out US India
P1 Out US India
P1 Out US India

I need to write a SQL Select query to display the information from table in below format.

Status(In) Status(Out)
From China 2 To China 0
From India 1 To India 4

If the source is non-US (India or China) and Status is In, get the counts of India and China from Source Column (From India, From China).

If the source is US and Status is Out, get the counts of India and China from Destination Column (To India, To China).

I know its easier done by writing a PL SQL code but I dont have rights to create the procedures. I have only SELECT privileges to the database, therefore the need to write a SELECT SQL Query. Any help in this is highly appreciated.

Thanks,
Best Regards,
Ayaz
Re: SQL: need to get data in a different way [message #401240 is a reply to message #401235] Sun, 03 May 2009 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, you can do it in SQL.

Now, post a working Test case: create table and insert statements along with the result you want with these data.

Before 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.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Need to write SQL Select query [message #401247 is a reply to message #401235] Sun, 03 May 2009 04:45 Go to previous messageGo to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
Hi,
I have a table PROD_DETAILS with the following structure.

CREATE TABLE PROD_DETAILS
PRODUCT VARCHAR(10),
STATUS  VARCHAR(5),
SOURCE VARCHAR(20),
DESTINATION VARCHAR(20));

INSERT INTO PROD VALUES('P1', 'IN', NULL, 'US');
INSERT INTO PROD VALUES('P1', 'IN', 'INDIA', 'US');
INSERT INTO PROD VALUES('P1', 'IN', 'CHINA', 'US');
INSERT INTO PROD VALUES('P1', 'IN', 'CHINA', 'US');
INSERT INTO PROD VALUES('P1', 'OUT', 'US', 'INDIA');
INSERT INTO PROD VALUES('P1', 'OUT', 'US', 'INDIA');
INSERT INTO PROD VALUES('P1', 'OUT', 'US', 'INDIA');
INSERT INTO PROD VALUES('P1', 'OUT', 'US', 'INDIA');


I need to write a SQL Select query to display the information from table in below format.


Status(In)	Status(Out)		
--------------------------
China	2	China	0	
India	1	India	4	

If the source is non-US (India or China) and Status is In, get the counts of India and China respectively from Source Column.

If the source is US and Status is Out, get the counts of India and China respectively from Destination Column.

I know its easier done by writing a PL SQL code but I dont have rights to create the procedures. I have only SELECT privileges to the database, therefore the need to write a SELECT SQL Query. Any help in this is highly appreciated.

Thanks,
Best Regards,
Ayaz

Re: SQL: need to get data in a different way [message #401249 is a reply to message #401240] Sun, 03 May 2009 04:59 Go to previous messageGo to next message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
thanks for the information Michel. I have reposted the query after considering your remarks. But I had to create a new topic as I couldn't find an option to append to existing post. Kindly have a look.

regards,
Ayaz
Re: SQL: need to get data in a different way [message #401252 is a reply to message #401249] Sun, 03 May 2009 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ayaz_ncr wrote on Sun, 03 May 2009 11:59
thanks for the information Michel. I have reposted the query after considering your remarks. But I had to create a new topic as I couldn't find an option to append to existing post. Kindly have a look.

regards,
Ayaz

But you just did it with this post!
Anyway I merge the two topics.

Regards
Michel

Re: SQL: need to get data in a different way [message #401255 is a reply to message #401249] Sun, 03 May 2009 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please note that my post mentionned:
Quote:
post a working Test case

SQL> CREATE TABLE PROD_DETAILS
  2  PRODUCT VARCHAR(10),
  3  STATUS  VARCHAR(5),
  4  SOURCE VARCHAR(20),
  5  DESTINATION VARCHAR(20));
PRODUCT VARCHAR(10),
*
ERROR at line 2:
ORA-00922: missing or invalid option

SQL> INSERT INTO PROD VALUES('P1', 'IN', NULL, 'US');
INSERT INTO PROD VALUES('P1', 'IN', NULL, 'US')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

Regards
Michel

[Updated on: Sun, 03 May 2009 06:23]

Report message to a moderator

Re: Need to write SQL Select query [message #401264 is a reply to message #401247] Sun, 03 May 2009 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select decode(status,'IN',source,destination) country,
  2         count(decode(status,'IN',1)) status_in,
  3         count(decode(status,'OUT',1)) status_out
  4  from prod
  5  where ( status = 'IN' and source != 'US' )
  6     or ( status = 'OUT' and source = 'US' )
  7  group by decode(status,'IN',source,destination)
  8  order by 1
  9  /
COUNTRY               STATUS_IN STATUS_OUT
-------------------- ---------- ----------
CHINA                         2          0
INDIA                         1          4

2 rows selected.

Regards
Michel
Re: Need to write SQL Select query [message #401511 is a reply to message #401264] Tue, 05 May 2009 05:56 Go to previous message
ayaz_ncr
Messages: 8
Registered: May 2009
Junior Member
Hi Michel,

It works to perfection! thanks a lot.

Best Regards,
Ayaz
Previous Topic: XMLTYPE in Oracle
Next Topic: SQL Query to get all constraints of database
Goto Forum:
  


Current Time: Sun Dec 04 18:55:44 CST 2016

Total time taken to generate the page: 0.08006 seconds