Re: SQL Where with concat

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

+1

On 7/27/2016 11:19 AM, Hiebert, Chris wrote:
> --I would need to know if is it possible to concato two fields inside the WHERE Clause
> --Select * from myfile where (date|| time) >= 20160723180000
>
> Yes its possible.
>
> Should you do it? Probably Not.
>
> IMO, the biggest reason not to cast in the were clause comes down to data access.
>
> If you cast and then compare to some static value then you are forcing the SQL engine to retrieve each row from the database, perform a conversion, and then compare to some static value.
> That has the potential to be extremely inefficient, especially for large databases.
>
> You are "allowed" to do something like this:
> Select * from myfile where  CAST(  (digits(date)|| digits( time)) as dec( 15,0 )) >= 20160723180000
>
> However the most efficiently sql would be to just compare against the fields individually. This can be even more efficient if you have indexes over the "date" and "time" fields.
>
> Select * from myfile where  (("date"  =  20160723 and "time" >= 180000 ) or "date" > 20160723)
>
> Chris Hiebert
> Senior Programmer/Analyst
> Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.