Recent

Author Topic: [SOLVED] SQLite: Selected fields on result  (Read 818 times)

totya

  • Hero Member
  • *****
  • Posts: 720
[SOLVED] SQLite: Selected fields on result
« on: February 23, 2023, 11:17:10 am »
Hi!

For example I have a simple query (draft untested):

Code: Pascal  [Select][+][-]
  1. SELECT
  2.         a.x1 AS "a-x1",
  3.         a.x2 AS "a-x2",
  4.         b.x1 AS "b-x1",
  5.         b.x2 AS "b-x2"
  6. FROM
  7.         a,
  8.         b
  9. WHERE
  10.         a.x1 = b.x1
  11.         AND a.x2 <> b.x2
  12.  

it's simple, but I want to see only the a.x1, a.x2, and b.x2 fields in the dbgrid (except b.x1).

Is it possible anyhow with SQLite command or only solution with add required columns to the dbrig manually?
« Last Edit: February 23, 2023, 11:45:13 am by totya »

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: SQLite: Selected fields on result
« Reply #1 on: February 23, 2023, 11:28:10 am »
With SQL you can leave out columns you don't need:
Code: MySQL  [Select][+][-]
  1.         a.x1 AS "a-x1",
  2.         b.x1 AS "b-x1",
  3.         b.x2 AS "b-x2"
  4.         a,
  5.         b
  6.         a.x1 = b.x1
  7.         AND a.x2 <> b.x2

If you need that column, beside manually adding columns to DBGrid, you could add it/remove it programmatically.
Something like this:
Code: Pascal  [Select][+][-]
  1. function GetDBGridColumn(aDBGrid:TDBGrid; aFieldName:string):TColumn;
  2. var iColidx:integer;
  3. begin
  4.   for iColidx:=0 to aDBGrid.Columns.Count-1 do
  5.     if aDBGrid.Columns[iColidx].FieldName=aFieldName then begin
  6.       Result:=aDBGrid.Columns[iColidx];
  7.       Exit;
  8.     end;
  9.   Result := nil;
  10. end;
  11.  
  12. var column: TColumn;
  13.  
  14. ...
  15.  
  16. SQLQuery1.SQL.Text := '...';
  17. SQLQuery1.Open;
  18.  
  19. // E.g. DBGrid1 connect to SQLQuery1.
  20. column := GetDBGridColumn(DBGrid1, 'a-x2');
  21. If column <> nil then
  22.   column.Visible := False;

totya

  • Hero Member
  • *****
  • Posts: 720
Re: SQLite: Selected fields on result
« Reply #2 on: February 23, 2023, 11:44:59 am »
With SQL you can leave out columns you don't need:

Wow, thank you for the quick answers! I tried to the first solution, it's working!

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [SOLVED] SQLite: Selected fields on result
« Reply #3 on: February 23, 2023, 12:33:33 pm »
hrhrhrhr
Code: SQL  [Select][+][-]
  1. SELECT
  2.         a.x1 AS "a-x1",
  3.         a.x2 AS "a-x2",
  4.         /*b.x1 AS "b-x1",*/
  5.         b.x2 AS "b-x2"
  6. FROM
  7.         a,
  8.         b
  9. WHERE
  10.         a.x1 = b.x1
  11.         AND a.x2 <> b.x2
  12.  
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

totya

  • Hero Member
  • *****
  • Posts: 720
Re: [SOLVED] SQLite: Selected fields on result
« Reply #4 on: February 23, 2023, 01:58:57 pm »
hrhrhrhr
Code: SQL  [Select][+][-]
  1. SELECT
  2.         a.x1 AS "a-x1",
  3.         a.x2 AS "a-x2",
  4.         /*b.x1 AS "b-x1",*/
  5.         b.x2 AS "b-x2"
  6. FROM
  7.         a,
  8.         b
  9. WHERE
  10.         a.x1 = b.x1
  11.         AND a.x2 <> b.x2
  12.  

I got already good answer, the point is that the field (column) you don't want must be omitted from the select, but yes, the good answer exactly what you wrote.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [SOLVED] SQLite: Selected fields on result
« Reply #5 on: February 23, 2023, 03:52:39 pm »
hrhrhrhr
Code: SQL  [Select][+][-]
  1. SELECT
  2.         a.x1 AS "a-x1",
  3.         a.x2 AS "a-x2",
  4.         /*b.x1 AS "b-x1",*/
  5.         b.x2 AS "b-x2"
  6. FROM
  7.         a,
  8.         b
  9. WHERE
  10.         a.x1 = b.x1
  11.         AND a.x2 <> b.x2
  12.  

I got already good answer, the point is that the field (column) you don't want must be omitted from the select, but yes, the good answer exactly what you wrote.
It's a Trick i developed for myself, when testing SQL-Statements.
Just comment out, what you don't need after the initial tests
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018