SQL Select Assigning a Variable Name to Concatenated Fields

Discussions relating to writing software in ILE RPG (RPG IV). This includes both fixed and free format RPG.
Post Reply
bbunney
Posts: 45
Joined: Wed Jul 06, 2022 7:52 pm

SQL Select Assigning a Variable Name to Concatenated Fields

Post by bbunney »

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'
vhamberg
Posts: 12
Joined: Thu Jul 29, 2021 1:33 am

Re: SQL Select Assigning a Variable Name to Concatenated Fields

Post by vhamberg »

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
bbunney
Posts: 45
Joined: Wed Jul 06, 2022 7:52 pm

Re: SQL Select Assigning a Variable Name to Concatenated Fields

Post by bbunney »

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