Building a dynamic SQL using ANSI standards

[jscontreras]

Hi All,

I have an issue with an SELECT statement:
I'm using ANSI SQL with a lot of outerjoins and they need to be compatible with SQL Server and Oracle. The problem is there a criteria screen that contains multiple parameters that need to be passed. Using the "Where" for the criteria will not worked because the outer joins need to be universal for both SQL Server and Oracle. The where clause is replace with HAVING and this is the problem.

How do I dynamically generate the HAVING Statement without knowing how many parameters are being passed. For example

Let's say I have:

SELECT a.projectID,b.ProjectManager,c.ProjectDescription
FROM (((Project a LEFT JOIN Person b ON a.PID = b.ID)
INNER JOIN ProjectDetails c on a.PID = c.PID)
Now if I have a parameter I need to display that parameter in not I'm done:
If I have a parameter I'll do the following:
HAVING (((a.PID IN(" & paramValues & &quot"

What do I do if I have another one and what if the first paramater isn't pick but the third parameter? and I need to put in the correct number of parathesis in the having clause

I'm not really sure how to approach this problem I would appreciate any help.

Thanks

 

[Dynapen]

First of all, why are you using the Having clause again? And why does it need to be acceptable for both Oracle and SQL Server. Are you going to be using the same statement against 2 different databases? Can you use a if statement to determine which DB you are connecting to and the rewrite the statement that way. The money's gone, the brain is shot.....but the liquor we still got.

 

[jscontreras]

The reason is I'm using outer joins both right and left. The requirement is have SQL database independent, ie this SQL needs to work on any database. SQL Server uses this syntax *= or =* for Outer Joins Oracle uses +. These are very specific syntax. No that is not the requirement that they want to use toggle between this database and that database. I need to make this a generic SQL understood by most databases.

Also are there any examples on searching using multiple parameters using SQL, this would probably solve my problem.

Thanks