OraFAQ Forum: SQL & PL/SQL » Using table-prefixed names in queries

Home » SQL & PL/SQL » SQL & PL/SQL » Using table-prefixed names in queries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using table-prefixed names in queries [message #8970]

Wed, 08 October 2003 09:53

Mark Grimshaw
Messages: 73
Registered: June 2002

Member

I am confused as to why I keep getting the SQL*Plus
error of SP2-0734 with a message of "unknown command beginning "number=740..." when I try to issue a cross-table query.

I have two tables, for brevity here are simplified versions of them: -

Table A
DescriptorName varchar2(30),
Descriptorvalue number,
SpecialCase varchar2(1) (can be 'Y' or 'N' only)

Table B
DescriptorName varchar2(30),
RunNumber number
Testname varchar2(30)

I want to get all those records from Table B that have a DescriptorName that matches one in Table A.

I *WANT TO* issue the following query: -

Select TableB.DescriptorName,TableB.RunNumber, TableB.Testname, TableA.SpecialCase From TableB, TableA Where TableB.DescriptorName = TableA.DescriptorName And (TableB.DescriptorName='Desc1' And TableB.RunNumber=74001)

This doesn't work and I get the error message given above. However, if in the very last bit of the query I remove the TableB prefix from the RunNumber field everything works as expected (??)

Does anyone know why this is. It is important to me because I have written a Visual Basic program that automatically generates SQL queries for me and the code that runs will mean some complicated changes.

I would be grateful for any help on this.
Additionally, does anyone know anyone know how I could alter the above query to just get JUST those records from TableA whose associated Descriptor names in Table A also have their SpecialCase field set to 'Y'? I have tried tagging a clause such as "and TableA.SpecialCase='Y'" to various parts of the query but keep getting errors.

Extreme thanks if you can provide help on this.

TYIA

Mark Grimshaw

Report message to a moderator

Re: Using table-prefixed names in queries [message #8972 is a reply to message #8970]

Wed, 08 October 2003 12:07

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Mark, not sure why you're getting an error:

sql>select * from tablea;
 
DESCRIPTORNAME                 DESCRIPTORVALUE S
------------------------------ --------------- -
Desc1                                       10 Y
Desc2                                       20 N
Desc3                                       30 Y
Desc4                                       40 N
 
4 rows selected.
 
sql>select * from tableb;
 
DESCRIPTORNAME                 RUNNUMBER TESTNAME
------------------------------ --------- ------------------------------
Desc1                              74001 Y
Desc2                              74002 N
Desc3                              74003 Y
 
3 rows selected.
 
sql>select tableb.descriptorname,
  2         tableb.runnumber,
  3         tableb.testname,
  4         tablea.specialcase
  5    from tableb,
  6         tablea
  7   where tableb.descriptorname = tablea.descriptorname
  8     and tableb.descriptorname = 'Desc1'
  9     and tableb.runnumber = 74001
 10     and tablea.specialcase = 'Y';
 
DESCRIPTORNAME                 RUNNUMBER TESTNAME                       S
------------------------------ --------- ------------------------------ -
Desc1                              74001 Y                              Y
 
1 row selected.

I don't receive an error with the exact SQL you posted either.

Report message to a moderator

Re: Using table-prefixed names in queries [message #8985 is a reply to message #8972]

Thu, 09 October 2003 01:37

Mark Grimshaw
Messages: 73
Registered: June 2002

Member

Todd,

I noticed from your response that you were separating the parts of the sql query into separate lines. When I
tried this my query worked! However, I double checked the exact same query without separation and I got the problem I was having yesterday. Is there an SQL*Plus command line setting or something for the size of a buffer that could be the culprit? In any case you solved both of my problems and I can now continue. Many thanks.

Report message to a moderator

Previous Topic:	Char Value
Next Topic:	Persistent remote connect in PL/SQL?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 12:19:31 CDT 2024