Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName

Compression

Hi everyone,
I am developing a web app and wanting to store documents (doc, xls,
pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
don't know exactly how big the docs are going to be) to compress the
files before putting them into the DB? How does this affect Full Text
Searching? On a wider note what's the attitude of people in terms of
storing documents in the DB versus on the file system. There seems to
be a lot of differing opinions. Any links to resources would be most
appreciated.
Thanks for your help in advance.
Jose
Jose,
First of all, can I assume that you are using SQL Server 2000? If so, could
you post the full output of: SELECT @.@.version -- as this is very help info
in understanding SQL FTS issues.
Secondly, what exactly do you mean by "compress[ing] the files before
putting them into the DB?" Do you mean to store them as ZIP files in columns
defined with the IMAGE datatype? or are you thinking about placing the FT
Catalog folder (SQL00060005) & files on a compressed drive. As for the
latter, I've not done any testing with compressed drive, but Hilary Cotter
has and from past postings on this subject, he's indicated that the
performance gains is mimumal. As for the former, you will need to purchase a
3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
using SQL Server 2000, you can store the zip files in an IMAGE datatype and
define a "file extension" column that will have the value of "zip" and the
ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
Daemon) processes to FT Index the contents of the zip file that contains MS
Word documents. You can purchase the ZIP IFilter from either of the
following two vendors:
http://www.4-share.com/content/products.htm
http://www.ifiltershop.com/zipfilter.html
I've personally have been using the 4-Share ZIP IFilter for over a year and
find it works very well for me.
As for "storing documents in the DB versus on the file system", this is very
much a open and very actively discussed subject, below is one past posting
I've made on this subject with links included...
"There are many issues related to storing the files on disk (faster access,
easy copy/move, etc.) vs. storing the files in the database (transaction
control, change control, audit of changes, etc) than just efficiency and
scalability, although, those are important points... There is a ASPFAQ
website that has a number of references
(http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
checked them all), but one major reason for storing the files in a SQL
Server table's column defined with an IMAGE datatype, is that in SQL Server
2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
Index the contents of supported file types, primarily MS Office, HTML and
3rd party IFilter's like Adobe's PDF files.
As this is one of those FAQ type questions that have been known to start
religious wars or at the very least a flame war, and while Sharon didn't ask
about other application issues that this question often generates as it is
often an open-ended question, i.e., one that never seems to be answered to
everyone's satisfactions as it usually "depends" upon the application or
upon how one defines the word "best"...
As for the Terra Server, checkout the "about page"
(http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
explains how MS and others did it and yes, they used the IMAGE datatype,
specifically
http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
contains the "blob" [image] field where the imagery data is stored.
Jose, depending upon how many files you have, how frequently they change,
how large they are, what the app is, etc. you may want to review the Terra
Server web site and consider the following rule of thumb that they used:
< 1 million images or big images (> 1MB) put them in the file system.
> 1 million and < 1 MB images, put them in SQL Server.
Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
For everything in between, either way will work, depending upon if you need
transactional control over your files. Additionally, if you store the files
in a TEXT or IMAGE column, you can also store related metadata about that
file in SQL Server as well for increased searching capabilities. Also, and
obviously with SQL Server you get built-in support for validating the
consistency of the database, indices, backup, restore, etc. As for loading
&/or extracting the files from SQL Server there are now many methods of
doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
to transform your files in some manner.
Hope this helps, as the primary consideration should always be what is best
for your application...
Regards,
John
"Jose Perez" <jlpv@.totalise.co.uk> wrote in message
news:3724a9d9.0404141212.480e1630@.posting.google.c om...
> Hi everyone,
> I am developing a web app and wanting to store documents (doc, xls,
> pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
> don't know exactly how big the docs are going to be) to compress the
> files before putting them into the DB? How does this affect Full Text
> Searching? On a wider note what's the attitude of people in terms of
> storing documents in the DB versus on the file system. There seems to
> be a lot of differing opinions. Any links to resources would be most
> appreciated.
> Thanks for your help in advance.
> Jose
|||John,
Thanks a lot for the detailed reply which you gave - it is most
appreciated. In answer to your question, yes I am running SQL Server
2000 (Version Information - Microsoft SQL Server 2000 - 8.00.760
(Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)).
To clarify, I meant storing documents as "ZIP files in columns defined
with the IMAGE datatype". Thanks for the URLs regarding the ZIP
IFilter - I will investigate them. Your post was very helpful -
thanks.
Jose
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#qtcv8pIEHA.964@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Jose,
> First of all, can I assume that you are using SQL Server 2000? If so, could
> you post the full output of: SELECT @.@.version -- as this is very help info
> in understanding SQL FTS issues.
> Secondly, what exactly do you mean by "compress[ing] the files before
> putting them into the DB?" Do you mean to store them as ZIP files in columns
> defined with the IMAGE datatype? or are you thinking about placing the FT
> Catalog folder (SQL00060005) & files on a compressed drive. As for the
> latter, I've not done any testing with compressed drive, but Hilary Cotter
> has and from past postings on this subject, he's indicated that the
> performance gains is mimumal. As for the former, you will need to purchase a
> 3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
> using SQL Server 2000, you can store the zip files in an IMAGE datatype and
> define a "file extension" column that will have the value of "zip" and the
> ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
> Daemon) processes to FT Index the contents of the zip file that contains MS
> Word documents. You can purchase the ZIP IFilter from either of the
> following two vendors:
> http://www.4-share.com/content/products.htm
> http://www.ifiltershop.com/zipfilter.html
> I've personally have been using the 4-Share ZIP IFilter for over a year and
> find it works very well for me.
> As for "storing documents in the DB versus on the file system", this is very
> much a open and very actively discussed subject, below is one past posting
> I've made on this subject with links included...
> "There are many issues related to storing the files on disk (faster access,
> easy copy/move, etc.) vs. storing the files in the database (transaction
> control, change control, audit of changes, etc) than just efficiency and
> scalability, although, those are important points... There is a ASPFAQ
> website that has a number of references
> (http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
> checked them all), but one major reason for storing the files in a SQL
> Server table's column defined with an IMAGE datatype, is that in SQL Server
> 2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
> Index the contents of supported file types, primarily MS Office, HTML and
> 3rd party IFilter's like Adobe's PDF files.
> As this is one of those FAQ type questions that have been known to start
> religious wars or at the very least a flame war, and while Sharon didn't ask
> about other application issues that this question often generates as it is
> often an open-ended question, i.e., one that never seems to be answered to
> everyone's satisfactions as it usually "depends" upon the application or
> upon how one defines the word "best"...
> As for the Terra Server, checkout the "about page"
> (http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
> explains how MS and others did it and yes, they used the IMAGE datatype,
> specifically
> http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
> contains the "blob" [image] field where the imagery data is stored.
> Jose, depending upon how many files you have, how frequently they change,
> how large they are, what the app is, etc. you may want to review the Terra
> Server web site and consider the following rule of thumb that they used:
> < 1 million images or big images (> 1MB) put them in the file system.
> Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
> For everything in between, either way will work, depending upon if you need
> transactional control over your files. Additionally, if you store the files
> in a TEXT or IMAGE column, you can also store related metadata about that
> file in SQL Server as well for increased searching capabilities. Also, and
> obviously with SQL Server you get built-in support for validating the
> consistency of the database, indices, backup, restore, etc. As for loading
> &/or extracting the files from SQL Server there are now many methods of
> doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
> to transform your files in some manner.
> Hope this helps, as the primary consideration should always be what is best
> for your application...
> Regards,
> John
>
> "Jose Perez" <jlpv@.totalise.co.uk> wrote in message
> news:3724a9d9.0404141212.480e1630@.posting.google.c om...

Complex SQL Query

Hi all,

I am developing an application using SQL Server as Back-end. I am facing a problem in creating a SQL Query. The details are as follows:

There are three tables in the Database, Data Type of all Columns is Numeric in all three tables:

1. T1

(column names and sample data)

en
==

1
2
3

2) T2

