Executing SQL statements in DataStage
Moderators: chulett, rschirm, roy
Executing SQL statements in DataStage
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your SQL is wrong. What you should be doing is (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.
Code: Select all
SELECT COUNT(TableB.SID) FROM TableA,TableB where TableA.SID = TableB.SID
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Executing SQL statements in DataStage
Thank you very much Ray
-Yamini
-Yamini
Executing SQL statements in DataStage
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
You may need to add a group-by.
Last edited by Sainath.Srinivasan on Sat Apr 09, 2005 1:06 pm, edited 1 time in total.
Executing SQL statements in DataStage
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom