problem with sql statements with sqlite3
lazarus / 16-Databases

----------------------------------------------------------------------
davesimplewear · 2011-05-22 00:10

I have the following statements in a small program, they cause the
program to exit when run, if I try them in a sqlite manager they give
desired result, I can't see where the error is, I have run them b4
with no problem, maybe I can't see the forest for the trees, any help
would be appreciated. Lazarus 0.9.29 FPC 2.5.1.

Regards
Dave

Statement 1
  dgView.DataSource := Data.dsCurrent;
  dnView.DataSource := Data.dsCurrent;
  Data.vqCurrent.DisableControls;
  Data.vqCurrent.Close;
  Data.vqCurrent.SQL:='SELECT COUNTRY.COUNTRY , COIN.DESCRIPTION,
COIN.CONDITION, COIN.ISSUED, COIN.HELD, COIN.AVALUE FROM COUNTRY,COIN
WHERE        COUNTRY.MASTERID = COIN.MASTERID GROUP BY
COUNTRY.COUNTRY,
COIN.DESCRIPTION,COIN.CONDITION,COIN.ISSUED,COIN.HELD, COIN.AVALUE'; 
              ;
  Data.vqCurrent.EnableControls;
  Data.vqCurrent.Open;

Statement 2

dgView.DataSource := Data.dsConsigned;
  dnView.DataSource := Data.dsConsigned;
  Data.vqConsigned.DisableControls;
  Data.vqConsigned.Close;
  Data.vqConsigned.SQL:='SELECT COUNTRY.COUNTRY, COIN.DESCRIPTION,
COIN.CONDITION, COIN.ISSUED, COIN.HELD, COIN.AVALUE FROM COUNTRY,COIN
WHERE COIN.CONSIGNED = 1 AND COUNTRY.MASTERID = COIN.MASTERID GROUP BY
COUNTRY.COUNTRY,
COIN.DESCRIPTION,COIN.CONDITION,COIN.ISSUED,COIN.HELD, COIN.AVALUE';
  Data.vqConsigned.EnableControls;
  Data.vqConsigned.Open;

Statement 3

dgView.DataSource := Data.dsSold;
  dnView.DataSource := Data.dsSold;
  Data.vqSold.DisableControls;
  Data.vqSold.Close;
  Data.vqSold.SQL:='SELECT COUNTRY.COUNTRY, COIN.DESCRIPTION,
COIN.CONDITION, COIN.ISSUED, COIN.SALEPRICE FROM COUNTRY,COIN WHERE
COIN.SOLD = 1 AND COUNTRY.MASTERID = COIN.MASTERID GROUP BY
COUNTRY.COUNTRY,
COIN.DESCRIPTION,COIN.CONDITION,COIN.ISSUED,COIN.SALEPRICE';
  Data.vqSold.EnableControls;
  Data.vqSold.Open;

Statement 4

dgView.DataSource := Data.dsSearch;
  dnView.DataSource := Data.dsSearch;
  Data.vqSearch.DisableControls;
  Data.vqSearch.Close;
  Data.vqSearch.SQL:='SELECT COUNTRY.COUNTRY, COIN.DESCRIPTION,
COIN.CONDITION, COIN.ISSUED, COIN.HELD, COIN.AVALUE FROM COUNTRY,COIN
WHERE COIN.DESCRIPTION LIKE ''FENTRY.SEARCH.TEXT'' AND
COUNTRY.MASTERID = COIN.MASTERID GROUP BY COUNTRY.COUNTRY,
COIN.DESCRIPTION, COIN.CONDITION, COIN.ISSUED, COIN.HELD, COIN.AVALUE
' ;
  Data.vqSearch.EnableControls;
  Data.vqSearch.Open;

----------------------------------------------------------------------
LuizAmérico · 2011-05-22 00:26

Probably related to a unitialized variable

Try to run the program in the debugger (stepping) to see where the
problem occurs

Also run in the command line to see the output

----------------------------------------------------------------------
davesimplewear · 2011-05-22 08:37

its got me puzzled, ran the statements in the terminal, no problem, in
sqliteman, worked properly, but won't work in the program??

Regards
Dave

----------------------------------------------------------------------
eny · 2011-05-22 11:00

Why do you use GROUP BY when there are no aggregate functions?

----------------------------------------------------------------------
davesimplewear · 2011-05-24 03:02

I was using GOUP for the Country as an aggregate, still puzzled
getting a different result in the program to SqliteMan, I have
attached screenshots of the results, the Sqliteman is what I am after
and have had previously.

Regards
Dave

----------------------------------------------------------------------
davesimplewear · 2011-05-24 05:46

sorry "GROUP"

----------------------------------------------------------------------
eny · 2011-05-24 07:39

Quote from: davesimplewear on May 24, 2011, 05:02:24 amI was using
GROUP for the Country as an aggregateIt looks like you are using it as
a DISTINCT because you are not aggregating anything.
You can probably drop the group by altogether if you use DISTINCT
(http://www.sqlite.org/lang_select.html).

----------------------------------------------------------------------
davesimplewear · 2011-05-25 09:13

Ok, I think the problem lies with having to have a table name in
sqlite3dataset and nominating the table where most of the info is, I
have nominated a master table with country data and a detail table
with data about coins, I nominated the detail table as the table to
run the query on and it is only returning data associated with that
table as shown in the previous screenshot, where the tables don't
depend on a single table I get the result that I want, any suggestions
on how I can get around this problem would be appreciated.

Regards
Dave

----------------------------------------------------------------------
Arbee · 2011-05-25 12:45

Are you saying that you do not get results if there is no
corresponding entry in the master table?  If so, you need to run a
query using a LEFT OUTER JOIN.

----------------------------------------------------------------------
davesimplewear · 2011-05-25 22:05

OK I got around not having a tablename in the result datasets that I
run the queries on and VOILA it all works now, thank you to those made
suggestions.

Regards
Dave