Shabeen Zarook Shabeen Zarook - 5 months ago 21
SQL Question

Data Extraction DB2

I have a file in the bellow format called BAIMTHP in our library.

I want to extract the lines related 50K only (For REF_NO 12345, ROW# 3,4 &5 For REF_NO 56789, ROW# 1,2,3& 5). Issue is that the TAG field will not populate for all the rows instead the ROW_NO will be populating. I tried the bellow SQL but I was able to extract only the 1st row of the TAG field. In this case ROW_NO 1 and 3 only.

Is there a way to do this in DB2? TAG 50K will populate only in maximum of 4 rows.

CREATE TABLE QTEMP. TEST AS (SELECT * FROM EMOQRYLIB.BAIMTHP WHERE TAG = '50K')


Since images are not allowed to upload for my profile, I had to type the sample file details

TEF_NO TAG ROW_# TEXT
12345 20K 1 XXXXXX
12345 25K 2 XXXXXX
12345 50K 3 29/1
12345 4 Ahamath Lane
12345 5 Colombo
56789 50K 1 No 11
56789 2 17th Lane
56789 3 Colpitty
56789 4 Colombo
56789 57A 5 XXXXXX
56789 52B 6 XXXXXX


IDEAL OUTPUT

TEF_NO TAG ROW_# TEXT
12345 50K 3 29/1
12345 4 Ahamath Lane
12345 5 Colombo
56789 50K 1 No 11
56789 2 17th Lane
56789 3 Colpitty
56789 4 Colombo

Answer

Your question is not clear about the logic - if the TAG field is null only after a 50K row - but following SQL would do the job (using table name t50 in my case):

select tef_no, tag, row_#, text
  from t50 t
 where tef_no in (select tef_no  from t50  where tag = '50K' )
   and row_# >= (select row_#  from t50  where tag = '50K' and  tef_no= t.tef_no)
   and row_# < (select row_#  from t50  where tag = '50K' and  tef_no= t.tef_no) + 4