[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL PLUS syntax

Posted on 2009-02-17
21
Medium Priority
?
717 Views
Last Modified: 2013-12-19
I've been asked to find any active accounts in the system that have a rate of 1234 that do not have a rate of 5678. The rates would be in the same table. I created the following query but it doesn't seem to be correct. Can someone point me in the right direction.


select ucrserv_prem_code, ucrserv_status_ind, ucrserv_srat_code
from ucrserv
where ucrserv_status_ind = 'A'
and ucrserv_srat_code = '1234'
and ucrserv_srat_code != '5678'

Open in new window

0
Comment
Question by:Shunda
  • 10
  • 5
  • 3
  • +1
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 23664689
I assume you mean GWIC = 1234   and GSIC = 5678?

or do you have some other column that means rate and you simply haven't posted it?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23664712
oh, you've editted the question.  already  :)

ok,

first, this simply doesn't make sense...

and ucrserv_srat_code = '1234'
and ucrserv_srat_code != '5678'

if something = 1234  then you already know it does not equal 5678

so maybe what you have are prem_codes that have multiple rates on them.
Is that it?

So you're looking for any prem_code that has a 1234 somewhere, but never has a 5678?

If so... then try this...


select ucrserv_prem_code, ucrserv_status_ind, ucrserv_srat_code
from ucrserv
where ucrserv_status_ind = 'A'
and ucrserv_srat_code = '1234'
and ucrserv_prem_code not in (select ucrserv_prem_code from ucrserv where 
ucrserv_srat_code != '5678')

Open in new window

0
 

Author Comment

by:Shunda
ID: 23664791
The code is actually as follows.... I changed them to something generic
select ucrserv_prem_code, ucrserv_status_ind, ucrserv_srat_code
from ucrserv
where ucrserv_status_ind = 'A'
and ucrserv_srat_code = 'GWIC'
and ucrserv_srat_code != 'GSIC'

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:Shunda
ID: 23664814
I'm thinking the requestor is saying that if an account has GWIC then it should have GSIC. If it doesn't I want to know.  Does that make sense?  Thanks
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 23664905
then using the original values...


select ucrserv_prem_code, ucrserv_status_ind, ucrserv_srat_code
from ucrserv
where ucrserv_status_ind = 'A'
and ucrserv_srat_code = 'GWIC'
and ucrserv_prem_code not in (select ucrserv_prem_code from ucrserv where 
ucrserv_srat_code != 'GSIC')

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23664909
if these do not work, please post sample data and expected output
0
 
LVL 6

Assisted Solution

by:FVER
FVER earned 500 total points
ID: 23668545
there is an error in sdstuber's query :
select ucrserv_prem_code, ucrserv_status_ind, ucrserv_srat_code
from ucrserv
where ucrserv_status_ind = 'A'
and ucrserv_srat_code = 'GWIC'
and ucrserv_prem_code not in (select ucrserv_prem_code from ucrserv where
ucrserv_srat_code = 'GSIC') -- replaced != with =

personaly, i would have used a query like this one :
select t1.ucrserv_prem_code, t1.ucrserv_status_ind, t1.ucrserv_srat_code
from ucrserv t1
left join ucrserv t2 on t1.ucrserv_prem_code=t2.ucrserv_prem_code and t2.ucrserv_srat_code = 'GSIC'
where t1.ucrserv_status_ind = 'A'
and t1.ucrserv_srat_code = 'GWIC'
and t2.ucrserv_prem_code is NULL
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23669271
yes, my last query did have an error, the !=  should be =  

 thanks!

however I think your query might have an error too.

What if there is a t2.ucrserv_prem_code that is NULL with a GSIC rate?  won't you get a false positive?
0
 
LVL 6

Expert Comment

by:FVER
ID: 23669408
You may be rigth. Since I don't know the structure of table ucrserv, I assumed ucrserv_prem_code and ucrserv_srat_code formed a unique key and were not nullable...

Anyway, if ucrserv_prem_code is nullable then your query can return false negative ;o)

another way to write this if ucrserv_prem_code is nullable is in attached code.
-- with exists clause, most obvious
select t1.ucrserv_prem_code, t1.ucrserv_status_ind, t1.ucrserv_srat_code
from ucrserv t1 
where t1.ucrserv_status_ind = 'A'
and t1.ucrserv_srat_code = 'GWIC'
and not exists (select 1
                  from ucrserv t2
                 where (t1.ucrserv_prem_code=t2.ucrserv_prem_code
                        or (t1.ucrserv_prem_code is NULL and t2.ucrserv_prem_code is NULL )
                       )
                   and t2.ucrserv_srat_code = 'GSIC');
 
--with left join, may require a distinct if t1 matches multiple line in t2
select t1.ucrserv_prem_code, t1.ucrserv_status_ind, t1.ucrserv_srat_code
from ucrserv t1 
left join ucrserv t2 on (t1.ucrserv_prem_code=t2.ucrserv_prem_code or (t1.ucrserv_prem_code is NULL and t2.ucrserv_prem_code is NULL)) and t2.ucrserv_srat_code = 'GSIC'
where t1.ucrserv_status_ind = 'A'
and t1.ucrserv_srat_code = 'GWIC'
and t2.ucrserv_srat_code is NULL;

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23669444
yep
0
 
LVL 32

Expert Comment

by:awking00
ID: 23670209
See attached.
query.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23670224
that will work provided the first set of assumptions is correct

if the second set is what the author is looking for then it won't catch all of them
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23670239
actually, no, it won't work at all
since the rate is in both queries,  using MINUS will never remove anything because no row in the second query will match a row in the first query
0
 
LVL 32

Expert Comment

by:awking00
ID: 23671490
I did a copy and paste or the original query and forgot to modify it. If the premium code identifies the account, then use of the minus should provide the list of accounts that match the criteria.
comments.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23673364
glad I could help,  but, if you used my query with FVER's correction, I suggest a split with KVER
0
 

Author Comment

by:Shunda
ID: 23673550
You are absolutely correct! I should give props to the both of you.
Thanks again.
0
 

Author Comment

by:Shunda
ID: 23673593
FVER thanks for your help on the solution. I really appreciate it!!!
0
 

Author Closing Comment

by:Shunda
ID: 31548027
I think this solution will work, with the correction of the != to = of course.
Thanks for your help!!!!!!!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23676754
thanks coolleomod and thanks FVER
0
 
LVL 6

Expert Comment

by:FVER
ID: 23678976
Thank you shunda, I'm always glad to help :o)
Thanks sdstuber for your fair play :)))
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question