CODEWITHSUNDEEP
oracle-database
sneha sv
sneha sv

Reputation: 11

Getting a customized row along with the resultant list in Oracle

I have a table ctry in Oracle Database schema.This table has columns country_name,city_name,city_id,site_name,site_id... My requirement is to get the distinct city Id and city name for a particular country. So my query, SELECT distinct(city_Id), city_name from ctry where country_name = "somevalue". gives me the required results.

To this resultant list I would want to add a row having values as city_id = "(offsite)" and city_name= "OFF site"(which is not there in the table.)

I have used query like SELECT distinct(city_Id), city_name from ctry where country_name = "somevalue". union select '(offsite)' AS city_Id, 'OFF SITE' AS city_name from ctry

But here the problem is the resultant list will have multiple values of city_id and city_name as '(offsite)' and 'OFF SITE' respectively.

But I would only want one row with Offsite Values.Is there any way i can achieve this? Thanks in advance.

Upvotes: 0

Views: 28

Answers (1)

Mynsk
Mynsk

Reputation: 209

As your are not reading data from ctry, you can just use dual table

SELECT distinct(city_Id), city_name from ctry where country_name = "somevalue". 
union select '(offsite)' AS city_Id, 'OFF SITE' AS city_name from dual

Upvotes: 0

Related Questions