MF syntaxing for aliasing

BigFoot · Post by **BigFoot** » Tue Jan 09, 2007 6:19 am

Hi all,

I am developing a job using Mainframe tables, where i am getting a conflict between two fields in different tables with the same name which i need as output.

so, can some one please suggest me the syntax for aliasing in MF job.

Thanks

kumar_s · Post by **kumar_s** » Tue Jan 09, 2007 6:32 am

Hi Vinay,
Welcome to DsXchange :D !!!!
Is it with Source are target? Its its with source and if the issue is with Datastage jobs, you can extract and change the name of the columns later in the stages. You need to explain in bit more detailed manner on you design of you job.

BigFoot · Post by **BigFoot** » Tue Jan 09, 2007 7:05 am

the tables are in the source...

I am joining two tables both of which have a field with the same name (with different values of course) and that field is NOT my joining key.
but i need both fields in my output.

the problem is not in extraction but in writing the query. I have to alias the field so that DS understands the field name.

anyway I know the syntax for alias in Oracle. I dont have the knowledge of MF jobs.

Example query-

SELECT SCHEMA.TABLE1.FIELD1 AS ABC, SCHEMA.TABLE2.FIELD1 AS EFG FROM TABLE1, TABLE2.

I also tried the same thing in MF(ODBC stage) but it didn't work.

Thanks

kumar_s · Post by **kumar_s** » Tue Jan 09, 2007 4:04 pm

Have you tried just SELECT SCHEMA.TABLE1.FIELD1 ABC.....
What is your job desing. If you have any transformer or modify or any other stage which aids renaming the column, you can do it before joining the columns in Join stage.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 09, 2007 7:18 pm

DataStage does not provide inherently for aliases in mainframe jobs. However, if you specify a valid alias in the SQL, that will be passed to DB2/390 and honoured.

BigFoot · Post by **BigFoot** » Tue Jan 09, 2007 11:26 pm

Have you tried just SELECT SCHEMA.TABLE1.FIELD1 ABC.....

i also tried that...but

if you specify a valid alias in the SQL, that will be passed to DB2/390 and honoured.

please specify the syntax for aliasing...

Thanks

Yuan_Edward · Post by **Yuan_Edward** » Wed Jan 10, 2007 12:42 am

I didn't try but just give you a hint:
You said you are using ODBC stage, then use user-defined SQL and follow the syntax of standard SQL for column aliasing.

BigFoot wrote:
Have you tried just SELECT SCHEMA.TABLE1.FIELD1 ABC.....
i also tried that...but

if you specify a valid alias in the SQL, that will be passed to DB2/390 and honoured.
please specify the syntax for aliasing...

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 1:51 am

Alas, there's no ODBC stage in mainframe jobs.

DSguru2B · Post by **DSguru2B** » Wed Jan 10, 2007 2:52 pm

ray.wurlod wrote:Alas, there's no ODBC stage in mainframe jobs.

Its a px job. The mainframe tables are visible to his px job.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 3:04 pm

Shouldn't the aliases be on the tables, rather than on the fields?

Code: Select all

SELECT ABC.FIELD1, EFG.FIELD1 FROM SCHEMA1.TABLE1 ABC, SCHEMA2.TABLE2 EFG WHERE ABC.FIELD1 = EFG.FIELD1

Yuan_Edward · Post by **Yuan_Edward** » Wed Jan 10, 2007 4:43 pm

I have never used MF stages, but the author said he tried to use ODBC stage...

Would be insterested in learning something about MF stages

BigFoot wrote:the tables are in the source...

I also tried the same thing in MF(ODBC stage) but it didn't work.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Jan 10, 2007 4:57 pm

First, you need to buy DataStage Enterprise MVS Edition licence.

BigFoot · Post by **BigFoot** » Wed Jan 10, 2007 11:41 pm

Hi All,

Thanks for all of u for giving me useful suggestions...my issue is resolved...

Have you tried just SELECT SCHEMA.TABLE1.FIELD1 ABC..... What is your job desing. If you have any transformer or modify or any other stage which aids renaming the column, you can do it before joining ...

this is the right solution...
my apologies to kumar...