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
 

 

SQL Query to select rows on 2 columns for certain range

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sangram12

New User


Joined: 22 Apr 2010
Posts: 2
Location: pune

PostPosted: Sun Sep 11, 2011 11:31 am    Post subject: SQL Query to select rows on 2 columns for certain range
Reply with quote

Hi, I want to update the Cursor to include condition on two columns.
I want to select the specific range of the below company code and account ID.
Take below Example.

Company ID Account ID
ABC A123
ABC B123
ABC C123
ABC D123
LMN C123
LMN D123
PQR A123
PQR B123
PQR C123
PQR D123

I want to select the rows from Comp ID ABC through PQR and Account ID A123 through B123.
So my result should contain all the above rows except last tow Rows.
Could you please help me to get above result,

Thanks,
Sangram
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: Sun Sep 11, 2011 12:04 pm    Post subject:
Reply with quote

Hello and welcome to the forum,


What have you tried so far? What happened?

Quote:
I want to select the rows from Comp ID ABC through PQR and Account ID A123 through B123.
So my result should contain all the above rows except last tow Rows.
These appear to be conflicting . . . icon_confused.gif

According to your rules these :
LMN C123
LMN D123
Should be excluded, but you also say that only the last 2 rows should be skipped. . .

At least, you need to provide a better explanation of the rules.
Back to top
View user's profile Send private message
sangram12

New User


Joined: 22 Apr 2010
Posts: 2
Location: pune

PostPosted: Sun Sep 11, 2011 12:56 pm    Post subject:
Reply with quote

Hi,

I want to select the rows from ABC-A123 to PQR-B123. and want skiped all the records.

Thanks for your qucik help

Sangram
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Sun Sep 11, 2011 1:23 pm    Post subject: Reply to: SQL Query to select rows on 2 columns for certain
Reply with quote

Quote:
I want to select the rows from ABC-A123 to PQR-B123. and want skiped all the records.

Thanks for your qucik help


ok for the language barrier, but when asked for an explanation
good manners suggest to spend some time trying to express clearly the requirement.

what in h311 does and want skiped all the records. mean

reread Dick' s question and reply accordingly
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Sun Sep 11, 2011 1:44 pm    Post subject: Re: SQL Query to select rows on 2 columns for certain range
Reply with quote

The following is some code which probably isn't syntactically correct in any language, so let's call it pseudo-code. As far as I can tell it meets your requirement (when read by a human, not a computer).

Code:

if Comp ID equal to ABC through PQR
    if Account ID equal to A123 through B123
        do the stuff I want
    endif
endif


Code:

Company ID    Account ID          Select?
ABC                   A123           Y
ABC                   B123           Y
ABC                   C123           N
ABC                   D123           N
LMN                   C123           N
LMN                   D123           N
PQR                   A123           Y
PQR                   B123           Y
PQR                   C123           N
PQR                   D123           N



Applying the code to your example, I have included a results column. There seems to be, simultaneously, more skipping than you expect, and less. You should include some company code data that does not meet the range, ideally less than and greater than your existing data. Less than for account as well.

So, you'll need to clarify things as you have been requested to.

We don't know if your SQL is working, and you misunderstand the data and results, or you have mis-stated the selection, or what.

So, please describe the process clearly, show the input, expected output, and what you have for the SQL. Please put everything relevant (not the description of the process) in the code tags, which preserves the spacing to make things easier for us to follow.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Sun Sep 11, 2011 2:30 pm    Post subject: Reply to: SQL Query to select rows on 2 columns for certain
Reply with quote

follow on, from the way the TS is expressing himself

Quote:
I want to select the rows from Comp ID ABC through PQR and Account ID A123 through B123.
So my result should contain all the above rows except last tow Rows.

and the result set described

he just wants a VISUAL SELECT...
he knows that the last two rows are not needed and asks for a correct SQL query to VISUALLY discard them ...

we are just wasting time icon_cool.gif
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: Sun Sep 11, 2011 11:48 pm    Post subject:
Reply with quote

Hello,

Quote:
I want to select the rows from ABC-A123 to PQR-B123. and want skiped all the records.
I believe this is also misleading. You "know" what you want, but others have to work with whatever explanation you provide.

Possibly what you want is every row that has a Company_Id less than PQR or has PQR as the Company-Id and the Account_Id equal (A123 or B123)?
Back to top
View user's profile Send private message
jerryte

Active User


Joined: 29 Oct 2010
Posts: 183
Location: Toronto, ON, Canada

PostPosted: Wed Sep 14, 2011 2:02 am    Post subject:
Reply with quote

I think sangram12 wants a range of rows using the values in two columns.
There are two ways to do this:
Code:
WHERE
  ((COMP_ID = 'ABC' AND ACCOUNT_ID >= 'A123') OR
    COMP_ID > 'ABC')
AND
  ((COMP_ID = 'PQR' AND ACCOUNT_ID <= 'B123') OR
    COMP_ID < 'PQR')

Another more elegant way is:
Code:
WHERE
COMP_ID CONCAT ACCOUNT_ID BETWEEN 'ABCA123' AND 'PQRB123'

NOTE: I haven't tested it but I think I got it right
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: Wed Sep 14, 2011 2:16 am    Post subject: Reply to: SQL Query to select rows on 2 columns for certain
Reply with quote

Once again, the TS has disappeared. . . icon_confused.gif

Maybe one day we'll learn if the issue has been resolved (and by which code) or if the effort was just abandoned.

d
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
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 Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


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