Selecting Field Defined as L in SQL Server
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
Software/Hardware used:
Just upgraded to V6R1
ASKED:
March 1, 2011 7:06 PM
UPDATED:
March 3, 2011 8:02 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
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
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/FNComments 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
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/FLThank you so much!!! I have been banging my head on the wall about this for a week!!