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

Computed Columns

I have a quick question for someone that has worked with
computed columns. Can a computed column within a table
reference a field from another table in it's formula. If
so, how? Also, if using a view that joins multiple
tables, how can you do an insert with that view, or can
you? Have tried to look this up, but can't find much.
Thanks,
Van JonesHi Van,
1) You acces other tables on SQL Server 2000 if you use a user defined
function in the formula of your computed column.
2) You can insert in a view that joins multiple table if you create an
INSTEAD OF trigger on that view. Once again this is SQL Server 2000 only,
this won't work on earlier versions.
--
Jacco Schalkwijk
SQL Server MVP
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:09b601c3a7a3$c4dcfa30$a401280a@.phx.gbl...
> I have a quick question for someone that has worked with
> computed columns. Can a computed column within a table
> reference a field from another table in it's formula. If
> so, how? Also, if using a view that joins multiple
> tables, how can you do an insert with that view, or can
> you? Have tried to look this up, but can't find much.
> Thanks,
> Van Jones|||Thanks for your help.
Van
>--Original Message--
>Hi Van,
>1) You acces other tables on SQL Server 2000 if you use a
user defined
>function in the formula of your computed column.
>2) You can insert in a view that joins multiple table if
you create an
>INSTEAD OF trigger on that view. Once again this is SQL
Server 2000 only,
>this won't work on earlier versions.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:09b601c3a7a3$c4dcfa30$a401280a@.phx.gbl...
>> I have a quick question for someone that has worked with
>> computed columns. Can a computed column within a table
>> reference a field from another table in it's formula.
If
>> so, how? Also, if using a view that joins multiple
>> tables, how can you do an insert with that view, or can
>> you? Have tried to look this up, but can't find much.
>> Thanks,
>> Van Jones
>
>.
>|||Van,
Although there are ways to achieve what you ask, you should wonder
whether to use this. There is nothing you can do with a computed column
that you can't do with a view. However, creating a computed column
complicates the table structure, especially if it is based on a UDF.
I would avoid using the (SQL-Server proprietary) computed columns, and
use them only if I *really* needed them.
My 5 cents,
Gert-Jan
Van Jones wrote:
> I have a quick question for someone that has worked with
> computed columns. Can a computed column within a table
> reference a field from another table in it's formula. If
> so, how? Also, if using a view that joins multiple
> tables, how can you do an insert with that view, or can
> you? Have tried to look this up, but can't find much.
> Thanks,
> Van Jones

composing a reference from fields located in mutiple tables

Consider a situation. There is a table of submitted 'documents'. They have some attributes. There are assignments to process the things, which have a date they were created. Finally there is a price list which specifies the price according to document features and date, so that the assignment to process a document created at different time will have a different cost. In other words, there is a relation
(assignment->document.attribute(s) + assignment.date) -> pricelist.price

Creating relations has the integrity advantages: it is not possible to create an assignment, which price is not defined in the pricelist; precludes the pricelist entry removal if it is referred by any assignments.

Should a view, which combines all the foreign fields into one virtual table, be created to make establishing the reference possible?

Not quite sure I understand the entire request. However, it seems you should be able to enforce referential integrity via Instead Of trigger.

Perhaps, you could give us some sample DDL and desired output to better describe your issue. We might be able to help further then.

|||Normally, you have all the tables interrelated. The reference (a foreign key) points to an object in another table specifying the "container" it belongs to. For instance, many books refer a single author.

Sometimes, you need to establish a complex reference consisting of multiple fields. For instance, a job refers to pricelist. The options in the print job specify a "service id", which has a unique price in the pricelist.

Suppose now that the pricelist can be updated. When job is created, it fixes the latest service cost in int field, the pricelist date. So the cost is uniquely identified by the job options (some fields) and the date. This is a complex key.

What I have faced is that nobody addresses the possibility of having the job attributes fixed in a separate table (say, documents to be processed always have the same settings). A job refers a document, from which the attributes are derived and coupled with the pricelist date identifies the job cost in the pricelist table. Effectively, the complex key is composed from fields located in different tables. A record contains only a part of complex key plus a reference to another entity, which has a rest of the key.

One way to create a relation would be to produce a view joining the key field tables. However, views are not enabled in diagrams. I suppose the reason is because the views are not allowed to participate in data relations.

Actually, I have decided that in my case I do not need to fix the job settings in the referred document, so I'll have all the key fields in one table. Yet, the topic is quite general to be interesting for me and others.

Complex File Import

I am trying to import a flat file of semi-colon delimited values into a
table. The table has many foreign keys that reference other tables. The
flat file has the data all explicitly stated instead of the ID numbers. I
want to make it so that if a new value is encountered, a new entry in that
related table is created. An example: The main table, CITTAS_Primary, has a
foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, whic
h
has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayage.
I can parse the entries that exist in the table already, but I am unsure of
how to insert new values using an ActiveX Script Task. I am currently using
Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
CITTAS_Drayage_Company and then place the resulting rowsets into global
variables. Here is the code that I have so far:
Function Main()
Dim Company, CompanyID
Company = DTSSource("Col003")
Dim Charge, ID
Charge = DTSSource("Col009")
ID = -1
Dim comps, chs
Set comps = CreateObject("ADODB.Recordset")
Set comps = DTSGlobalVariables("DrayageCompany").Value
Set chs = CreateObject("ADODB.Recordset")
Set chs = DTSGlobalVariables("DrayageCharge").Value
Dim i
For i = 1 To comps.RecordCount
If comps.Fields("DrayageCompany") = Company Then
CompanyID = comps.Fields("CompanyID")
Break
End If
comps.MoveNext
Next i
For i = 1 To chs.RecordCount
If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID") =
CompanyID Then
ID = chs.Fields("DrayageID")
Break
End If
chs.MoveNext
Next i
If ID = -1 Then
'Insert code to insert new record
'Get ID for new cost/company pair
End If
DTSDestination("DrayageCompanyID") = ID
Main = DTSTransformStat_OK
End Function
I want it to be able to insert new companies into CITTAS_Drayage and new
cost/company pairs into CITTAS_Drayage_Company.
Also, for some reason, it is complaining about using the RecordCount
property of the recordset. I would think that it would work since it is
straight from SQL Server Books Online DTS collection. If anyone knows what'
s
going on with this, please enlighten me, because it amkes no sense to me.
Thanks in advance, everyone.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi Chris
I am not sure of the example you talk about but
DTSGlobalVariables("DrayageCompany").Value may not be a recordset.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||BTW... I was going to add..
Rather than mess around with the activeX script it may be quicker (and IMO
easier) to load into a staging table, sort out the missing FKs and then load
into the live table.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps

compilation error while adding web reference RS05

hi,
if i try to add a web reference in my c# application to reporting services
2005 i got a compilation error.
a part of the displayed message:
--
source error:
line 4: <%@. Import Namespace="System.Xml" %> <-- is a red line
error message:
CS0234: The type or namespace name 'Xml' does not exist in the namspace
'System' (are you missing an assembly reference?
do anybody know a solution for this problem?
thx
MartinTake a look at this forum:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=10847
particularly the post from AkhilaL MSFT
Jens Konerow