SQLite Forum

Thoughts on why this is not working?
Login

Thoughts on why this is not working?

(1) By jose isaias cabrera (jicman) on 2020-05-12 18:01:09 [link] [source]

Greetings!

This command brings,

sqlite> select ProjID, Methodology,InsertDate from Project_List where ProjID='PR0000014752' ORDER BY InsertDate; PR0000014752||2018-07-30_05-43-20 PR0000014752||2018-08-01_04-41-32 [clip] PR0000014752||2020-03-06_06-51-32 PR0000014752||2020-03-09_08-51-14 PR0000014752||2020-03-10_06-56-06 PR0000014752|Waterfall|2020-03-13_06-29-19 PR0000014752|Waterfall|2020-03-18_12-06-38 PR0000014752|Waterfall|2020-03-19_08-14-33 sqlite> Then, this command, should bring an entry, sqlite> SELECT ProjID, ...> Updated_By, ...> InsertDate, ...> var, ...> oldv, ...> newv ...> FROM ...> ( ...> SELECT ProjID, ...> Updated_By, ...> InsertDate, ...> 'Methodology' as var, ...> ( ...> SELECT Methodology FROM Project_List WHERE ...> ProjID = o.ProjID ...> AND InsertDate < o.InsertDate ...> ORDER BY InsertDate DESC ...> LIMIT 1 ...> ) AS oldv, ...> Methodology as newv ...> FROM Project_List as o ...> ) ...> WHERE oldv <> newv ...> AND ProjID = 'PR0000014752' ...> ORDER BY InsertDate ASC; sqlite> But it does not. Thoughts? Complaints? Jokes? :-) Thanks.

josé

(2) By jose isaias cabrera (jicman) on 2020-05-12 18:26:04 in reply to 1 [link] [source]

Never mind. Apologies. For those of you playing at home, I had to use coalesce to bring non-null value. So, this now brings something,

sqlite> SELECT ProjID, ...> Updated_By, ...> InsertDate, ...> var, ...> oldv, ...> newv ...> FROM ...> ( ...> SELECT ProjID, ...> Updated_By, ...> InsertDate, ...> 'Methodology' as var, ...> ( ...> SELECT coalesce(Methodology,'null') FROM Project_List WHERE ...> ProjID = o.ProjID ...> AND InsertDate < o.InsertDate ...> ORDER BY InsertDate DESC ...> LIMIT 1 ...> ) AS oldv, ...> coalesce(Methodology,'null') as newv ...> FROM Project_List as o ...> ) ...> WHERE oldv <> newv ...> AND ProjID = 'PR0000014752' ...> ORDER BY InsertDate ASC; PR0000014752|Guzman Alcanfor, Pedro|2020-03-13_06-29-19|Methodology|null|Waterfall sqlite>

thanks for your good thoughts.

josé

(3) By Keith Medcalf (kmedcalf) on 2020-05-12 18:35:51 in reply to 1 [source]

Is the "empty" methodology NULL or a blank string? If NULL, then the comparison <> should be IS NOT ...

Secondly, should not the order by and ProjID constraint not be in the subselect?

  SELECT ProjID,
         Updated_By,
         InsertDate,
         var,
         oldv,
         newv
    FROM (
           SELECT ProjID,
                  Updated_By,
                  InsertDate,
                  'Methodology' as var,
                  (
                      SELECT Methodology
                       FROM Project_List
                      WHERE ProjID = o.ProjID
                        AND InsertDate < o.InsertDate
                   ORDER BY InsertDate DESC
                      LIMIT 1
                  ) AS oldv,
                  Methodology as newv
             FROM Project_List as o
            WHERE ProjID = 'PR0000014752'
         ORDER BY InsertDate ASC
         )
   WHERE oldv IS NOT newv;

(4) By jose isaias cabrera (jicman) on 2020-05-12 18:50:08 in reply to 3 [link] [source]

Is the "empty" methodology NULL or a blank string? If NULL, then the comparison <> should be IS NOT ...

Hmmm, that is the problems. Believe it or not, ss both. I get the data from a system, that apparently, would choose '' on some specific times, or null in others. For example, for this same SELECT, it works for some projects, but for others, it was not. So, I was killing myself trying to figure out why. So, I asked. But, then I figured that it must have been the data coming in with different values for nothingness: null or ''. So, coalesce is now working correctly. Thanks.

josé