Select in the select clause

How to use another select clause within a select clause itself.
Like;
select col1,col2,(select colx from table b) from table a .
The above statement is throwing syntax error

Thanks,
 Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92
http://www.cgvakindia.com


0
anand_group
2/24/2004 1:02:44 PM
sybase.powerbuilder.general 62418 articles. 18 followers. Follow

5 Replies
852 Views

Similar Articles

[PageSpeed] 35

"anand_group" <anand@cgvakindia.com> wrote:

> How to use another select clause within a select clause itself.
> Like;
> select col1,col2,(select colx from table b) from table a .
> The above statement is throwing syntax error

   Ensure that subquery returns only one row. And _always_ mention your DBMS
when asking similar questions.

Dmitri.


0
Dmitri
2/24/2004 1:59:11 PM
Your select is not completely correct for most databases, at least some of
the ones I have worked on have problems with this sort of statement:

A more correct syntax would be:

        SELECT col1, col2, ( SELECT col1 FROM t1 ) as col3 FROM ....

Some engines allow you to dump the "as" keyword, but I don't think any of
them have problems with it.

There are however some limitations:
1) A subquery in a select may only return one row, otherwise you get an
error executing the SQL
2) There may only be one column, which is completely logical.

Talking from experience that is, not from theoretical knowledge....

Hope this helps....

"anand_group" <anand@cgvakindia.com> wrote in message
news:403b4b74@forums-1-dub...
> How to use another select clause within a select clause itself.
> Like;
> select col1,col2,(select colx from table b) from table a .
> The above statement is throwing syntax error
>
> Thanks,
>  Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92
> http://www.cgvakindia.com
>
>


0
Roel
2/24/2004 2:03:23 PM
I had the same problem.  But that works only for updates, p.e. 
update tab1 set col1 = (select max(col2) from tab2 where ...) 
from tab1 ...

For my problem, I wrote a process that create a temp-table with tha basics
columns, and then apply the update statmen like the example.

So, for me was imposible create a select clause like you wrote.

Good luck.


On 24 Feb 2004 05:02:44 -0800,
 in sybase.public.powerbuilder.general
anand_group <anand@cgvakindia.com> wrote: 
>How to use another select clause within a select clause itself.
>Like;
>select col1,col2,(select colx from table b) from table a .
>The above statement is throwing syntax error
>
>Thanks,
> Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92
>http://www.cgvakindia.com
>
>
0
W
2/24/2004 2:33:09 PM
That is very possible, not all database engines ( especially older ones  )
support subselects in the select clause, that's why Dmitri is right in
suggesting to always include the engine.

