View previous topic :: View next topic
|
Author |
Message |
sangram12
New User
Joined: 22 Apr 2010 Posts: 2 Location: pune
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 . . .
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 |
|
|
sangram12
New User
Joined: 22 Apr 2010 Posts: 2 Location: pune
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
jerryte
Active User
Joined: 29 Oct 2010 Posts: 202 Location: Toronto, ON, Canada
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Once again, the TS has disappeared. . .
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 |
|
|
|