Solved

inner selects to pass results back to the originating select statment

Posted on 2009-07-01
16
409 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
  • 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 42

Expert Comment

by:EugeneZ
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
 
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 500 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now