OraFAQ Forum: SQL & PL/SQL » Sql querry needed for scenario

Home » SQL & PL/SQL » SQL & PL/SQL » Sql querry needed for scenario

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sql querry needed for scenario [message #630882]

Tue, 06 January 2015 01:15

asbh123
Messages: 26
Registered: November 2014
Location: mumbai

Junior Member

Countrycode	EMPID	balance	citigenship	amount1	amount2	unspecamount Convertvalue
Aus	        21	210	singapore	500	10	600	       3
India	        11	209	india	        200	30	400	       2
US	         1	309	america	        300	40	300	       3
UK	        500	408	london	        400	50	200	       2
US	        2	301	japan	        300	40	600	       3

I need output like below based on the below conditions,

1)citigenship='singapore' for any countrycode then in my output it will show it in one new column as Specialamount value.Special amountvalue should take as amount1 value.next amount1 and amount2 values should show as '0' and citizenship should display as null.

2)citizenship notequal to 'singapore'for any countrycode(it should be anything apart from singapore) then special amount column should display
as '0' and amount1 and amount2 and citizenship columns values should display like as is(watever value present in table).

3)countrycode='US' then unspecamount value is divided by corresponding convert value and for other countries it will display as is.
first two conditions will apply for all the country codes and the last one only for Countrycode='US' for only unspecamount column.Table may have any number of countrycodes
but this converter logic will apply for only US code.

Output;

Countrycode	EMPID	balance	citigenship	Specialamount	amount1	amount2unspecamount
Aus	          21	21	null	        500	          0	 0	600
India	          11	20	india	         0	         200	30	400
US	          1	30	america	         0	         300	40	100
UK	         500	40	london	         0	         400	50	200
US	         2	30	japan	         0	         300	40	200

TableCreationscript:

CREATE TABLE Persons
(
countrycode varchar(255),
citigenship(255),
empid int,
balance int,
amount1 int,
amount2 int,
unspecamount int,
Convertvalue int,
);

Insertscript:

Insert Into persons(Countrycode , EMPID  , balance  , citigenship  , amount1  , amount2  , unspecamount  , Convertvalue ) Values ( 'Aus' , '21' , '210' , 'singapore' , '500' , '10' , '600' , '3' );
Insert Into persons(Countrycode , EMPID  , balance  , citigenship  , amount1  , amount2  , unspecamount  , Convertvalue ) Values ( 'India' , '11' , '209' , 'india' , '200' , '30' , '400' , '2' );
Insert Into persons(Countrycode , EMPID  , balance  , citigenship  , amount1  , amount2  , unspecamount  , Convertvalue ) Values ( 'US' , '1' , '309' , 'america' , '300' , '40' , '300' , '3' );
Insert Into persons(Countrycode , EMPID  , balance  , citigenship  , amount1  , amount2  , unspecamount  , Convertvalue ) Values ( 'UK' , '500' , '408' , 'london' , '400' , '50' , '200' , '2' );
Insert Into persons(Countrycode , EMPID  , balance  , citigenship  , amount1  , amount2  , unspecamount  , Convertvalue ) Values ( 'US' , '2' , '301' , 'japan' , '300' , '40' , '600' , '3' );

[Edit MC: Add code tags, please to it yourself next time: How to use [code] tags and make your code easier to read.

Lalit : Added code tags to the DDL and DML statements

[Updated on: Tue, 06 January 2015 03:02] by Moderator