SQLite Forum

Error: no such column: x
Login

Error: no such column: x

(1) By jose isaias cabrera (jicman) on 2020-09-16 01:20:09 [link] [source]

Greetings!

Please take a look at the following query:

WITH maxes(ProjID, pl_insert, ab_insert) as ( SELECT ProjID, MAX(InsertDate), (select max(InsertDate) from ABT_Budget where ProjID = e.ProjID) FROM Project_List e group by ProjID ) SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID AND b.InsertDate = e.ab_insert AND a.PMO_Board_Report != 'No' AND ( ( a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15' ) OR ( a.Target_Go_Live_Date = '' AND a.Finish_Date > substr(date('now'),1,4) || '-01-15' ) ) AND a.InsertDate = e.pl_insert group BY a.ProjID;

And if I execute this command,

sqlite> WITH maxes(ProjID, pl_insert, ab_insert) ...> as ( ...> SELECT ...> ProjID, ...> MAX(InsertDate), ...> (select max(InsertDate) from ABT_Budget where ProjID = e.ProjID) ...> FROM Project_List e ...> group by ProjID ...> ) ...> SELECT * from maxes; ProjID|pl_insert|ab_insert PR0000003811|2019-02-12_12-12-12| ...clip...

Why do I get no such column? Any help would be greatly appreciated. Thanks.

josé

(2.1) By Keith Medcalf (kmedcalf) on 2020-09-16 02:30:31 edited from 2.0 in reply to 1 [link] [source]

What do you mean "no such column"? I do not see any such error message ...

If you mean why is the result of the correlated subquery NULL, that is because there are no rows that satisfy the subquery, or they all have a NULL InsertDate.

Similarly, pl_insert may be NULL if all the InsertDate for that projID in Project_List are NULL.

While this may appear distressing it is not. It would be more work to get rid of the nulls than to leave them be. Plus, of course, the outer query (shown at the top) uses the equal (==) in the constraints involving these columns, and there is no value (not even another NULL) that equals (==) a NULL. In other words, there is no way to descend into the other tables from a NULL value, so they (ought to be culled) very quickly during execution anyway.

Perhaps you could use the following instead (which may be faster anyway):

with maxes (ProjID, pl_insert, ab_insert)
  as (
      select ProjID,
             pl_insert,
             ab_insert
        from (
                select ProjID,
                       (select max(InsertDate) from Project_List where ProjID == o.ProjID) as pl_insert,
                       (select max(InsertDate) from ABT_Budget where ProjID == o.ProjID) as ab_insert
                  from (
                        select distinct ProjID
                          from Project_List
                       ) as o
             )
       where ProjID is not null
         and pl_insert is not null
         and ab_insert is not null
     )
select *
  from maxes
;

(3) By Keith Medcalf (kmedcalf) on 2020-09-16 03:00:23 in reply to 2.1 [link] [source]

Forget that being faster. I have no clue how to convince the query optimizer to not execute the correlated subquery multiple times, so you are better off leaving the NULLs alone since they don't hurt anything.

However, this may still be faster, even though it still may have nulls:

with maxes (ProjID, pl_insert, ab_insert)
  as (
      select ProjID,
             (select max(InsertDate) from Project_List where ProjID == o.ProjID) as pl_insert,
             (select max(InsertDate) from ABT_Budget where ProjID == o.ProjID) as ab_insert
        from (
              select distinct ProjID
                from Project_List
             ) as o
     )
select *
  from maxes
;

(5) By jose isaias cabrera (jicman) on 2020-09-16 16:11:12 in reply to 3 [link] [source]

Thanks, Keith. I am trying this one that you provided with another select, sqlite> with maxes (ProjID, pl_insert, ab_insert) ...> as ( ...> select ProjID, ...> (select max(InsertDate) from Project_List where ProjID == o.ProjID) as pl_insert, ...> (select max(InsertDate) from ABT_Budget where ProjID == o.ProjID) as ab_insert ...> from ( ...> select distinct ProjID ...> from Project_List ...> ) as o ...> ) ...> SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a ...> LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID ...> AND ...> b.InsertDate = o.ab_insert ...> AND ...> a.PMO_Board_Report != 'No' ...> AND ...> ( ...> ( ...> a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15' ...> ) ...> OR ...> ( ...> a.Target_Go_Live_Date = '' AND a.Finish_Date > substr(date('now'),1,4) || '-01-15' ...> ) ...> ) ...> AND ...> a.InsertDate = o.pl_insert ...> WHERE a.ProjID = b.ProjID ...> group BY a.ProjID ...> ; Run Time: real 0.031 user 0.000000 sys 0.000000 Error: no such column: o.ab_insert sqlite> Why do I get no such column? Thanks.

josé

(6) By David Raymond (dvdraymond) on 2020-09-16 16:44:36 in reply to 5 [link] [source]

Again, because the FROM clause of your select statement does not include any table named or aliased as "o".

The only things in your FROM clause are "Project_List AS a" and "ABT_Budget AS b". "o" does not appear in there anywhere.

In particular, "maxes" which you made with your CTE does not show up in your query, so all the stuff you have at the start for defining "maxes" is thrown out and not used, because you don't use it anywhere.

You alias a subquery as "o" inside the definition of "maxes". That alias of "o" only works inside that definition of "maxes", you can't use it anywhere else.

(8) By Keith Medcalf (kmedcalf) on 2020-09-16 18:59:24 in reply to 5 [link] [source]

From the SELECT after the WITH clause,

  • after the word FROM insert "maxes AS o JOIN" (without the quotes)
  • remove the word LEFT
  • change the word ON to WHERE
  • change the word WHERE to AND

(9) By jose isaias cabrera (jicman) on 2020-09-16 20:01:48 in reply to 8 [source]

Thanks, Keith.

(10) By Keith Medcalf (kmedcalf) on 2020-09-16 20:35:18 in reply to 9 [link] [source]

Here is another alternate and more succinct spelling that should obtain the same results:

  SELECT a.*, b.AnnualDossier as Dossier
    FROM (
          SELECT DISTINCT ProjID
            FROM Project_List
         ) as o,
    JOIN Project_List as a
      ON a.ProjID == o.ProjID
     AND a.InsertDate == (
                          select max(InsertDate)
                            from Project_List
                           where ProjID == o.ProjID
                          )
    JOIN ABT_Budget as b
      ON b.ProjID == o.ProjID
     AND b.InsertDate == (
                          select max(InsertDate)
                            from ABT_Budget
                           where ProjID == o.ProjID
                          )
   WHERE a.PMO_Board_Report != 'No'
     AND CASE WHEN a.Target_Go_Live_Date == ''
              THEN a.Finish_Date > substr(date('now'),1,4) || '-01-15'
              ELSE a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15'
         END
ORDER BY o.ProjID
;

(11.1) By Keith Medcalf (kmedcalf) on 2020-09-16 20:45:24 edited from 11.0 in reply to 9 [link] [source]

Ooops. The CASE is not exactly the same as the AND/OR conditions, so it should be:

  SELECT a.*, b.AnnualDossier as Dossier
    FROM (
          SELECT DISTINCT ProjID
            FROM Project_List
         ) as o,
    JOIN Project_List as a
      ON a.ProjID == o.ProjID
     AND a.InsertDate == (
                          select max(InsertDate)
                            from Project_List
                           where ProjID == o.ProjID
                          )
    JOIN ABT_Budget as b
      ON b.ProjID == o.ProjID
     AND b.InsertDate == (
                          select max(InsertDate)
                            from ABT_Budget
                           where ProjID == o.ProjID
                          )
   WHERE a.PMO_Board_Report != 'No'
     AND (
          (
           a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15'
          )
          OR
          (
           a.Target_Go_Live_Date = '' AND a.Finish_Date >  substr(date('now'),1,4) || '-01-15'
          )
         )
ORDER BY o.ProjID
;

(12) By jose isaias cabrera (jicman) on 2021-03-25 13:39:07 in reply to 11.1 [link] [source]

Thanks, Keith. I know you probably don't remember this one, but I was able to get this one to work with DISTINCT and it is the fastest yet: 2.34 secs loading. Thanks, Obrigado, gracias, Danke, etc.

josé

(4) By David Raymond (dvdraymond) on 2020-09-16 13:11:03 in reply to 1 [link] [source]

So "e" only exists inside the CTE definition of "maxes", you can't use it outside of that definition. You then go on to write a query that doesn't even use "maxes" in it anywhere.

Project_List AS a
and
ABT_Budget AS b

are the only two tables in your FROM clause, so what was the point of defining maxes in the first place if it's not used anywhere?

You're not even trying to references maxes itself or the fields of maxes, you're trying to reference one of the fields of one of the tables used to create maxes. So I'm not really sure what you're trying to do here.

(7) By jose isaias cabrera (jicman) on 2020-09-16 18:25:48 in reply to 4 [link] [source]

Thanks, David. Long story short, my data keeps growing and some of the queries that were fast at the beginning of the year are becoming slower and slower. I am trying to speed them up with the novice SQL talent I possess. So, this query, SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID AND b.InsertDate = (SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID) WHERE a.ProjID IN ( SELECT ProjID FROM Project_List WHERE 1=1 AND PMO_Board_Report != 'No' AND ( ( Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15' ) OR ( Target_Go_Live_Date = '' AND Finish_Date > substr(date('now'),1,4) || '-01-15' ) ) AND InsertDate = ( SELECT MAX(InsertDate) FROM Project_List WHERE 1=1 AND PMO_Board_Report != 'No' AND ( ( Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15' ) OR ( Target_Go_Live_Date = '' AND Finish_Date > substr(date('now'),1,4) || '-01-15' ) ) ) ) AND a.InsertDate = ( SELECT MAX(InsertDate) FROM Project_List WHERE a.ProjID = ProjID ) group BY a.ProjID; Takes about 12-20 seconds depending, Run Time: real 12.539 user 10.765625 sys 0.468750 I am trying to speed it up by following Keith's suggestion which is to get the ProjID, and both InsertDates ahead of time, and using their values in the query. Using one of Keith's examples, I was able to minimize the response time from 30 secods to less than 2 seconds on another query. So, I was trying to follow the same idea to try to fix the query above in which I failed miserably. So, I will go back to the drawing board and try to re-do this one and see if I can get any speed, and also the same output.