Re: Interesting parameter SQL issue

From: Oliver Kiessling <o.kiessling_at_gmx.net>
Date: 19 Oct 2001 01:50:11 -0700
Message-ID: <bb6e74df.0110190050.43b2000a_at_posting.google.com>

traceable1_at_hotmail.com (traceable1) wrote in message news:<8551d8c9.0110151444.3c6d1c32_at_posting.google.com>...
> I need to create a view that pulls data as described below. Any
> suggestions?
>
> Assume we have a view with four parameters: SecurityKey, SecurityID,
> SecurityIDType and NumberingSystem. However, we need to get the data
> differently, depending on what is specified. Assume that SecurityKey
> is always specified. SecurityID and/or SecurityIDType may or may not
> be specified. Numbering system will never be specified.
>
> If SecurityID or SecurityIDType is specified, then we want to:
>
> SELECT
> S.SecurityKey "SecurityKey",
> S.SecurityID "SecurityID",
> S.SecurityIDType "SecurityIDType",
> ST.NumberingSystem "NumberingSystem",
> FROM
> SecurityID "S",
> SecurityIDType "ST"
> WHERE
> ST.SecurityIDType = S.SecurityIDType
>
> But if neither SecurityID or SecurityIDType is specified, then we want
> to link into the security table instead of the SecurityID table like
> so:
>
> SELECT
> S.SecurityKey "SecurityKey",
> S.IdentifySecID "SecurityID",
> S.IdentifySecIDType "SecurityIDType",
> ST.NumberingSystem "NumberingSystem",
> FROM
> Security "S",
> SecurityIDType "ST"
> WHERE
> ST.SecurityIDType = S.IdentifySecIDType
>
>
> Any ideas are greatly appreciated!!
>
> Thanks! :0)

How about a simple UNION? Or did I get something wrong?

SELECT S.SecurityKey      "SecurityKey",
       S.SecurityID       "SecurityID",
       S.SecurityIDType	  "SecurityIDType",
       ST.NumberingSystem "NumberingSystem"
  FROM SecurityID     S,
       SecurityIDType ST

 WHERE ST.SecurityIDType = S.SecurityIDType  UNION

SELECT S.SecurityKey,
       S.IdentifySecID,
       S.IdentifySecIDType,
       ST.NumberingSystem
  FROM Security       S,
       SecurityIDType ST

 WHERE ST.SecurityIDType = S.IdentifySecIDType    AND S.SecurityIDType IS NULL

• Oliver

Received on Fri Oct 19 2001 - 10:50:11 CEST