Re: Interesting parameter SQL issue

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)

Just do a little pl/sql if statement

begin

    if SecurityID is not null or SecurityIDType is not null then

        SELECT 
 	 S.SecurityKey 		"SecurityKey",
 	 S.SecurityID 		"SecurityID",
 	 S.SecurityIDType 	"SecurityIDType",
 	 ST.NumberingSystem	"NumberingSystem",
        FROM
 	 SecurityID 		"S",	
 	 SecurityIDType 		"ST"
        WHERE
 	 ST.SecurityIDType = S.SecurityIDType
    else 
        SELECT
  	 S.SecurityKey 		"SecurityKey",
 	 S.IdentifySecID		"SecurityID",
 	 S.IdentifySecIDType 	"SecurityIDType",
 	 ST.NumberingSystem	"NumberingSystem",
        FROM
 	 Security 		"S",	
 	 SecurityIDType 		"ST"
        WHERE
 	 ST.SecurityIDType = S.IdentifySecIDType
     end if;

Something like that should do it.

Adam Received on Tue Oct 16 2001 - 13:56:15 CEST