Selecting Field Defined as L in SQL Server

235 pts.
Tags:
Date convert
Embedded SQL
Field Defined L
SQL Server development
V6R1
I am building 4 part SQL select across 4 files using union alls. All 4 files have a date field which is one of the fields I am selecting. My problem is that on one file the date field is defined as L type where the other three files have a date field defined as 8S 0. Since the field I am selecting into has to be compatible across all 4 files and L data type is not an option in RPG....  How can I define or convert this L field so that it will pull it in to the D data type field I currently have defined? Or how can I define the field I am fetching into so that it will be compatible across all 4 files?

Software/Hardware used:
Just upgraded to V6R1
ASKED: March 1, 2011  7:06 PM
UPDATED: March 3, 2011  8:02 PM

Answer Wiki

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

Discuss This Question: 4  Replies

 
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
  • TomLiotta
    There shouldn't be much problem using RPG 'D'ate fields for DDS 'L' fields (or SQL DATE columns). You might need to compile your RPG with a H-spec that species your DATFMT(). By default, it should be DATFMT(*ISO). You might need to set DATFMT(*USA) or whatever makes your job, your program and your file consistent with each other. Use DSPFFD over the file to see how the field date format is defined. For the 8-digit zoned-decimal fields, you'll probably need to cast those as DATEs or convert your DATE column to some numeric value. I might choose to create VIEWs over the three files and have the casting to DATEs be done by the views. Without knowing the general structure of the program, it's hard to tell what would be useful. Tom
    125,585 pointsBadges:
    report
  • DanTheDane
    First of all, I recommend you to output to an 'L' ('native') date field, which I'll do this way: My two test-files has one field each, - file FL with an 'L'-date field named Lfield, and FN with an 8.0 numeric field named Nfield. I assume the Nfield has format 'ccyymmdd'. This SQL 'unions' the two files:
     SELECT date(substr(char(Nfield),1,4) || '-' ||
                substr(char(Nfield),5,2) || '-' ||
                substr(char(Nfield),7,2) )
     FROM mylib/FL
    UNION ALL
     SELECT Lfield
     FROM mylib/FN
    
    Comments on the above SQL: Since I chose my output date to be of type 'L' I must convert the 8 digit date to an 'edited' character field with the format of an 'L'-field. Therefore I cut out the pieces to concatenate into the 10-character string that is required to use the sql date function. This is done in the fist three statements. My next already file has its date fields as an 'L'-field, which I simply select. It should be easy for you to expand this code to include your four files. I hope this can be of help to you. DanF
    2,555 pointsBadges:
    report
  • DanTheDane
    Sorry, I got the two files in my previous post mixed up. Here is the right sql:
     SELECT date(substr(char(Nfield),1,4) || '-' ||
                substr(char(Nfield),5,2) || '-' ||
                substr(char(Nfield),7,2) )
     FROM mylib/FN
    UNION ALL
     SELECT Lfield
     FROM mylib/FL
    
    2,555 pointsBadges:
    report
  • Canuhp
    Thank you so much!!! I have been banging my head on the wall about this for a week!!
    235 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