I have two tables like this:
| MOVIE_ID | BUDGET |
|----------+-------------|
| 1904269 | $850,000 |
| 1809508 | NLG 800,000 |
| 1988471 | $40,000 |
| 2119404 | $3,266 |
| 2117105 | $125,000 |
| 2167227 | CAD 280,000 |
and
| MOVIE_ID | GROSS |
|----------+------------------------|
| 2463072 | ESP 20,040,964 (Spain) |
| 2044720 | ESP 7,494,043 (Spain) |
| 2083304 | ESP 53,463,024 (Spain) |
| 2461323 | ESP 15,670,733 (Spain) |
| 2318432 | ESP 16,530,040 (Spain) |
| 1874413 | SEK 512,112 (Sweden) |
I want to create a budget_table which contains pairs of movie_id's and movie budgets. The movie budgets are stored as strings in movie_info. We only need to consider budgets which are listed in dollars, i.e. "$ XXXXXX". I also want to create a gross_table which contains pairs of movie_id's and movie gross revenues. Some movies have multiple gross revenues (i.e. the revenue as of 2008, 2010, 2012, etc.), and we just want the largest one. Then, I need to find the most profitable one by gross - budget. The two tables above I got by using the following:
CREATE OR REPLACE VIEW budget_table AS
SELECT movie_id, info AS budget
FROM movie_info WHERE info_type_id = 105;
so the budget info has id = 105. And the gross info has id = 107 and the code is:
CREATE OR REPLACE VIEW gross_table AS
SELECT movie_id, info AS gross
FROM movie_info WHERE info_type_id = 107;
Below is my code to get the budgets only listed in dollars:
CREATE OR REPLACE VIEW budget_table AS
SELECT movie_id, TO_NUMBER(REPLACE(REGEXP_SUBSTR(info, '\$[0-9]+'), '\$', ''))
AS budget
FROM movie_info
WHERE info_type_id = 105
AND REGEXP_LIKE(info, '\$[0-9]+');
I have no idea why it didn't print out anything when I do SELECT * FROM budget_table LIMIT 10;. If I get rid of the last row REGEXP_LIKE, I get the movie_id but all nulls for the budget. Can someone point out the issue with my SQL query?
