PostgreSQL view embedded if-statements

In my database, for stores that have no rating from Reseller Ratings, they still have an entry but have -1.0 instead of a number between 0.0 and 10.0. The following query results in -10.00 showing up in my view for those stores with -1.0. Instead, I would like either nothing or a - showing up in its place, but I'm not very comfortable with implementing embedded if-statements in my view. Here is my current view.

CREATE VIEW myview AS
SELECT co_url_name AS company_url, score_combined AS stella_score, trunc(score*10, 2) AS bizrate_score,
(SELECT trunc("lifetimeRating"*10, 2)) AS resellerRating_score
    FROM ss_profile_co AS s LEFT OUTER JOIN "resellerRatings_ratings" AS r
        ON s.id = r.company_id
            LEFT OUTER JOIN (SELECT * FROM bizrate_bizrate_ratings WHERE score_name = 'Overall rating') AS b
                ON s.id = b.fk_co_id
                    ORDER BY co_url_name ASC;

The line (SELECT trunc("lifetimeRating"*10, 2)) AS resellerRating_score is the one that returns the negative numbers (or, for valid entries, will return a score between 0.00 and 100.00).

Obviously, I could simply remove these entries from the database that result in this, but it's half a learning experience and half out of my hands to do so anyways.

I appreciate the help!

EDIT: Attempted an embedded if but not surprisingly got an error.

IF (SELECT trunc("lifetimeRating"*10, 2)) = -10.00 THEN NULL ELSE (SELECT trunc("lifetimeRating"*10, 2)) AS resellerRating_score

EDIT2: Figured it out. Line in question is as follows:

(SELECT trunc("lifetimeRating"*10, 2) WHERE trunc("lifetimeRating"*10, 2) > 0) AS resellerrating_score

/foreveralone

Solution

Could look like this:

CREATE VIEW myview AS
SELECT co_url_name AS company_url
     , score_combined AS stella_score
     , trunc(score * 10, 2) AS bizrate_score
     , CASE WHEN "lifetimeRating" < 0
            THEN NULL
            ELSE trunc("lifetimeRating" * 10, 2)
            END AS resellerRating_score
FROM   ss_profile_co s
LEFT   JOIN "resellerRatings_ratings" r ON r.company_id = s.id
LEFT   JOIN bizrate_bizrate_ratings   b ON b.score_name = 'Overall rating' 
                                        AND b.fk_co_id = s.id
ORDER  BY co_url_name;

The sub-select without a FROM clause served no purpose. I use a CASE statement instead.

I also simplified your LEFT JOIN to bizrate_bizrate_ratings. No sub-select necessary either. I pulled the WHERE clause up into the JOIN condition. Simpler and faster.

I would advise not to use mixed case identifiers, so you never have to use double quotes. (This probably makes @Daniels comment invalid, because lifetimerating != "lifetimeRating")