rpg sql concat

15 pts.
Tags:
Concatenate
Database
RPG
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Gilly400
    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.
    23,730 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following