Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    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.
    Last edited by ingineu; 02-19-04 at 15:06.

  9. #9
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    Thanks Brett ... I'll give it a go.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Here's a model

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •