On 23-Jul-2016 15:00 -0500, GioArt wrote:
I would need to know if is it possible to concat two fields inside
the WHERE Clause
Yes, that is possible. Note: Best to use the CONCAT keyword\operator
instead of the || operator, as the latter operator is defined by the
variant "vertical bar" character [repeated\doubled].
i.e. my file's fields: Item, Date, Time
Offer the actual DDL; i.e. offer the CREATE TABLE statement. Not
having offered the DDL, a reader can only _guess_ at what might be an
appropriate response; an attempt to reply covering all possible
variations would be a daft endeavor. Might as well also offer a few row
values to populate the table, with data that ensures ability to test the
boundary\edge cases. Without that, offered replies are more likely than
not, to be faulty, not merely for incorrectly matching their reply with
what the OP actually has [but has not revealed] for DDL and data, but
because the data they chose "worked" for them, despite their review was
not thorough enough to know their chosen expression was faulty if
applied across all possible values.
Select * from myfile where (date||time) >= 20160723180000
That can be done, and is syntactically correct, but may not pass
data-checking rules. Also beware the possibility for the effects of
implicit casting. And depending on the actual data-types and attributes
of the columns, as well as the actual data values, the results might not
be preferred.
Here is a variation on the above query that runs [apparently with
success], but is not really /functional/; i.e. returns what likely is to
be perceived as /unexpected/ results according to a cursory review, and
requiring further study to understand where things went wrong, to know
that the issue is usage vs defect. The query selects only three of the
four values conspicuously expected to represent a date\time *equivalent*
and\or *beyond* the compare-value. Review the results of the query as
written, then again after having changed from using the *GE (>=)
predicate to using a *LT (<) predicate; review for why the value that
intends to represent 2016-07-24.00.00.00 is deemed to be Less Than the
compare value intending to represent 2016-07-23.18.00.00:
with
daft (d, t) as
( values
(20160722 , 100000) ,(20160723 , 100000) ,(00090723 , 100000)
,(20160722 , 000000) ,(20160723 , 000000) ,(00900723 , 000000)
,(20160722 , 180000) ,(20160723 , 180000) ,(09000723 , 180000)
,(20160724 , 000000) ,(20160724 , 100000) ,(20160724 , 180000)
)
Select bigint(d concat t) as intcat
, daft.*
, bigint( 20160723180000) as cmp_val
from daft as daft
where (d concat t) >= 20160723180000 /* repeat, with < test */
order by d, t
Essentially, while (2016-07-24.00.00.00>=2016-07-23.18.00.00) may be
true, (201607240>=20160723180000) is false. Oops! Try again!
So, again, give the DDL and some sample data [including boundary
data], and then somebody can try to give a worthwhile and appropriate
reply; i.e. other than simply saying "Yes, you can"... and possibly
adding as I have "But you may not want to do that, depending on the
actual setup\data; or possibly even for other reasons, such as for
performance considerations, other choices about how to effect the query
might be better."
midrange.com
