Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Selection of rows in select caluse

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Selection of rows in select caluse
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

Site Director


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

PostPosted: Mon Feb 04, 2008 9:56 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Tue Feb 05, 2008 5:41 pm    Post subject: Reply to: Selection of rows in select caluse
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us