CODEWITHSUNDEEP
sqloracle-databasesyntax
Tom Betts
Tom Betts

Reputation: 15

Problems with SQL syntax

    CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN hd.country_visited = 'USA' THEN 'AMERICA' 
           WHEN hd.country_visited = 'India' THEN 'ASIA' 
           WHEN hd.country_visited = 'CHINA' THEN 'ASIA' 
           WHEN hd.country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE hd.country_visited 
         END AS hd.COUNTRY_VISITED 
  FROM   holiday_details hd 
  UNION 
  SELECT hd.holiday_desc, 
         holiday_code 
  FROM   holiday_details hd

This is the code that i currently have, i keep getting either FROM missing errors, or MISSING KEYWORD errors.

Can anyone see any blinding errors in my syntax?

Upvotes: 0

Views: 82

Answers (4)

Simon Kingston
Simon Kingston

Reputation: 495

I don't think you really want the UNION - it appears you're selecting from the holiday_details table in both cases. If you'd like the country_visited to just be a column along with the related holiday_desc and holiday_code, try this:

CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN country_visited = 'USA' THEN 'AMERICA' 
           WHEN country_visited = 'India' THEN 'ASIA' 
           WHEN country_visited = 'CHINA' THEN 'ASIA' 
           WHEN country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE country_visited 
         END AS COUNTRY_VISITED, 
  holiday_desc, 
  holiday_code 
  FROM   holiday_details

This syntax should work with T-SQL. Been too long since I used Oracle to be sure if the syntax is correct there.

Upvotes: 0

Taryn
Taryn

Reputation: 247860

You have a few errors. You appear to be selecting a different number of columns with both SELECT statements. when you are using a UNION the column numbers must be equal:

CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN hd.country_visited = 'USA' THEN 'AMERICA' 
           WHEN hd.country_visited = 'India' THEN 'ASIA' 
           WHEN hd.country_visited = 'CHINA' THEN 'ASIA' 
           WHEN hd.country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE hd.country_visited 
         END AS COUNTRY_VISITED,  -- remove the hd alias
         Null as PlaceholderColumn  
  FROM   holiday_details hd 
  UNION 
  SELECT hd.holiday_desc, 
         holiday_code 
  FROM   holiday_details hd

You also have a table alias on the column name in the first select.

If you only want to return one column, then you will want to use:

CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN hd.country_visited = 'USA' THEN 'AMERICA' 
           WHEN hd.country_visited = 'India' THEN 'ASIA' 
           WHEN hd.country_visited = 'CHINA' THEN 'ASIA' 
           WHEN hd.country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE hd.country_visited 
         END AS COUNTRY_VISITED  -- remove the hd alias
  FROM   holiday_details hd 
  UNION 
  SELECT hd.holiday_desc
  FROM   holiday_details hd

Besides the queries having the same number of columns in each SELECT you are also required to have the same data types for each column. If they are not the same, then you will need to perform a CAST of any of the columns to get the data type the same.

Upvotes: 4

Lamak
Lamak

Reputation: 70668

Well, I do see an error, but not with missing keywords. You are trying to do a UNION between SELECTs with different number of columns:

CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN hd.country_visited = 'USA' THEN 'AMERICA' 
           WHEN hd.country_visited = 'India' THEN 'ASIA' 
           WHEN hd.country_visited = 'CHINA' THEN 'ASIA' 
           WHEN hd.country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE hd.country_visited 
         END AS hd.COUNTRY_VISITED 
  FROM   holiday_details hd 
  UNION 
  SELECT holiday_desc --holiday_code This column shouldn't be here
  FROM   holiday_details

Upvotes: 2

Coding Duchess
Coding Duchess

Reputation: 6919

try

CREATE VIEW hd3_b 
AS 
  SELECT CASE 
           WHEN hd.country_visited = 'USA' THEN 'AMERICA' 
           WHEN hd.country_visited = 'India' THEN 'ASIA' 
           WHEN hd.country_visited = 'CHINA' THEN 'ASIA' 
           WHEN hd.country_visited = 'SPAIN' THEN 'EUROPE' 
           ELSE hd.country_visited 
         END AS COUNTRY_VISITED 
  FROM   holiday_details hd 
  UNION 
  SELECT hd.holiday_desc, 
         hd.holiday_code 
  FROM   holiday_details hd

you did not need hd. in thisl line

END AS COUNTRY_VISITED

since COUNTRY_VISITED is an alias, you do not need preceed it with table alias

Upvotes: 0

Related Questions