Another SQLP question

[ondohotola]

As part of m current little project (see #2425) I had to resort to sqlite3 where I can do something like

-- Create the table in memory
CREATE TABLE data (FQDN TEXT, HOST TEXT, ns TEXT, ip TEXT);

-- Import the CSV into the table
.mode csv
.import '$CSVFILE' data

-- Assign unique indices to each ip for every FQDN
CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

-- Turn headers back on for clean output
.headers on
.mode csv
.nullvalue ''

-- Pivot the data into 12 columns, using NS1-NS12 as column names
SELECT 
    FQDN,
    MAX(CASE WHEN ip_index = 1 THEN host_ip END) AS NS1,
    MAX(CASE WHEN ip_index = 2 THEN host_ip END) AS NS2,
    MAX(CASE WHEN ip_index = 3 THEN host_ip END) AS NS3,
    MAX(CASE WHEN ip_index = 4 THEN host_ip END) AS NS4,
    MAX(CASE WHEN ip_index = 5 THEN host_ip END) AS NS5,
    MAX(CASE WHEN ip_index = 6 THEN host_ip END) AS NS6,
    MAX(CASE WHEN ip_index = 7 THEN host_ip END) AS NS7,
    MAX(CASE WHEN ip_index = 8 THEN host_ip END) AS NS8,
    MAX(CASE WHEN ip_index = 9 THEN host_ip END) AS NS9,
    MAX(CASE WHEN ip_index = 10 THEN host_ip END) AS NS10,
    MAX(CASE WHEN ip_index = 11 THEN host_ip END) AS NS11,
    MAX(CASE WHEN ip_index = 12 THEN host_ip END) AS NS12
FROM numbered
WHERE FQDN != 'FQDN'
GROUP BY FQDN;

When I issue qsv sqlp data.csv t.sql with t.sql having:

CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

I get this error message

Failed to execute query: CREATE TABLE numbered AS
SELECT
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*)
     FROM data d2
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1: unexpected subquery