Executing SQL statements in DataStage

[yaminids]

Hello friends,

I am trying to execute a SQL statement from DataStage. I have two tables 'A' and 'B" in the input. In table 'A' I have 'NAME' and 'SID' columns. For every row in table 'A' I have to look into table 'B' and find how many times 'SID' is repeated for that particular name. I am trying to execute the following SQL statement:
SELECT COUNT(*) FROM Table B where TableA.SID = TableB.SID

Can anyone help me with this as this is the first time I am doing such kind of execution from DataStage.

Thanks a lot in advance
-Yamini

[ray.wurlod]

Your SQL is wrong. What you should be doing is

Code: Select all

SELECT COUNT(TableB.SID) FROM TableA,TableB where TableA.SID = TableB.SID 

(there are two table names in the FROM clause, and the counted column is exactly specified, which can be more efficient than * in certain databases).

Build your SQL in whatever stage you're using in exactly that way.

• The General tab becomes the FROM clause; put both table names in the Table Name field, separated by a comma.
  
  The Columns tab becomes the SELECT clause; put one column there (whatever name you like, data type INTEGER) and make its derivation COUNT(TableB.SID).
  
  Specify the join criterion on the Selection tab, though without the word WHERE (DataStage will fill this in automatically).

View the resultant SQL on the View SQL tab. Voila!

[yaminids]

Thank you very much Ray

-Yamini

[yaminids]

Hello Ray,

I followed your reply and got the required results. Now I am trying to do the same thing differently.

The data in Table A is:

NAME SID
--------------------------
AAAA 1234
BBBB 5678
CCCC 9012
DDDD 3456

My intension is to read a record from Table A and for every occurence of 'NAME' (eg. AAAA) in Table A, I have to find the Count of its occurence in Table B. Can you help me with this.

Thank you very much in advance
-Yamini

[scottr]

select count(tableb.name) from tablea,tableb where tablea.name=tableb.name

[ray.wurlod]

What Scott said. This is basic SQL - not really a DataStage issue at all.

[Sainath.Srinivasan]

You may need to add a group-by.

[yaminids]

Hello friends,

I followed your suggestions and got a partial answer.

For example I have two tables A and B. The contents in Table A are:

AAAA 1111
BBBB 2222
CCCC 3333
DDDD 4444

and the contents in Table B are:

AAAA
AAAA
CCCC
BBBB
BBBB
AAAA
DDDD
DDDD
CCCC
CCCC
--------------------------------------------------------------------
When I executed the above SQL script with GROUP BY NAME, I got the following result:
3
3
2
2

I am trying to get the result in the following format:

Name Count
------------------
AAAA 3
BBBB 2
CCCC 3
DDDD 2

I would highly appreciate if anyone of you could help me with this

-Yamini

[Sainath.Srinivasan]

Add the key column as part of the select.