(column names and sample data)

en gn
== ==

1 10
1 11
2 10
2 12
2 13

3) T3

(column names and sample data)

en pn
== ==

1 20
1 21
1 22
2 20

Now I have to create a SQL Query, whereby I can get the following result:

en gn pn
== == ==
1 10 20
1 11 21
1 NULL 22
2 10 20
2 12 NULL
2 13 NULL

I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.there does not seem to be any join criterion

how do you know gn=10 matches pn=20?

try stating the join criterion on english, and i don't think you can do it

you may have to do your "matching" with application code|||I agree with rudy, tried playing with the query but could not come up with anything.|||I think this is what you search for:

create table ##tmp1 (en Int,pn Int,ref Int);
create table ##tmp2 (en Int,gn Int,ref Int);

insert into ##tmp1
select
a.en,
a.pn,
count(b.en) ref
from t3 a,t3 b
where a.en=b.en and a.pn>=b.pn
group by a.en,a.pn;

insert into ##tmp2
select
a.en,
a.gn,
count(b.en) ref
from t2 a,t2 b
where a.en=b.en and a.gn>=b.gn
group by a.en,a.gn;

select
coalesce(##tmp1.en,##tmp2.en) en,
##tmp1.pn,
##tmp2.gn
from ##tmp1
full join ##tmp2 on ##tmp1.en=##tmp2.en and ##tmp1.ref=##tmp2.ref
order by en