IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Selection of rows in select caluse


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
creator.abhishek

New User


Joined: 07 May 2006
Posts: 32
Location: Pune

PostPosted: Mon Feb 04, 2008 9:41 pm
Reply with quote

I have a problem,

suppose in a table there are 150 rows but in spufi its returning 25 rows only because in where clause 124 rows are having same values.

as exp :-

SELECT X,Y,Z FROM TABLE1
WHERE A1 = 'A' AND B1 = 'B'
AND FILE_TS >= '2007-11-15-14.00.00.100000'
AND FILE_TS <= '2007-11-20-14.00.00.625000'
AND FILE_TS >= '2007-11-11-14.00.00.100000'



Here A1 and B1 columns are constant and assume FILE_TS is Timestamp, which is written in the file itself.

Suppose Customer has submitted 150 files from 15 nov to 20 nov, which are coming in this criteria and 124 files are having the same FILE_TS, then SPUFI will return only 25 rows instead of 150.

------------
I have tried to solve it by giving DB2 insertion date in place of File_TS date in where clause, cause insertion timestamp always be unique as concern of DB2,
Logic has worked in initial testing, But then i thought , Timestamp in file could be of any date but Insertion timestamp always will be of the current date, so here can be mismatch b/w the date written in the files on the front end screen.

Now what ???????????

Then i thought, why not add another unique column in where clause , which available in table (ref_no), but not able to think the exact query....

SELECT X,Y,Z FROM TABLE1
WHERE A1 = 'A' AND B1 = 'B'
AND FILE_TS >= '2007-11-15-14.00.00.100000'
AND FILE_TS <= '2007-11-20-14.00.00.625000'
AND FILE_TS >= '2007-11-11-14.00.00.100000'
AND ref_no = (Select ????????????



* ref_no is alfanumeric variable but it is unique for each file.

can any one of you please help me out this..............
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon Feb 04, 2008 9:56 pm
Reply with quote

Hello,

Quote:
Suppose Customer has submitted 150 files from 15 nov to 20 nov, which are coming in this criteria and 124 files are having the same FILE_TS, then SPUFI will return only 25 rows instead of 150.


Quote:
SELECT X,Y,Z FROM TABLE1
WHERE A1 = 'A' AND B1 = 'B'
AND FILE_TS >= '2007-11-15-14.00.00.100000'
AND FILE_TS <= '2007-11-20-14.00.00.625000'
AND FILE_TS >= '2007-11-11-14.00.00.100000'
Your code does not support your observation. I see nothing in the SELECT that would prevent duplicates from being returned. . . . How did this concern originate?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Mon Feb 04, 2008 10:09 pm
Reply with quote

The third "FILE_TS >=" is meaningless since '2007-11-11-14.00.00.100000' is less than '2007-11-15-14.00.00.1000000'.
Back to top
View user's profile Send private message
vebs

New User


Joined: 27 Oct 2007
Posts: 19
Location: UK

PostPosted: Mon Feb 04, 2008 10:52 pm
Reply with quote

According to the query given, it will provide all 150 rows in output. So I do not agree with explanation that output wont return duplicates.

Also, your efforts posted suggest you wanted to include one unique value for timestamp, which you believe will only be db2 insertion date.



Quote:
Timestamp in file could be of any date but Insertion timestamp always will be of the current date, so here can be mismatch b/w the date written in the files on the front end screen



But you can insert FILE_TS as well in db2 table, in which case both dates will be same. Still this does not explain why do you think only 25 rows are being returned for the query, and why do you require this manipulation. Please elaborate.
Back to top
View user's profile Send private message
creator.abhishek

New User


Joined: 07 May 2006
Posts: 32
Location: Pune

PostPosted: Tue Feb 05, 2008 5:28 pm
Reply with quote

Thanks u all for your comments,

In the background of problem, this query is written under the cursor of ONLINE program.
Online program is giving 10 rows in the first screen from the table, where 10th row is referring to first row of next page in the same map.
What had happened, 10 row of the first page of the screen and the first row of the next page, contains exactly the same value.

As i have written the gen form of query

SELECT X,Y,Z FROM TABLE1
WHERE A1 = 'A' AND B1 = 'B'
AND FILE_TS >= '2007-11-15-14.00.00.100000'
AND FILE_TS <= '2007-11-20-14.00.00.625000'
AND FILE_TS >= '2007-11-11-14.00.00.100000'

here third row is mainly responsible for selecting the next row on the cursor, as rest four where clause are constant and the two files has equal FILE_TS as well bot has came in that scenario of the screen where first file came in the 10th row of the first page and second file came into the first line of next page. results caused in infinite loop of scrolling the screen without giving next page info.
Screen is of CICS so commarea took the values of 10th row of first page and refers to first row of next page which is again the same, and CICS was unable to recognize the difference so not processed the next information with end lost scrolling...

I know that scenario can happens once out of lacks but had happened.

as insertion time is the current timestamp so there could be the solution like, we can use the insertion timestamp in order by clause of the same query but, actually query has the order by clause on FILE_ts , so performance related issue can arise..

Please let me know your suggestions here
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Feb 05, 2008 5:41 pm
Reply with quote

as a general point, CICS(IMS terminal paging by reading from the database one screen at the time is usually considered a bad pratice

there are a few concerns on it

data consistency, when user is paging forth and back the assumption
is to always get the same data ( for the same paging session )
( what if he/she was jotting down data and has to rewrite everything because of consistency ?? quite unpleasant )

a better approach would be run the query to extract all the data writing the formatted lines to temporary storage
and, code reuse good practice, invoke a generalized paging program to do the display

in this case no worries about cursor, nor about deleted rows, nor inserted rows
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts SELECT from data change table DB2 5
Search our Forums:

Back to Top