VERY SIMPLE QUESTION ABOUT A VIEW

VERY SIMPLE QUESTION ABOUT A VIEW

Post by JESS » Fri, 01 Aug 2003 23:14:10



In the following query, (Value1 - 5) as Value2 is not
admissible. SQL does not acknowledge Value1 as a column
(it displays that error).

The SELECT statement corresponding to Value1 is a very
complicated one and I do not want SQL to calculate Value2
as (SELECT COUNT(...) FROM Table2...) - 5. I would like
SQL to substract 5 from the previously calculated value

What do I have to do with Value1 so that it is acknowleged
by SQL server?. Should I implement another view based on
this one?

SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
(Value1 - 5) as Value2 FROM dbo.Table1.....

Any help is welcome

 
 
 

VERY SIMPLE QUESTION ABOUT A VIEW

Post by Bob Barrow » Fri, 01 Aug 2003 23:33:52



> In the following query, (Value1 - 5) as Value2 is not
> admissible. SQL does not acknowledge Value1 as a column
> (it displays that error).

> The SELECT statement corresponding to Value1 is a very
> complicated one and I do not want SQL to calculate Value2
> as (SELECT COUNT(...) FROM Table2...) - 5. I would like
> SQL to substract 5 from the previously calculated value

Unfortunately for you, that is not possible. You have to repeat the
calculation for Value1.
Quote:

> What do I have to do with Value1 so that it is acknowleged
> by SQL server?. Should I implement another view based on
> this one?

> SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
> (Value1 - 5) as Value2 FROM dbo.Table1.....

Yes, that will work.
You can also create a UDF that does the Value1 calculation ...

Bob Barrows

 
 
 

VERY SIMPLE QUESTION ABOUT A VIEW

Post by oj » Fri, 01 Aug 2003 23:33:49


Alias is allowed only the "order by" clause. You either specify your
correlated or you can try derived table...
e.g.
select value1,value1-5 as value2
from(
SELECT (SELECT COUNT(...) FROM Table2....) AS Value1
FROM dbo.Table1...) Derived

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net


Quote:> In the following query, (Value1 - 5) as Value2 is not
> admissible. SQL does not acknowledge Value1 as a column
> (it displays that error).

> The SELECT statement corresponding to Value1 is a very
> complicated one and I do not want SQL to calculate Value2
> as (SELECT COUNT(...) FROM Table2...) - 5. I would like
> SQL to substract 5 from the previously calculated value

> What do I have to do with Value1 so that it is acknowleged
> by SQL server?. Should I implement another view based on
> this one?

> SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
> (Value1 - 5) as Value2 FROM dbo.Table1.....

> Any help is welcome

 
 
 

VERY SIMPLE QUESTION ABOUT A VIEW

Post by Vern Rab » Fri, 01 Aug 2003 23:37:43


try:

select Value1, Value1 - 5 AS Value2
from (
SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
(Value1 - 5) as Value2 FROM dbo.Table1.....
) AS X

HTH
Vern

Quote:>-----Original Message-----
>In the following query, (Value1 - 5) as Value2 is not
>admissible. SQL does not acknowledge Value1 as a column
>(it displays that error).

>The SELECT statement corresponding to Value1 is a very
>complicated one and I do not want SQL to calculate Value2
>as (SELECT COUNT(...) FROM Table2...) - 5. I would like
>SQL to substract 5 from the previously calculated value

>What do I have to do with Value1 so that it is
acknowleged
>by SQL server?. Should I implement another view based on
>this one?

>SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
>(Value1 - 5) as Value2 FROM dbo.Table1.....

>Any help is welcome

>.

 
 
 

VERY SIMPLE QUESTION ABOUT A VIEW

Post by Vern Rab » Fri, 01 Aug 2003 23:46:02


I'm not sure I'm helping much today. I'm making so many
typo's. This should be:

select Value1, Value1 - 5 AS Value2
from (
select (select count(...) from Table2...) AS Value1
from dbo.Table1...
) AS X

Hopefully I have it right this time.
Vern

Quote:>-----Original Message-----
>try:

>select Value1, Value1 - 5 AS Value2
>from (
>SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
>(Value1 - 5) as Value2 FROM dbo.Table1.....
>) AS X

>HTH
>Vern

>>-----Original Message-----
>>In the following query, (Value1 - 5) as Value2 is not
>>admissible. SQL does not acknowledge Value1 as a column
>>(it displays that error).

>>The SELECT statement corresponding to Value1 is a very
>>complicated one and I do not want SQL to calculate
Value2
>>as (SELECT COUNT(...) FROM Table2...) - 5. I would like
>>SQL to substract 5 from the previously calculated value

>>What do I have to do with Value1 so that it is
>acknowleged
>>by SQL server?. Should I implement another view based on
>>this one?

>>SELECT (SELECT COUNT(...) FROM Table2....) AS Value1,
>>(Value1 - 5) as Value2 FROM dbo.Table1.....

>>Any help is welcome

>>.

>.

 
 
 

VERY SIMPLE QUESTION ABOUT A VIEW

Post by Joe Celk » Sat, 02 Aug 2003 03:03:57


PROBLEM: You think that SQL is a procedural language which reads files
and stuff from left to right, one "field" at a time.  It ain't; it is
set-oriented and the WHOLE ROW comes into existence ALL AT ONCE in the
SELECT clause.  

Here is how a SELECT works in SQL ... at least in theory.  Real products
will optimize things when they can.

 a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there.  The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.  

 b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE).  The WHERE
clause is applied to the working in the FROM clause.  

 c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the new
grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
of the those three items.

 d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

 e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions in
the SELECT are done after all the other clauses are done.  The AS
operator can give a name to expressions in the SELECT list, too.  These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause has been executed; you cannot use them
in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed.  For purposes of defining a duplicate row, NULLs are treated as
matching (just like in the GROUP BY).  

 f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. SQL7, SIMPLE SIMPLE SIMPLE question

I'm looking for someone to help show me how i can accomplish 2 things.

1) write a simple simple simple stored procedure with SQL 7 that
        a) deletes everything from about 10 tables, or 1 table

2) call this procedure from VB with NO parameters.

I can't find an easyway with the online help. but i'm sure it's a simple
process, can someone help please?

2. SQL Statement , Please help

3. Security with views (simple question)

4. error message says Could not find the ...

5. Simple View Question !

6. Copy of ID field between servers

7. Simple view question

8. Question for Sun experts

9. Simple question about SP and Views

10. Join in a View - simple question

11. View Related Simple Question !

12. simple VIEW questions