slavyan887

Untitled

Sep 26th, 2021
1,657
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE VIEW v_vacancy AS
  2. SELECT vacancy.id AS id,
  3.       CASE WHEN vacancy.name LIKE '%(%' THEN
  4.           substr(vacancy.name, 1, instr(vacancy.name, '(')-2)
  5.           ELSE vacancy.name
  6.         END AS short_name,
  7.       employer.name AS employer,
  8.       area.name AS area,
  9.       CAST(vacancy.salary_from / currency.rate AS INTEGER) AS salary
  10. FROM vacancy
  11.     INNER JOIN currency ON vacancy.salary_currency = currency.code
  12.     INNER JOIN employer ON vacancy.employer_id = employer.id
  13.     INNER JOIN area ON vacancy.area_id = area.id
  14. ;
  15.  
  16. SELECT * FROM v_vacancy ORDER BY employer, id LIMIT 10;
RAW Paste Data