Home » SQL & PL/SQL » SQL & PL/SQL » Question about where-clause in select-statements
Question about where-clause in select-statements [message #392587] Wed, 18 March 2009 09:32 Go to next message
the[V]oid
Messages: 2
Registered: March 2009
Location: Germany
Junior Member

Hi,

I am trying to do the following, where cType is a valid varchar2 that looks like AB-CD-EF:

for rc in (select ID from switch_type where name=cType) loop
-- more code
end loop;


My question is, what is the right way to do this correctly? My select statement doesn't return any rows. My guess is that this is because I need high-commas before and after cType in the where-clause. But I don't know how to add them. Can anybody tell?
Re: Question about where-clause in select-statements [message #392589 is a reply to message #392587] Wed, 18 March 2009 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>My question is, what is the right way to do this correctly?
I give up.
How can we provide "correct" answer when we do not know the inputs, the requirements, or expected output/results?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Question about where-clause in select-statements [message #392590 is a reply to message #392587] Wed, 18 March 2009 09:51 Go to previous messageGo to next message
the[V]oid
Messages: 2
Registered: March 2009
Location: Germany
Junior Member

I'm sorry I gave this less information... I though it was enough.

The exact value of cType is 'WS-C3548-XL', without the high-commas of course.
In table switch_type there is exactly one row with 'WS-C3548-XL' in it's name field.
So I expect to select exactly one row by the given select statement.
The result is, that no row is being selected.

When I try exactly the same select statement on the sql prompt, it only works when I put high-commas arround WS-C3548-XL in the where-clause.
Re: Question about where-clause in select-statements [message #392592 is a reply to message #392587] Wed, 18 March 2009 09:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
what's the datatype of name?
Re: Question about where-clause in select-statements [message #392593 is a reply to message #392587] Wed, 18 March 2009 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I do not understand the term "high-commas".

Please read & follow Posting Guidelines in URL above.
Please read & follow the section titled "Practice".
Please use sqlplus along with CUT & PASTE so we can see exactly what you are doing & how Oracle responds.

I can not debug code I can not see.
Re: Question about where-clause in select-statements [message #392604 is a reply to message #392590] Wed, 18 March 2009 10:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
High commas are more accurately called single quotes.
They are used to mark the begining and end of a text string.

Can you run the following in SQL*Plus and cut and paste the results:
SET SERVEROUTPUT ON SIZE 20000

DECLARE
  ctype  varchar2(100);
BEGIN
  ctype := 'WS-C3548-XL';

  for rec in (select ID from switch_type where name=cType) loop
    dbms_output.put_line('Found ID '||rec.id);
  end loop;
end;
/
Re: Question about where-clause in select-statements [message #392606 is a reply to message #392590] Wed, 18 March 2009 10:53 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@the[V]oid,

Have you assigned the value 'WS-C3548-XL' to your variable cType before using it in the SQL in the cursor?

[Edit]
Seems like JRowbottom got there first. ./fa/1578/0/

Regards,
Jo

[Updated on: Wed, 18 March 2009 10:56]

Report message to a moderator

Previous Topic: how to get length long raw datatype (merged 4)
Next Topic: Restricting Data using LIKE
Goto Forum:
  


Current Time: Thu Dec 08 18:39:26 CST 2016

Total time taken to generate the page: 0.06907 seconds