scottklement.com

I'm concatenating a date and time field together using 'AS' to associate a field name to it which works fine.  I'm looking for a way to use a field name instead of the bulky concatenate each time I want to refer to the date/time.  The below SQL statement works but seems awful clunky. Is there a better way to do this? If I want to select more columns from the file, how would I do that using this format? Thanks!

select timestampdl from (select digits(hydtedl)||digits(timedl) as
timestampdl from cmpdetail) where timestampdl > '43655232547'

You might try a CTE (Common Table Expression) - something like this in general -

with xx(field1, field2) as (select infield1, infield2 from infile) select field1 from xx where field2 = 'value'

The columns of xx are listed, their attributes are either as they are from infile or some expression in the select column list.

Your example might be like this -

with xx(timestampdl) as (select digits(hydtedl) || digits(timedl) from cmpdetail)
select timestampdl from xx where timestampdl > '43655232547'

I think that gives the ability to have all the columns you want in xx, and you can use their names as listed after xx. You might want to use casts or char conversions in the CTE.

HTH
Vern

Thank you for taking the time to explain this to me. I was able to get it to work following your example.