Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

WITH Clause in SELECT with empty result set

Hi,

I am trying to use the WITH clause in a SELECT.  Documentation is at https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/Queries/WITHClausesInSELECT.htm .

My query looks like this.


WITH
    with_clause_1 AS (SELECT  ...    ),
    with_clause_2 AS (SELECT  ...    )

SELECT *
FROM with_clause_1, with_clause_2

 

If both *with_clauses* return values, then the query works fine.  However, if one of them do not have any rows (e.g. with_clause_1 returns 0 row), then the result of the whole query returns zero row.

 

Does anyone know how I can write the query such that rows will always be returned if at least 1 of the clauses return a row?  For the clauses that return 0 row, it's ok to return a static value too, so something like NULL is fine to indicate 0 row returned.

Is this a bug with this WITH clause then?

Comments

  • I just found a workaround by doing a UNION with DUAL, but it requires defining a specific value for each column in the query.  It would still be nice to not have to do this.

  • Actually, the UNION with DUAL will only address the case where one of the *with_clauses* return an empy result set.  But if the with_clause has values, I'm not gettingn the same result set as running the whole query vs. running the "select for the with_clause UNION DUAL".

  • To summarize, the original problem is still there and not resolved.  Introducing the UNION with DUAL just ensures that the result set returned is never empty.
  • Review SQL syntax
    FROM with_clause_1, with_clause_2
    comma means join(and don't invent a new syntax - UNION DUAL). Try next:
    WITH set1 AS(SELECT * FROM Customer),
    set2 AS(SELECT * FROM Customer)
    SELECT * FROM set1
    UNION ALL
    SELECT * FROM set2;

    daniel=> \e
    id | name | birthday
    ----+------+------------
    1 | Dan | 1983-01-08
    2 | Ian | 1984-01-09
    3 | Lee | 1985-02-10
    1 | Dan | 1983-01-08
    2 | Ian | 1984-01-09
    3 | Lee | 1985-02-10
    (6 rows)
  • Since you're using a union, it requires the columns for set1 and set2 to be of the same data type.  This means you can't have set1 return a column of varchar but set2 have an integer column.  With what I did originally, it works fine even if the data types are different, but the problem is these with_clauses must have data!

    Also, I don't understand why the WITH clause is used in this case then if you're just doing a union.  Alternative, you can just have a union for all those selects in the with_clauses.  The only "advantage" I see is that you get to use these named queries in the union.
  • I am now able to accomplish this using a FULL OUTER JOIN on set1 and set2, by adding a common column across these "sets" that is used for the join. 

    I will update if there are any other issues.

    Thanks id10t for pointing me in the right direction.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.