Union operator problem

[deesh]

Hi Friends,
When i generated Union script in ODBC connector its showing the failure message. Please can check this one is correct or want to write some other way.
below are the errors

IIS-CONN-ODBC-000004] ODBC function {0} reported: SQLSTATE = 42S02: Native Error Code = 208: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.SR_MVGB_OCUSMA'.

SR_MVDE_OCUSMA,0: [IIS-CONN-ODBC-000004] ODBC function {0} reported: SQLSTATE = 42000: Native Error Code = 8 180: Msg = [IBM (DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (CC_OdbcDBStatement::executeSelect, file CC_OdbcDBStatement.cpp, line 1 140)

Script

SELECT OCUSAD.OPCUNO,
OCUSAD.OPADID,
OCUSAD.OPCUNM,
OCUSAD.OPCUA1,
OCUSAD.OPCUA2,
OCUSAD.OPCUA3,
OCUSAD.OPCUA4
FROM
dbo.SR_MVGB_OCUSAD AS OCUSAD
UNION
SELECT TR_UK_SLS_CUA_CD001.SAP_DESC,
TR_UK_SLS_CUA_CD001.SAP_VALUE
FROM
dbo.TR_UK_SLS_CUA_CD001;

[Aquilis]

In first select statement I can see 7 columns and second select statement you have mentioned only two columns, Then How could you union these two statements ?
What you want to do ? can you elaborate your requirement more ?

[deesh]

HI Aquilis,

Actually I need below condition requirement

If (Trim(OKCUA4='') and SR_TR_OCUSMA.OKCUA3 = SAP_DESC) Then SAP_VALUE Else if SR_TR_OCUSMA.OKCUA4 = SAP_DESC Then SR_TR_OCUSMA.SAP_VALUE else ''

Here is Two Tables

1 SR_TR_OCUSMA is one table
OKCUA3, OKCUA4 are the fields

2 SAP Table is second one
SAP_DESC, SAP_VALUE are the fields

Here is no common value are key in the both tables but i have to write this query from with two tables

[Aquilis]

Read both the table Data separately with the new unique numbers associated for each and then try to join on that particular unique number. Once joining both the tables ,in new transformer stage try to add your constraint
If (Trim(OKCUA4='') and SR_TR_OCUSMA.OKCUA3 = SAP_DESC) Then SAP_VALUE Else if SR_TR_OCUSMA.OKCUA4 = SAP_DESC Then SR_TR_OCUSMA.SAP_VALUE else ''
.

[deesh]

SAP Table having only 98 rows but Another table have more than 20000 rows how to use unique key for this.

[chulett]

Not really a DataStage problem. Best to work this out with a DBA or whomever does SQL support in your organization, get them to help you work this out. As noted, not only must you select the same number of columns on each 'side' of the union, data types of matched columns must be compatible as well.

[kandyshandy]

HI Aquilis,

Actually I need below condition requirement

If (Trim(OKCUA4='') and SR_TR_OCUSMA.OKCUA3 = SAP_DESC) Then SAP_VALUE Else if SR_TR_OCUSMA.OKCUA4 = SAP_DESC Then SR_TR_OCUSMA.SAP_VALUE else ''

Here is Two Tables

1 SR_TR_OCUSMA is one table
OKCUA3, OKCUA4 are the fields

2 SAP Table is second one
SAP_DESC, SAP_VALUE are the fields

Here is no common value are key in the both tables but i have to write this query from with two tables

Deesh, Your original SQL and the above posting refers to different table names. Can you pls be more specific with the requirements? I guess this can be done in SQL or DS.

1. Keep SR_TR_OCUSMA as the main stream
2. Have 2 look up links (both refer to the same SQL)
3. From the main stream, link OKCUA3 to first look up link & link OKCUA4 to second look up link.

But again, your requirements is not clear. Be more specific.