working in DB2-environment with Datamigrator GUI. This is my field in input table: FIELDNAME=ACTU_DAT, ALIAS=ACTU_DAT, USAGE=A8, ACTUAL=A8 An example is '20190105'
It has to be joined with this field in another table: FIELDNAME=DAT, ALIAS=DAT, USAGE=YYMD, ACTUAL=DATE Example is '2019-01-05'
In the source transforms calculator I putted this transformation DATECVT(ACTU_DAT, 'A8', 'YYMD')
But the inner join gives no result.
Where does it go wrong?
Regards RonThis message has been edited. Last edited by: FP Mod Chuck,
Personally I would do the opposite : convert the date into alpha (A8) and then join the two alpha. Or convert both into integer date (I8YYMD) or alpha date (A8YYMD) and then join them.
You need to be sure that both field have the same format and content. You problem is probably there where one field doesn't have the exact same format and content then the other.
Have you print them individually to compare and see if they have the same representation ?
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 14, 2019, 07:50 AM
Ronibi
Hoi Martin
you give three solutions: - convert the date into alpha (A8) and then join the two alpha - convert both into integer date (I8YYMD) and then join them or - convert both into alpha date (A8YYMD) and then join them.
Can you , as a learning option for me, show the three solutions?
Hi, Why dont you change the Usage from A8 to YYMD in the master file rather than converting it. Is there are reaons you want to keep format as A8 in the master file?
WF 8.2.04 Windows/Unix All Formats In Focus since 2006
February 14, 2019, 09:56 AM
Ronibi
Master file is used in multiple dataflows. I don't know what the effect will be in the other flows by changing the master.
You can add a DEFINE in the synonym and use that without affecting other users...
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
February 14, 2019, 01:54 PM
MartinY
quote:
Originally posted by Ronibi: Hi Martin
you give three solutions: - convert the date into alpha (A8) and then join the two alpha - convert both into integer date (I8YYMD) and then join them or - convert both into alpha date (A8YYMD) and then join them.
Can you , as a learning option for me, show the three solutions?
They are just format conversion and may or may not apply to your situation and/or input format.
You can use DEFINE and apply a DATECVT function to move from A8 to I8YYMD par example or to any other specified format The important thing is that at the end you are comparing two fields with the same format and content representation.
You should first look at individual field content after transformation and ensure that they can be compared.
It can have plenty of solutions, you need to find the proper one for you
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
February 14, 2019, 02:50 PM
Waz
quote:
quote: Master file is used in multiple dataflows
You can add a DEFINE in the synonym and use that without affecting other users...
FYI
With Relation DB's, you can have the same field multiple times.
I your case you could have: FIELDNAME=ACTU_DAT, ALIAS=ACTU_DAT, USAGE=A8, ACTUAL=A8 FIELDNAME=ACTU_YYMD, ALIAS=ACTU_DAT, USAGE=YYMD, ACTUAL=A8
BUT What is the format of the column in DB2 ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
February 15, 2019, 04:48 AM
Ronibi
Thnx. The problem in the last answer is that we always do a recreate of a master, not a refresh. So when I add something in the master, it will be lost once it is recreated because of some other change.
As I said before I already tried DATECVT
In the source transforms calculator I putted this transformation DATECVT(ACTU_DAT, 'A8', 'YYMD')