In subquery, can I limit the fetch to 1 row?

[chrislarkin]

Is there any way to force the first row retrieved in a subquery to be returned?

I am dealing with a large table and when I use MAX or MIN to limit the subquery to one record, the response is very slow. Is there a way to have the query respond immediately on the first match?

Here's my SQL --


select DBACV_ACTIVITY, DBACV_DESCRIPTION,
DBACV_BEGIN_DATE, DBACV_END_DATE,
c.SHOWEND_DATE_NUMERIC,
DBACV_HIGH_PST_DATE,
PB.DBAJV_BRANCH AS P_BRANCH, PB.DBAJV_OBJ_ID
, (SELECT MIN(DBAMD_CUSTOMER) FROM LAWDBF7.DBARAMD AR WHERE AR.DBAMD_ACTIVITY = ACT.DBACV_ACTIVITY) AS CUSTOMER
FROM LAWDBF7.DBACACV ACT INNER JOIN LAWDBF7.DBACAJV2_1C_PB PB ON ACT.DBACV_OBJ_ID = PB.DBAJV_OBJ_ID
INNER JOIN LAWDBF7.DBACAJV2_1c c ON PB.DBAJV_OBJ_ID = c.DBAJV_OBJ_ID where showend_date_numeric between 20050701 and 20051231

 

[MarcLodge]

Chris,
I'm not dear a DB2 system so can't check this out first, so.... is it possible to use the FETCH FIRST 1 ROWS ONLY clause on the subselect?

Marc

 

[PruSQLer]

I was able to fun the following so it appears you can do fetch in a subquery. We're using Version 8, AIX UDB.



select value_basis ,
(select kind_code
from pfmc.nvt_agmt_elem_prod
fetch first 1 rows only)
from pfmc.nvt_agmt_elem_prod
fetch first 1 rows only

 

[chrislarkin]

Thanks for your help -- the error I'm getting is "keyword fetch not expected" here's how I added fetch to the original post's code

select DBACV_ACTIVITY, DBACV_DESCRIPTION,
DBACV_BEGIN_DATE, DBACV_END_DATE,
c.SHOWEND_DATE_NUMERIC,
DBACV_HIGH_PST_DATE,
PB.DBAJV_BRANCH AS P_BRANCH, PB.DBAJV_OBJ_ID
, (SELECT DBAMD_CUSTOMER
FROM LAWDBF7.DBARAMD AR WHERE AR.DBAMD_ACTIVITY = ACT.DBACV_ACTIVITY
fetch first 1 rows only)
FROM LAWDBF7.DBACACV ACT INNER JOIN LAWDBF7.DBACAJV2_1C_PB PB ON ACT.DBACV_OBJ_ID = PB.DBAJV_OBJ_ID
INNER JOIN LAWDBF7.DBACAJV2_1c c ON PB.DBAJV_OBJ_ID = c.DBAJV_OBJ_ID where showend_date_numeric between 20050701 and 20051231


Can anyone see anything clearly wrong? This is on DB2 for as400 V5R3

 

[blom0344]

I'm guessing here, but the ' fetch first .... ' hint is probably interpreted as a select terminator.

What happens if you use

SELECT DBAMD_CUSTOMER
FROM LAWDBF7.DBARAMD AR, [COLOR=red] .......  ACT [/color] WHERE AR.DBAMD_ACTIVITY = ACT.DBACV_ACTIVITY
fetch first 1 rows only

Ties Blom

 

[MarcLodge]

Chris,
In your original query you are selecting a MIN customer number, and in your 2nd post you are picking up the first customer that you hit. This leads me to ask whether you actually need the customer number in the outer query, or whether an existential check would suffice.

Just a thought.

Marc

 

[ddiamond]

Do you have an index on DBAMD_ACTIVITY?

 

[PruSQLer]

Chris,

I converted your SQL to process in my database and it ran fine. Is it possible the AS 400 doesn't allow FETCH in a subquery?

 

[ddiamond]

PluSQLer,

I think you are right. I skimmed our AS400 documentation, and it indicates that the fetch first clause can only go at the end of a full select, or the end of a with clause, but not in a sub-select. I haven't tested this though.

 

[PruSQLer]

Can you use global temp tables on the AS 400? That's what I would use to solve this performance problem.

 

[chrislarkin]

It appears that I can use the global temp tables. The reason I was using MIN as a function before is because I know that there is only one customer per activity, even though there are multiple records of that single match. I could use select distinct but would also have the problem of the system continuing to read past the first match...

 

[kkitt]

Is it not also true (unless the indexes/selection criteria are just right) that it is also needing to read all the rows to select the MIN for each customer.

I would also look at moving the customer number select out of the "SELCET" portion of the statement and moving it into the "FROM" area and use it as part of you join to only select those customer numbers.

It seems that the data that is being returned from the join has no way of matching up with the corresponding data returned from the customer select.

 

[chrislarkin]

kkitt -- could you show your suggestion based on the SQL above?

 

[kkitt]

Let me see which statement is what you are trying to acheive.

1)
find the lowest customer number on the table 'LAWDBF7.DBARAMD'  then pull me all the activity from the other tables for just this one customer

where the 'DBAMD_ACTIVITY' is found on the table 'LAWDBF7.DBACACV' 

and 'DBACV_OBJ_ID' is found on table 'LAWDBF7.DBACAJV2_1C_PB' and  'LAWDBF7.DBACAJV2_1c'

2)
for each customer number's 'DBAMD_ACTIVITY' on the 'LAWDBF7.DBARAMD' pull me all the activity from the other tables

where the 'DBAMD_ACTIVITY' is found on the table 'LAWDBF7.DBACACV' 

and 'DBACV_OBJ_ID' is found on table 'LAWDBF7.DBACAJV2_1C_PB' and  'LAWDBF7.DBACAJV2_1c'

If neither of these, then please let me know what you are tring to pull.

Also is the cutomer number on any of the other table that may be used in the joining???

 

[chrislarkin]

Thanks -- actually what I'm trying to do is get all of the activites and pull the customer over from the AR record, because the activity table doesn't have the field. There are multiple records for each activity and each AR record and I've had trouble with several of the examples I've found in books or online due to some differences in the AS400 version of DB2..

 

[kkitt]

How do you tell what activites are tied to a specific customer?

What is the definition of 'DBACV_ACTIVITY'? is this like a transaction id that would be unique on both tables? if this is just holding if a type of activity has taken place the the following statement comes into question....

I see where you are using the activity code from the 'LAWDBF7.DBACACV (activity)' against the activity code from the 'LAWDBF7.DBARAMD (customer)' but this just ties the activities together, not that this customer has that particular activity during the time period that is being requested.

You stated in your origianal post that the query works but is just slow... are you sure that it is bring back the correct information for each customer -->> activities or is it repeating the same customer number on all of the same activity codes?