CODEWITHSUNDEEP
sqlsqlite
Joseph Freivald
Joseph Freivald

Reputation: 421

sqlite ( LIKE or LIKE) getting different results from LIKE ( select )

There are a number of parameters in this query that are user tune-able, and rather than edit them directly into the sql statement, I want the user to be able to create a few text files, run the query with sqlite3 db.sqlite ".read query.sql" > result.csv, and get the results as a CSV. The problem is that I get different results if I hard-code the select functions in the SQL statement, or if I import the text files and use a select.

Here is the whole SQL file:

.headers off
.mode csv
CREATE TEMP TABLE IF NOT EXISTS zipcodes(zipcode text primary key);
.import zipcodes.txt zipcodes
CREATE TEMP TABLE IF NOT EXISTS dates(year text primary key);
.import dates.txt dates
CREATE TEMP TABLE IF NOT EXISTS history_codes(code text primary key);
.import history_codes.txt history_codes

.print "CALLSIGN,FIRST,LAST,ADDRESS,BOX,CITY,STATE,ZIP"

select
    DISTINCT
    COUNT(*)
    from PUBACC_EN 
        JOIN PUBACC_HD ON PUBACC_EN.unique_system_identifier = PUBACC_HD.unique_system_identifier 
        JOIN PUBACC_AM ON PUBACC_EN.unique_system_identifier = PUBACC_AM.unique_system_identifier
        JOIN PUBACC_AD ON PUBACC_EN.unique_system_identifier = PUBACC_AD.unique_system_identifier
        JOIN PUBACC_HS ON PUBACC_EN.unique_system_identifier = PUBACC_HS.unique_system_identifier
    WHERE (radio_service_code = "HA" or radio_service_code = "HV")
            and PUBACC_AM.callsign <> ''
            and PUBACC_HS.code LIKE ( select code from history_codes )
            and ( street_address <> '' OR po_box <> '')
            and applicant_type_code == "I" 
            and NOT previous_operator_class <> '' 
            and NOT previous_callsign <> ''
    --      and grant_date like ( select year from dates ) 
            and ( grant_date like "%2015%" or grant_date like "%2016%" ) 
            and zip_code IN ( select zipcode from zipcodes )
    ORDER BY PUBACC_AM.callsign ASC
;

DROP TABLE zipcodes;
DROP TABLE dates;
DROP TABLE history_codes;

Notice the lines

--      and grant_date like ( select year from dates ) 
        and ( grant_date like "%2015%" or grant_date like "%2016%" )

The date table contains:

sqlite> select * from dates;
%2015%
%2016%

So it has the same items as the hard coded line. I get a different number of records if I swap statements using comments. I've only shown the dates item here, but I get different results if I do the same with zipcodes or history_codes as well.

How do I allow the users to edit text files for the parameters and then import that information into the query?

Thank you.

Upvotes: 1

Views: 124

Answers (1)

CL.
CL.

Reputation: 180310

Unless you're using an operator like IN, a subquery is a scalar subquery, i.e., only the first result is used. This means that date like (select ...) is the same as just date like '%2015%'.

To get access to all dates, you have to add a join with the dates table:

...
JOIN dates ON grant_date LIKE dates.year
...

Upvotes: 1

Related Questions