When I query a view using a varchar in my clause, it doesn't return anything unless I use an int. Is there something wrong with my code or is it Synapse?

Question

Hello everyone,

I am completely stumped with my issue. I am trying to query a view I made with two fields called year and month. They are both NVARCHAR's but when I query the view using month = '01' AND year = '2020' I get no results.

However, when I query the view this way ... I get results

This is how I created my view:

GO

DROP VIEW IF EXISTS silver.vw_trip_data_green

GO


CREATE view silver.vw_trip_data_green
AS
SELECT 
    result.filepath(1) AS year,
    result.filepath(2) AS month,
    result.*
FROM 
    OPENROWSET(
        BULK 'silver/trip_data_green/year=*/month=*/*.parquet',
        DATA_SOURCE = 'nyc_taxi_src',
        FORMAT = 'PARQUET'
    ) WITH (
        vendor_id INT,
        lpep_pickup_datetime DATETIME2(7),
        lpep_dropoff_datetime DATETIME2(7),
        store_and_fwd_flag CHAR(1),
        ratecode_id INT,
        pu_location_id INT,
        do_location_id INT,
        passenger_count INT,
        trip_distance FLOAT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        ehail_fee FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT,
        payment_type INT,
        trip_type INT,
        congestion_surcharge FLOAT
    )    AS [result]

Is there something fundamental I'm missing or is there an issue with Synapse?

Kind regards,

Kane.

Answer 1

Don't worry, I found the issue. It was a bloody space in my code. Have a good day