Solved

inner selects to pass results back to the originating select statment

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

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 76
Need help with a query 3 39
help converting varchar to date 14 25
add stored proc on publlication 4 20
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

749 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