FB-3 SELECT WHERE

[Henry Olive 5]

Good Day,

My Table :

ID...TNAME..TDATE................INVNO

1......AA...........01/06/2022

2......BB............01/06/2022.........22

3......AA...........02/06/2022.........33

4......AA...........10/06/2022.........44

 

Requested Result :

ID....TNAME.....TDATE..................INVNO

1........AA.............01/06/2022

4........AA............10/06/2022..........44

 

Lets say today = 13/06/2022  (dd/mm/yyy)

and i want to show TNAME='AA'  and

INVNO IS NULL (Whatever TDATE is )   **or**  INVNO IS NOT NULL but TDATE >= 03/06/2022 ( All 10 Days old records )

 

My Sql :

SELECT * FROM MYTABLE
WHERE TNAME IN ('AA') and (TDATE >= (SELECT MAX(TDATE) FROM MYTABLE ) - 10  or (X.INVRNO IS NULL))

with above Sql i dont see ID=4 record (Its INVNO is not null  BUT Its Date is just 3 days old, that is i should see it  )

 

Thank You

 

 

 

 

 

 

 

 

 

 

 

[Stano 139]

That's right. I will translate:
SELECT MAX (TDATE) FROM MYTABLE) - 10 = 3.6.2022.

Note: 10 <> 3

The condition should be: MyDate <= RequiredDate

[Serge_G 85]

Hi,

 

- what is this X.INVRNO  ? Your test table only contains INVNO column !

- use DateAdd Firebird function to compute your old date parameter

 

DATEADD(-10 DAYS FROM SELECT MAX(TDATE) FROM MYTABLE ))

 

or

SELECT MAX(DATEADD(-10 DAYS FROM TDATE)) FROM MYTABLE

 

I am not  fan of sub-querys I should certainly prefer a Delphi Code based (we are in a Delphi forum 😉) and a  Firedac query with parameters and  macro (for the in clause) 

 

var d : variant;
begin
YourFDquery.Close;
d:=FDConnection.ExecSQLScalar('SELECT MAX(DATEADD(:g DAYS FROM TDATE)) FROM MYTABLE',[-10]) // in this way you can change day number
if not VarisEmpty(d) then
begin
YourFDQuery.SQl.text('SELECT * FROM MYTABLE WHERE (TDATE >= :d or INVNO IS NULL) &inmacro');
YourFDQuery.ParamByName('d').asDateTime:=d;
yourFDquery.MacroByName('inmacro').asRaw:='AND TNAME IN (''AA'')'; // so you can easily change, i.e *using format or clear clause  
yourFDQuery.Open;
end;

Ok, I change position of the IN clause to easily clear the clause but, you can modify SQL text to

YourFDQuery.SQl.text('SELECT * FROM MYTABLE WHERE &inmacro (TDATE>=:D OR INVNO IS NULL)');
YourFDQuery.ParamByName('d').asDateTime:=d;
yourFDquery.MacroByName('inmacro').asRaw:='TNAME IN (''AA'') AND';  

* "inmacro" can be computed by a format,

var mymacro : String;
begin
mymacro:=Format('TNAME IN (%s) AND',[list_of_quoted_tname]);

 

 

 

Edited June 13, 2022 by Serge_G

[Stano 139]

Serge_G - I have a question for you. Perhaps the founder of the topic forgives me.
What is the advantage of your solution over subquery? I see a lot of unnecessary code
I usually use subquery and I'm happy with it. Also because I don't know other techniques

Edited June 13, 2022 by Stano

[Serge_G 85]

Stano Ok, my response is in three parts.
 

1- The first is only a Firebird compliant SQL on how to use DATEADD function, and for the question asked, just sufficient.
2- Why I am not fan of the subquery ? Because it is evaluated at each row of MYTABLE .

 

Perhaps a CTE should be better, but with the so poor data sample I am lazy  to test over

 

WITH X AS (SELECT MAX(DATEADD(-10 DAYS FROM TDATE)) MD FROM MYTABLE)
SELECT M.ID,M.TNAME,M.TDATE,M.INVNO FROM MYTABLE M JOIN X ON 1=1  
 WHERE M.TNAME='AA' AND (TDATE>=X.MD OR M.INVNO IS NULL)

Note that I prefer the list of each column to an *   

 

3- Delphi side, yes there is code (much more lines and I don't create an runtime TFDQuery .... ) but queries with parameters is, I think is a very usefull technique (I use for years)   

 
 

[Stano 139]

1 - I didn't mean that

2 - It is true. Personally, I haven't encountered a performance issue yet. But I don't have many records. I learned to use "with x as". It's my favorite technique

3 - Many rows = many errors 

1 hour ago, Serge_G said:

and I don't create an runtime TFDQuery .... ) but queries with parameters is

interesting. There is no point in talking about the parameters. Without it, it is very bad.

I plan to move to UniDAC. I don't know if he supports it.

Thanks!

Edited June 13, 2022 by Stano

[Stano 139]

I don't understand "I'm not creating a TFDQuery runtime". TFDQuery is, of course, used in the example. Can you give a sample / description?

[Serge_G 85]

Something like :

const SQL = // asSQltext
with TFDQuery.Create(self) do
 begin
  connection:=fdconnection;
  SQL.Text:=SQL;
  //parambyName
  //macrobyname
  Open;
  // processing
  Close;
 end;

or

const SQL = 'your sql';
var Q : TFDQuery;

Q:=TFDQuery.Create(self);
try
 Q.Connection:=FDConnection;
 Q.SQL.Text:=SQL;
 //Q.ParamByName
 //Q.macrobyName
 Q.Open;
 // processing
 Q.Close;
finally
 Q.Free;
end; 

 

[haentschman 92]

Hi...

 

NOT with WITH:

with TFDQuery.Create(self) do

[Stano 139]

I checked the translation where "and I don't create an runtime TFDQuery"
But it is also created in the demo. There is a bad translation (Google translator) or a communication error somewhere

I never use a construction with the "with"

I already understood that - "with"

Edited June 14, 2022 by Stano

[Serge_G 85]

37 minutes ago, Stano said:

I checked the translation where "and I don't create an runtime TFDQuery"

not my mother tongue too

 

37 minutes ago, Stano said:

a communication error somewhere

Yes, here I miss some words I think

 

I am not a fan of the WITH but, sometimes using it by lazyness 

Edited June 14, 2022 by Serge_G

[Pat Heuvel 1]

select *
from
    mytable
where
    tname = 'AA' and
    invno is null or
    datediff(day, current_date, tdate) >= 10

[Henry Olive 5]

Thank you SO MUCH  Stano, Serge, Pat