rpg sql concat
Our database was designed with separate fields for cc, yy, mm and dd. I'd like to know if and how these fields can be combined into one field then order by on that field. Is this possible? I would need to know the syntax also. If this is not possible, then is there a another way to order by century, year, month and day in sql? Remember, we are using rpgle. Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 16, 2008 1:14 PM
UPDATED:
April 17, 2008 8:29 AM
Answer Wiki:
Can you please refine your question in terms of what you're trying to do?
For example:
1) Would you like to add a datetime column to the table and be able to populate the current date
(or a date data) directly from the database?
Would you like to be able to query date data between date ranges easily without breaking the
application?
--> In these cases, add a datetime column (datetime data type) and create a trigger that will
populate the 4 date fields when the datetime is populated and/or viceversa.
2) Would you like to get a concatenation of the 4 fields in the result but still order by the 4 fields?
--> In this case the query is simple, for example (assuming that all the fields are strings, otherwise
you should convert numbers to strings using the cast or convert functions):
SELECT 'Century=' + cc + ',Year=' + YY + ',Month=' + MM + ',Day=' + DD
FROM <your table>
ORDER BY CC,YY,MM,DD
OR
SELECT YY + '-' + MM + '-' + DD
FROM <your table>
ORDER BY CC,YY,MM,DD
OR
SELECT convert(char(4),YY) + '-' + convert(char(2),MM) + '-' + convert(char(2),DD)
FROM <your table>
ORDER BY CC,YY,MM,DD
Hope this helps.
Michelle.
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
Is there any reason why you need to use SQL for this? You could create a logical file with the date fields as key fields. Why do you need to concatenate the fields? Surely doing an ORDER BY or having the fields as key fields will give the same result?
Regards,
Martin Gilbert.