Re: SQL solution needed

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/17
Message-ID: <8dfrnv$fhl$1@nnrp1.deja.com>#1/1

Try this instead:

CREATE VIEW myView(

	  product
	, name
	, value
	)

AS

SELECT
  temp.productname
, parameter.name
, parameter.value
FROM
  parameter
  (

	SELECT
	  product.name productname
	, parameter.name parametername
	, MAX(parameter.version) maxversion
	FROM
	  parameter
	, product_map
	, product
	WHERE product.version = product_map.version
	AND product_map.parent_version = parameter.version
	GROUP BY
	  product.name
	, parameter.name

  ) temp
WHERE parameter.name = temp.parametername AND parameter.version = temp.maxversion; /

HTH,
Michael J. Ort

In article <8dfhkh$49a_at_nntpb.cb.lucent.com>,   "Gary Knopp" <gknopp_at_ascend.com> wrote:
>
> I'm trying to develop a system that stores all the parameters for a
> particular product release. As new releases are introduced, I want to
> be able to utilize parameters from past products that still apply if
 the
> parameter value is still the same ( to avoid record duplication). At
 any
> time I want to be able to query a past product and get a list of its
> parameters
> and values.
>
> I have figured out a way to do this with a view using a correlated
 subquery,
> however it
> is not the most efficient solution. Does anyone have a better idea
 how I
> could
> accomplish the above. I have enclosed my solution below.
>
> /**********************************************************/
> Below is an example of a solution with the expected results for each
 query.
>
> PRODUCT - holds distinct names of all products
> PRODUCT_MAP - identifies past product parameters that are inherited on
> newer version.
> PARAMETER - contains a list of parameter and values. If a
 paramter
> value
> changes in a new version than a new
> record is created for
> that new version.
>
> TABLE DATA
> =============
>
> PRODUCT PRODUCT_MAP
> PARAMETER
> ============== =====================
> =========================
> version name version parent_version
 name
> value version
> ---------- ---------- ---------- ------------------
 ---
> ------- ------- -----------
> 1 Prd_A 1 1
> voltage 10 1
> 2 Prd_B 2 1
> current 20 1
> 3 Prd_C 2 2
> voltage 11 2
>

 3                      1


> current           21                3

>

 3                      2


> power           231              3

>

 3                      3

>
> /*************************************************************/
> CREATE VIEW myView (product,name,value)
> as
> SELECT prd.name, p.name, p.value
> FROM product prd1
> parameter param1,
> WHERE p.version=
> (SELECT
> max(param2.version)
> FROM
> parameter param2
> WHERE
> param2.name=param1.name and
> param2.version in (
> SELECT
>

 map.parent_version
> FROM
> product
 prd2,
> product_map
 map
> WHERE
> prd2.version
 =
> map.version and
> prd2.name =
> prd1.name
> )
> );
>
> /***************************************************************/
> RESULTS from running queries
> ==========================
> Select name,value from myView where product = 'Prd_A'
>
> ... = 'Prd_B'
>
> ... = 'Prd_C'
>
> Prd_A Prd_B
> Prd_C
> ======== ==========
> ===========
> name value name value
> name value
> -------- -------- ------- -

---

> - --------- ---------
> voltage 10 voltage 11
> voltage 11
> current 20 current
 20
> current 21
>
> power 231
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 17 2000 - 00:00:00 CDT