SQL for Multiple Databases?

[thethuthinnang]

I am a n00b to Crystal and SQL so I hope I can explain this
properly. I am trying to query multiple companies that would be selected by the user. So I need to be able to allow multiple values for the parameter {?CompanyCode} below.

A simplified version of my SQL query:

SELECT
"orders"."status",
"orders"."type"

FROM "{?CompanyCode}"."dbo"."orders" "orders"

WHERE
"orders"."type"='I' AND
"orders"."status"='N'

Company Code is a parameter selected by the user. It is simple enough to disallow multiple selction and make it a drop down box, the user selects Australia so it queries Australia.

-----------------------------

I have used a union join to allow the user to select multiple companies (which shocked me that it worked as I didn't really know what I was doing):

SELECT
"orders"."status",
"orders"."type",
"orders"."company_code"

FROM "australia"."dbo"."orders" "orders"

WHERE
"orders"."type"='I' AND
"orders"."status"='N'

UNION

SELECT
"orders"."status",
"orders"."type",
"orders"."company_code"

FROM "uk"."dbo"."orders" "orders"

WHERE
"orders"."type"='I' AND
"orders"."status"='N'

And then I use Select Expert to filter the Company Code by the companies selected by the user.

-------------------------------

Using a union join causes 2 problems if I understand correctly:

1) Efficiency, I have to copy, paste and modify the code for each company that is on the parameter list. Also, it looks like the report queries all companies and then filters them by the parameters selected, I think it would be more efficient if only the parameters selected were queried? This hasn't been a huge problem yet but it seems sloppy and I can see where it might be a problem later.

2) If I'm trying to write the report for a table that doesn't have a "company_code" field it will show all of the companies.

-----------------------------

Is there a way to allow multiple selection for the parameter

{?CompanyCode}

Thanks!

 

[lbass]

Please repost this in the correct forum, either forum149 or forum767. This thread is unrelated to the current forum.

-LB