|
View previous topic :: View next topic
|
| Author |
Message |
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
Hi,
I have SQL query which is given below,
| Code: |
SELECT A.WRITING_AGENT_NUM
,A.AGNT_CASE_END_DT
,A.DISTRICT_OFFICE_CD
,A.AGENCY_NUM
,A.INDEX_NUM
,A.AGNT_CASE_STRT_DT
,A.WRIT_AGENT_SHR_PCT
,A.WRAG_SEQ_NUM
,' '
,' '
FROM TABLE A
WHERE A.CASE_NUM = '707003756'
AND A.WRAG_SEQ_NUM =
(SELECT MAX(C.WRAG_SEQ_NUM)
FROM TABLE C
WHERE C.CASE_NUM = A.CASE_NUM)
|
The out put of the query is,
| Code: |
WRITING_AGENT AGNT_CASE .. AGENCY_NUM INDEX_NUM ...
_NUM _END_DT
1 12/31/9999 88 1
1 01/01/2012 88 1
2 12/31/9999 90 2
2 01/01/2012 90 2
|
But i need only one row for each Writing agent num. Please advise on this.
Thanks in advance. |
|
| Back to top |
|
 |
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
| you may have to relook at column AGNT_CASE_END_DT, as it has different values in both rows, are you sure this date value doesnt matter ? |
|
| Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
As a matter of fact, the values of AGNT_CASE_END_DT doesn't matter.
But i have figured a way of doing it. But, i will have to split the query into two different SQLs. It does my job.
Here is the solution i thought of,
| Code: |
SELECT DISTINCT A.CASE_NUM, A.WRAG_SEQ_NUM,A.WRITING_AGENT_NUM
FROM TABLE A
WHERE A.CASE_NUM = '707003756'
AND A.WRAG_SEQ_NUM =
(SELECT MAX(C.WRAG_SEQ_NUM)
FROM TABLE C
WHERE C.CASE_NUM = A.CASE_NUM)
Then with the above results i will write another query which will fetch other columns and this query will have 'FETCH FIRST ROW ONLY'.
Let me know if there is any other solution for this. |
|
|
| Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
| you can try taking out AGNT_CASE_END_DT column in your first query, you should be able to achieve your result. Test and try. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
This untested (i don't have the tables) should work for DB2V9 up
| Code: |
SELECT b2.WRITING_AGENT_NUM
,b2.AGNT_CASE_END_DT
,b2.DISTRICT_OFFICE_CD
,b2.AGENCY_NUM
,b2.INDEX_NUM
,b2.AGNT_CASE_STRT_DT
,b2.WRIT_AGENT_SHR_PCT
,b2.WRAG_SEQ_NUM
,' '
,' '
FROM (SELECT CASE_NUM
, WRITING_AGENT_NUM
, max(WRAG_SEQ_NUM) as wrag_seq_num
FROM TABLEA
WHERE CASE_NUM = '707003756'
group by CASE_NUM
, WRITING_AGENT_NUM ) A
, table (select * from tableA B where
where a.CASE_NUM = b.CASE_NUM
and a.wrag_seq_num = b.wrag_seq_num
and a.writing_agent_num = b.writing_agent_num
fetch first 1 row only) B2 |
|
|
| Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
| Code: |
SELECT b2.WRITING_AGENT_NUM
,b2.AGNT_CASE_END_DT
,b2.DISTRICT_OFFICE_CD
,b2.AGENCY_NUM
,b2.INDEX_NUM
,b2.AGNT_CASE_STRT_DT
,b2.WRIT_AGENT_SHR_PCT
,b2.WRAG_SEQ_NUM
,' '
,' '
FROM TABLE (SELECT CASE_NUM
, WRITING_AGENT_NUM
, MAX(WRAG_SEQ_NUM)
AS WRAG_SEQ_NUM
FROM TUWCMAG_COMM_AGENT
WHERE CASE_NUM = '707003756'
GROUP BY CASE_NUM
, WRITING_AGENT_NUM ) A
, TABLE (SELECT * from TUWCMAG_COMM_AGENT A,
TUWCMAG_COMM_AGENT B
WHERE A.CASE_NUM = A.CASE_NUM
AND A.WRAG_SEQ_NUM = B.WRAG_SEQ_NUM
AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM
FETCH FIRST 1 ROW ONLY) B2
|
When i ran this query, i am getting the below errro.
SQL error at or before FETCH (line 23, position 10)
Please advise. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Which release of DB2 are you using? |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
during adaptation of the query you made a few mistakes.
| Quote: |
...
TABLE (SELECT * from -- TUWCMAG_COMM_AGENT A, This should not be here
TUWCMAG_COMM_AGENT B
WHERE A.CASE_NUM = B.CASE_NUM -- This should compare A to B, not A to A
AND A.WRAG_SEQ_NUM = B.WRAG_SEQ_NUM
AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM
FETCH FIRST 1 ROW ONLY) B2 |
and as already mentioned and asked : You DB2 should be in Version 9 New Function Mode or higher. |
|
| Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
Thanks for the responses.
I am currently using DB2 V09.02.00.
But still after the above changes, i am getting the same db2 error.  |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Show what you have used and any output/errors you are getting.
I have no idea which is Psychic Day this week, so it's not today :-) |
|
| Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
| Code: |
SELECT b2.WRITING_AGENT_NUM
,b2.AGNT_CASE_END_DT
,b2.DISTRICT_OFFICE_CD
,b2.AGENCY_NUM
,b2.INDEX_NUM
,b2.AGNT_CASE_STRT_DT
,b2.WRIT_AGENT_SHR_PCT
,b2.WRAG_SEQ_NUM
,' '
,' '
FROM TABLE (SELECT CASE_NUM
, WRITING_AGENT_NUM
, MAX(WRAG_SEQ_NUM)
AS WRAG_SEQ_NUM
FROM TUWCMAG_COMM_AGENT
WHERE CASE_NUM = '707003756'
GROUP BY CASE_NUM
, WRITING_AGENT_NUM ) A
, TABLE (SELECT * from TUWCMAG_COMM_AGENT B
WHERE A.CASE_NUM = B.CASE_NUM
AND A.WRAG_SEQ_NUM = B.WRAG_SEQ_NUM
AND A.WRITING_AGENT_NUM = B.WRITING_AGENT_NUM
FETCH FIRST 1 ROW ONLY) B2 |
output error:
| Quote: |
QUERY MESSAGES:
SQL error at or before FETCH |
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| I have no idea which is Psychic Day this week, so it's not today :-) |
Usually Psychic day is on wednesday,
sometimes if somebody is on holiday on wednesday, it gets shifted to friday 
( the psychic day, naturally, bot the somebody ) |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you ensure that you are running in New Function Mode?
If you are not sure, ask your dba. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
the following is correct sql (=tested) and
is about the same as the above.
| Code: |
select B2.dbname,b2.tsname,b2.createdts,b2.name from
(select dbname
, tsname
, max(CREATEDTS) as createdts
from sysibm.systables
where creator = 'SYSIBM'
group by dbname,tsname) A
, table (
select * from sysibm.systables B
where B.dbname = a.dbname
and b.tsname = a.tsname
and b.createdts = a.createdts
fetch first 1 row only) B2 |
Which tool are you using to run this,
I can't recall a tool returning "SQL error at or before FETCH" without an SQLCODE/STATE |
|
| Back to top |
|
|
santosh ks
New User
Joined: 27 May 2011
Posts: 16
Location: india
|
|
|
|
Is keyword 'TABLE' not required before this code?
| Code: |
(select dbname
, tsname
, max(CREATEDTS) as createdts
from sysibm.systables
where creator = 'SYSIBM'
group by dbname,tsname) A |
How will the variables used in the second select get valus from table A?
I am using QMF. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
keyword table is only required for correlated subqueries in a FROM-clause (ie. a query that can not be run on its own, but uses columns from other tables/subqueries).
A is a non-correlated subquery
B2 is a correlated subquery |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you run the code provided by GuyC? If not, why not?
If you actually look at the provided code, it HAS "table" where you expect . . .
Unless i've gotten lost. . . |
|
| Back to top |
|
|
|
|
