Reputation: 421
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
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