You can always change to either outer join or using a subselect in the from
clause ( all engines/databases I worked with except Access ( very old
version, don't know about newer versions ) support this ) if possible ( and
if performance permits it ).


"W.S. [BCCR]" <wsolis_sysde@hotmail.com> wrote in message
news:403b60b2$1@forums-2-dub...
> I had the same problem.  But that works only for updates, p.e.
> update tab1 set col1 = (select max(col2) from tab2 where ...)
> from tab1 ...
>
> For my problem, I wrote a process that create a temp-table with tha basics
> columns, and then apply the update statmen like the example.
>
> So, for me was imposible create a select clause like you wrote.
>
> Good luck.
>
>
> On 24 Feb 2004 05:02:44 -0800,
>  in sybase.public.powerbuilder.general
> anand_group <anand@cgvakindia.com> wrote:
> >How to use another select clause within a select clause itself.
> >Like;
> >select col1,col2,(select colx from table b) from table a .
> >The above statement is throwing syntax error
> >
> >Thanks,
> > Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92
> >http://www.cgvakindia.com
> >
> >


0
Roel
2/24/2004 2:46:25 PM
You can also try:

    select a.col1, a.col2, c.colx from table a, (select colx from table C) b...

This works for Oracle 8.0.4

This might help, I hope

Jim

On 24 Feb 2004 06:46:25 -0800,
 in sybase.public.powerbuilder.general
Roel Sergeant <Roel.Sergeant@pandora.be> wrote: 
>That is very possible, not all database engines ( especially older ones  )
>support subselects in the select clause, that's why Dmitri is right in
>suggesting to always include the engine.
>
>You can always change to either outer join or using a subselect in the from
>clause ( all engines/databases I worked with except Access ( very old
>version, don't know about newer versions ) support this ) if possible ( and
>if performance permits it ).
>
>
>"W.S. [BCCR]" <wsolis_sysde@hotmail.com> wrote in message
>news:403b60b2$1@forums-2-dub...
>> I had the same problem.  But that works only for updates, p.e.
>> update tab1 set col1 = (select max(col2) from tab2 where ...)
>> from tab1 ...
>>
>> For my problem, I wrote a process that create a temp-table with tha basics
>> columns, and then apply the update statmen like the example.
>>
>> So, for me was imposible create a select clause like you wrote.
>>
>> Good luck.
>>
>>
>> On 24 Feb 2004 05:02:44 -0800,
>>  in sybase.public.powerbuilder.general
>> anand_group <anand@cgvakindia.com> wrote:
>> >How to use another select clause within a select clause itself.
>> >Like;
>> >select col1,col2,(select colx from table b) from table a .
>> >The above statement is throwing syntax error
>> >
>> >Thanks,
>> > Anandkumar B CGVAK Software & Exports Ltd. ( 0422 2434491/92
>> >http://www.cgvakindia.com
>> >
>> >
>
>

Jim Smith
Duqusene Light Company
jrsmith@duqlight.com
0
Jim
2/24/2004 3:08:59 PM
Reply:

Similar Artilces:

SELECT from SELECT
Hi! I remember seeing an example where you can do a select from a nested select, but I cannot seem to make it work. So it would be something like this: select max( name ) from ( select name from customer union select name from vendor ) Was I dreaming I saw that? Thanks! D In article <4500243f@forums-2-dub>, daniel_AT_infrontsoftware_DOT_com@N0SPAM.com says... > Hi! > > I remember seeing an example where you can do a select from a nested > select, but I cannot seem to make it work. No, you weren't. Try (UNTESTED!) adding a...

Why is the select saying selected after an item is selected?
This was a whitelisting issue due to a software upgrade at hostgator..  Nothing wrong with my code..Scot King SEO Software Tool | Search Engine Optimization"Get your page rank high"  huh? It is smart to put your question in the body and not just in the title. Have no idea what your question is or if it even a question.Eric Pascarello.com | Twitter epascarello | LinkedIn use thisddl.options[ddl.selectedIndex].selected = false; //Clear the selection ddl.options[1].selected = trueMAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Sit...

insert select and select into
Hello, I am using temp tables to generate a report. I created most of the temp tables and then use insert select. I found for one, when I use insert select, I get a different query plan than if I use select into. The select into get a much better performance. The problem is I get dead locks on the statement. Is there anything I can do to change this behavior? Thanks, Josh Manfred ...

SELECT . FROM ( SELECT .. FROM )
Hi, In application we generate very effective SQL statements that are understandable by MS SQL Server and Oracle but give syntax errors on Sybase. I am talking about SELECT . FROM ( SELECT .. FROM a_table). For example: select 1 from (select abc_id from abc) or select 1 from (select abc_id from abc union all select abc_id from bcd) Is there any way to overcome this limitation? Any help will be greatly appreciated. Leonid Borisov Tefensoft On Wed, 15 May 2002 02:09:52 -0700, Leonid Borisov wrote: > Hi, > > > >...

Bug in SELECT from SELECT ?
I have a table intwb_items CREATE TABLE intwb_items ( wb_id integer not null, prod_id integer not null, qty numeric(12,5) not null, primary key(wb_id, prod_id) ) How can I obtain sum of 10 greatest "qty"? I tried to execute query: SELECT sum(qty) FROM ( SELECT top 10 qty FROM intwb_items ORDER BY qty DESC )as tmp But it raise error: "Syntax error or access violation: near 'ORDER' in ... FROM intwb_items [ORDER]" The same query but without "ORDER BY" works perfectly, but "TOP N" without "ORDE...

Select option selection
I'm created a gridview of websites, in one column, and use the gridview's 'selection' featue in the next column. When I click the 'selection' link I tried to get the website by using: Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(1).Text but it said it was not part of the webcontrol for selection. How do I get cell so that I can read it? thanks   Looks like your grabbing the select option and not the first cell  try  Dim key As String = grvPrivacy.Rows(e.RowIndex).Cells(0).TextwerD, MCSD VB.Net Hi, gmcghee: There are several ways...

select in the select statement
Hi, I am trying to add another select statement in my original/outer/overall select statement. Something like: Select 1, 2, 3 = (Select # from Table1 Where....) From Table 2 Where.... Does anyone know the standard way to write this or is there any other alternative that can perform the same behavior. Much thanks!! Kenny That's database dependent. In oracle you would use: select A.* from ( select * from mytable) A -- Terry Dykstra (TeamSybase) Please state PB / OS / DB versions in your post. MySybase http://my.sybase.com/mysybase "Kenny Tran" <...

Second SELECT-box doesn't show the selected value as SELECTED
Hi! I usually find what I look for in the other threads, but now I've spent two weeks searching and I still can't work this out, even though I'm sure the solution is easy:( Why doesn't my second dropdown-list show the selected value Arb (which shows in the url-window as for example myPage.asp?Ar=1976&Arb=1987) as selected? It manages to use the Ar-variable for the first SELECT-box, but the second box always has the first querystring-value selected, as if Arb="". I've also tried using a second sql-query, doing exactly as with the first SELECT-box, without success. Is ...

dynamically populating a select box depending upon selections in another select box
Dear sir, I am trying to populate a select box depending on multiple selections in another select box. I find the dependent select box does not populate at all. while it does populate if the parent select box is a single election box and not multiple. i want to use javascript so that for population i do not have to go to the server. I am using onchange event to execute the function.  Some one please help. pcg Could you post your code?jameswrightwww.jwright.info"Never be so busy making a living that you forget to make a life." Relevent code is given below please. Pa...

DROPDOWNLIST DOES NOT SELECT THE SELECTED VALUE
Hi all, i am using C# , asp.net vs2005. ajax 1.0 i am populating data programitically . when i select first value, dropdown control select the same. when i select second value ,dropdownlist select second value too But i does not continue  from third value onward . when i select third value, dropdownlist control select second value then after behavior reman the same no matter what i select dropdownlist control select second value ONLY.<asp:DropDownList id="Ddlzse" runat="server" Width="165px" Font-Bold="False" Fo...

Multi-select ListBox and Select All
Hi,I'm having an issue with some JS code for IE6 (no need for x-browser) and multi-select listbox.I'm trying to implement a Select All button.Current code is : function SelectAll(obj){ for (var i = 0; i < obj.options.length; i++) { obj.options[i].selected = true; } } which logically seems to work.  The problem I have is that this does not seem to work until I click the multi-select again.  It seems that although the options are marked as selected, the screen does not update to reflect this until something triggers it (in this case trying to click in ...

Select into vs. insert ... select ...
Hello, I am using temp tables to generate a report. I created most of the temp tables and then use insert select. I found for one that when I use insert select I get a different query plan that if I use select into. The select into get a much better performance. The problem is I get dead lock on the statement. Is there anything I can do to change this behavior? Thanks, Josh Manfred SELECT INTO #temp is a non-logged operation. This can provide a performance increase. However, one downside is that you may end up with a lot of contention on system tables. (I didn...

SELECT * vs. SELECT 1
I have always understood that when using the WHERE EXISTS clause, one codes it as WHERE EXISTS (SELECT * FROM .....). Lately, I've been told that performance is better if the it's coded as ...(SELECT 1 FROM ...) (or SELECT '' .. - that is, to select some literal). Everything I've read says that SELECT * is used with EXISTS because there's no result set returned - it only evaluates into true or false. I'm now curious - is there an advantage to writing SELECT 1 instead of SELECT * with EXISTS? If so, how much of an advantage, & why do all of the SQL b...

Accessing the selected value from a Select
Need to access the selected value from a Select in client script.  I can get the selectedIndex but where do I find the selected value? Will this give it to me? form1.Select1.Options(form1.Select1.SelectedIndex)   form1.Select1.options[form1.Select1.selectedIndex] Try form1.Select1.options[form1.Select1.selectedIndex].value> penny for a thought...

Web resources about - Select in the select clause - sybase.powerbuilder.general

United States House Select Committee on Energy Independence and Global Warming - Wikipedia, the free ...
but will work with the House standing committees with jurisdiction over climate change issues and develop recommendations on legislative proposals. ...

kotaku selects
Kotaku Selects

Facebook Testing ‘Select Ad Variations To Generate’ Option In Power Editor?
Facebook appears to be testing a new feature in Power Editor that allows users to more easily test different versions of their ads. continued… ...

Apple Selects Best Apps, Games of 2015
Apple revealed its “Best of 2015,” highlighting the best applications and games the iTunes App Store had to offer this year. Apple selected ...

Google Play offering select buy one, gift one movies for the holidays
... a few days ago, Google is giving you the chance to purchase a movie and gift that same movie to a friend for no extra charge. The selection ...

BMW selects two artists to create M6 GT3 art cars
Filed under: Design/Style , Motorsports , BMW , Coupe , Performance , Racing Artists Cao Fei and John Baldessari will create the 18th and 19th ...

US confidential note to select countries sets the terms for talks
... between developed and developing countries scrapped Days ahead of the launch of the Paris talks, the US has confidentially informed select countries ...

Cisco Selects Netformx Discovery to Power New Net Authenticate Assessment Service
Cisco Selects Netformx Discovery to Power New Net Authenticate Assessment Service Business Wire (press release) SAN JOSE, Calif.(BUSINESS WIRE)Netformx®, ...

DEAL: Buy Select Google Play Movies, Gift One to Someone for Free
We’re getting closer and closer to the holidays and gift-giving fever is just around the corner. In the spirit of the season, Google’s latest ...

CyanogenMod gives select smartphones an Android 6 update option
Lots of folks still don't have Android 6 "Marshmallow" on their shiny new phones, but owners of some older models can now it from CyanogenMod. ...

Resources last updated: 12/22/2015 12:39:38 PM