Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

inner selects to pass results back to the originating select statment

Posted on 2009-07-01
16
Medium Priority
?
420 Views
Last Modified: 2012-06-21
I am trying to get a list of tables from a table that stores table names with a where clause that gives me only the tables i need. I have a select for that, but I need to pass the tables to the beginning select statement that needs to take the tablename passed and do a count or where clause to get the data I need.
select count(*) from (select table from tables where ACTIVE = 1) AS t1) where mycolumn = 'UPDATE';
 
I am trying to pass the tables so I can get a count and tablename and update mycolumn to 'UPDATED'
 
I want to select the data first to verify contents before I do an update on the records.

Open in new window

0
Comment
Question by:mphillip85
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24759508
Try it this way:

SELECT  COUNT(*)
FROM    ( SELECT    [table]
          FROM      tables
          WHERE     ACTIVE = 1
        ) AS t1
WHERE   mycolumn = 'UPDATE' ;
 
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24759934
mphillip85:
it  needs your additional clarification:
<I am trying to pass the tables  so I can get a count and tablename and update mycolumn to 'UPDATED'
<I want to select the data first to verify contents before I do an update on the records.>

-----------------------------------------------------------------------------------------------------

check if it can  help ...

select table, count(*)  from tables where ACTIVE = 1  and  mycolumn = 'UPDATE';

update tables
set  mycolumn ='UPDATED'
where  ACTIVE = 1  and  mycolumn = 'UPDATE';
 

...
0
 

Author Comment

by:mphillip85
ID: 24764472
Invalid column name 'mycolumn'.

No matter which example I use it comes back with the error message above.

(select mytable from mytables where mycolumn = 'ACTIVE')
This will give me the list of tables.  

I believe I need a fetch cursor routine.  But I do not want to use one, if I do not have to.
If you can write me a fetch that would accomplish this.  That would help as well.


0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24765331
>>Invalid column name 'mycolumn'.<<
You do realize that is the name of the column as you posted it, right?

Since it is obviously a fake name as is "table" why don't you post the structure of the table and then we can give you a valid query for something that is trivial to accomplish, provided you have the right column/table names, that is.
0
 

Author Comment

by:mphillip85
ID: 24765669
SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        )
WHERE   priceindex = 'myprice' ;

The inner select have the table names to pull from and each of the tables in the inner select have a column called priceindex
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 24766373
Try it like this:

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   priceindex = 'myprice' ;
0
 

Author Comment

by:mphillip85
ID: 24767311
Invalid column name 'priceindex'.

The inner select statement pulls tables that have a column named priceindex

The outer select is not grabbing the table name passed and then checking the priceindex column to see if the where statement matches

the outer select does not pull columns from the table being passed, its still referecing what columns I  pull from the inner select statement
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24769014
if you cannot post the structure of your table(s) then I am afraid I cannot help you any further.

Good luck.
0
 

Author Comment

by:mphillip85
ID: 24775440
so you are telling me that you do not know why the outer select statement is not retrieving values being passed by the inner select statement?  You said this was simple, in your earlier comment, I guess it is not as simple as you stated.  

I will wait for another person or moderator to help.  I do appreciate you trying to help me.  I then ask you to give me the syntax for a DECLARE CURSOR in place of the select statement or translate to a cursor statement.  You were very snobbish when you said this was trivial.  I dont expect that from someone who wants to help.

There is another genius that said it was not possible to do this in another question I checked.  But you are telling me based on your statements that it is possible.  But, now all I want is a cursor statement to do what I am asking or translate the select statement into a cursor statement for me if you would.  I know that works.  So will you help me with that?
0
 

Author Closing Comment

by:mphillip85
ID: 31599010
since I had to do a group by since there was a count(*) involved.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009453
>>since I had to do a group by since there was a count(*) involved.<<
Huh?  There is no GROUP BY required in the SQL Statement I posted:

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   priceindex = 'myprice' ;

If you needed one then perhaps you can enlighten us with your "solution".

Also, if you want help on this site then lose the attitude.
0
 

Author Comment

by:mphillip85
ID: 25009790
I merely responded with what I needed to do.  It was resolved, and I was able to do what I needed to do. So was it really necessary to respond back the way you did.  Get over yourself, life is too short, and there are others out there that are on exchange-experts that really care and are sincere, and not arrogant like yourself.  

So, dont anwer my questions.  I can always find someone else who is better at answering my questions.  If you continue this thread, you are really a piece of work.  Otherwise, drop it, grow up and move on.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009991
You are missing the point.  We are here to help everyone.  You have stated that my answer was not sufficient, please do your part and post your solution so that everyone in this community can learn from it.

Thank you,
Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25009995
P.S. If you feel my comments are in any way inappropriate feel free to post a message in Community Support to have a Moderator review the whole thread.
0
 

Author Comment

by:mphillip85
ID: 25028186
my apologies, i thought i had actually made it work like I wanted exactly, but i modified it too much to be the original request.  below is the code that should work, but does not

SELECT  COUNT(*)
FROM    ( SELECT    dbtable
          FROM      dbobject
          WHERE     dbcolumn = 'priceindex'
        ) d
WHERE   d.priceindex = 'myprice' ;
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 25029317
>>below is the code that should work, but does not<<
Can you tell me what does this mean? Do you get:

A. An error message.  If so what is it.
B. The wrong results.  If so what are they and what are the desired results.
C. Something else.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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