SQL Query

I have two databases: US and CAN. They contain the same table structure etc, so you can run the same query against both databases and get the correct results. One database will give results for United States, and the other for Canada.

I have a view that I've created in the US database called NationalAccounts. The view also exists in the Canada database. This is ok for when I need to look at the data seperately.

However, I now have a situation where I need the results combined.

Is it possible to have the NationalAccount view that's located in the US database to pull US data, and to also pull info from the Canada database?
LVL 1
isamesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Yes, using a UNION that crosses databases, and goes something like this...
CREATE VIEW US..NationalAccount AS

SELECT 'US' as county, goo, foo, boo
FROM US..TableName
UNION ALL
SELECT 'CAN', goo, foo, boo
FROM CAN..TableName
GO

Open in new window

If you have a more specific need, you'll have to provide more details.
ste5anSenior DeveloperCommented:
hmm, do you have any constraints in implementing your solution?

It sounds like those databases were separated for good reason, so I don't think using a combined view in one of the databases is a good idea.

You should create your own database to host those UNION queries.
John TsioumprisSoftware & Systems EngineerCommented:
UNION is the way to go,just remember that you should use the same amount of fields of each table and you are good to go....
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

isamesAuthor Commented:
@Jim Horn

So the view below is called NationalAccount and it pulls from US database. The full name of the view is US.tsi.nationalaccounts. The same for Canada.

The content of the view is below:

SELECT DISTINCT
CASE when LEN(a.ARCF_ALT_CUST_N) <= 1
  then ltrim(a.arcf_cust_n)
  else ltrim(a.arcf_alt_cust_n)
end as Cust_No,
CASE when LEN(a.arcf_parent_cust_n) > 1
  then (select CASE when LEN(b.arcf_alt_cust_n)<=1
                 then ltrim(b.arcf_cust_n)
                 else ltrim(b.arcf_alt_cust_n)
               end from arcf b where a.arcf_parent_cust_n=b.arcf_cust_n)
  else (CASE when LEN(a.arcf_alt_cust_n)<=1
         then ltrim(a.arcf_cust_n)
         else ltrim(a.arcf_alt_cust_n)
       end)
end as Sub_Account,
rscd_prod_n,
CASE WHEN CHARINDEX('-',RSCD_PROD_N) = 0 THEN rtrim(ltrim((RSCD_PROD_N))) ELSE rtrim(ltrim((SUBSTRING(RSCD_PROD_N,1,CHARINDEX('-',RSCD_PROD_N)-1)))) END as group_nbr,
CASE WHEN CHARINDEX('-',RSCD_PROD_N) = 0 THEN rtrim(ltrim((RSCD_PROD_N))) ELSE rtrim(ltrim((SUBSTRING(RSCD_PROD_N,CHARINDEX('-',RSCD_PROD_N)+1,6)))) END as serial_nbr,
RSPF_DESC,
GLDV_ALT_DIV_N current_location,
GLDV_ALT_N as store_nbr,
CASE
  WHEN RSCD_STATUS = 'O' and  rscd_time_disc_p<>100 then 'RENT'
  WHEN RSCD_STATUS = 'O' and rscd_time_disc_p=100 and rscd_time_disc_days<>0 and (dbo.DateOnly(getDate()) - dbo.DateOnly(d.RSCH_DATE)) >= rscd_time_disc_days then 'RENT'
  WHEN RSCD_STATUS = 'F' and rscd_off_rent_trx_n  > 1 and rsfr_ship_exchange ='Y' and rsfr_date_off < getDate() then 'PEND'
  WHEN RSCD_STATUS = 'O' and rscd_time_disc_p=100 and rscd_time_disc_days<>0 and (dbo.DateOnly(getDate()) - dbo.DateOnly(d.RSCH_DATE)) < rscd_time_disc_days then 'LOAN'
end as Rent_Status,
getDate() as asof_date,
ARCF_NAME,
ARCF_C_RATING,
RSPF_REPLACEMENT,
RSCD_DATE_OUT,
CASE WHEN (RSCD_STATUS = 'O' and rscd_time_disc_p=100)
  THEN RSCD_TIME_DISC_DAYS
  ELSE 0
END AS RENT_DAYS
FROM ARCF a
inner join rsch d on d.RSCH_CUST_N=a.ARCF_CUST_N
inner join RSCD c on c.RSCH_CONTRACT_N=d.RSCH_CONTRACT_N
inner join RSPF e on c.RSCD_PROD_N=e.RSPF_PROD_N
inner join ARCF_ALT f on a.ARCF_CUST_N=f.ARCF_CUST_N
inner join GLDV_ALT g on c.RSCD_LOC=GLDV_DIV
left outer join RSFR on c.rsch_contract_n=rsfr_contract_n
INNER JOIN
(SELECT RSIL_PROD_N, RSIL_ONHAND, GLDV_ALT_DIV_N, GLDV_DIV
      FROM GLDV
      INNER JOIN  RSIL ON (RSIL_DIV = GLDV_DIV)
      WHERE RSIL_ONHAND > 0  
      AND RTRIM(LTRIM(GLDV_DIV)) NOT IN ('200', '209', '228', '229') /* US */
      /* AND RTRIM(LTRIM(GLDV_DIV)) NOT IN ('9', '10', '11')  CA */)
LOCATION ON (RSIL_PROD_N = RSPF_PROD_N)
WHERE c.RSCD_QTY<>0
AND RSPF_RENT_DEPR_CODE <> 'LT'
AND RSPF_R_OR_S = 'R'
AND RSPF_INV_Y_N='Y'
AND RSPF_CLASS <> 'NOATF'
AND (RSCD_STATUS ='O' or (RSCD_STATUS = 'F' and LEN(rscd_off_rent_trx_n) > 1 and rsfr_ship_exchange ='Y' and rsfr_date_off < getDate()))
--AND ltrim(RSPF_CLASS) in
--('10 BX', '12 BX', '2" CF', '2" NS', '3" CF' , '3" NS', '35 CF', '4 BOX', '7 BOX', 'APPS', 'CAHDL', 'CFEXT', 'CFHDL', 'CFS', 'COBRA', 'CORSH', 'CR', 'CREXT', 'CRHDL', 'CS',
--'EXTN', 'FILLR', 'GSNEK', 'HANDL', 'MAXI', 'MESH', 'MH', 'MR', 'NSHDL', 'OUTCR', 'PUMPS', 'SKY', 'TAPER', 'UNIHD', 'UNIXT', 'XTNDR', 'XTDNS', 'XTDCR', 'XTDCF' ,'XTDCA')
GO


So are saying I should Union All this query against itself, but change the From to something like From [US].[tsi].[arcf a]?
Jim HornMicrosoft SQL Server Data DudeCommented:
>So are saying I should Union All this query against itself, but change the From to something like From [US].[tsi].[arcf a]?
Not against itself, but the Canada itself.   It'll go { SELECT ... FROM [US].[tsi].[arcf a]} UNION (or UNION ALL) {SELECT ... FROM [CANADA].[tsi].[arcf a]}, ensuring that the column order and data types of both queries are exactly the same.  If a column is in one query but not the other, you can add a placeholder like NULL as column_name.

UNION - Does a DISTINCT, and adds a little more time to your query.
UNION ALL - Does not do a DISTINCT, which in theory should be the case if the US db and CANADA db do not have the same rows, so it saves a little time.

For example:  How certain are you that these two sets do not have duplicate rows?
SELECT name FROM whorehouses
UNION ALL
SELECT name FROM burgerjoins

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Or better yet, have a NationalAccount view in both US and CANADA databases with the same exact T-SQL and specific to that databases, then create a view that uses them...
CREATE VIEW NationalAccountCombined AS
SELECT * FROM US.tsi.NationalAccount
UNION ALL
SELECT * FROM CANADA.tsi.NationalAccount
GO

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
isamesAuthor Commented:
I believe this might be better for me, as the NationalAccount view already exists in both databases.

Trying it now.
PortletPaulEE Topic AdvisorCommented:
Views on views can be troublesome, just be aware of that, as it only takes a very minor difference between the 2 views for things to go wrong. If either view changes it is likely to affect this combined view.

Do NOT assume the column order is the same in both views, specify them.

i.e. when Jim used "select *" I'm sure he was just using a shorthand for the answer, and not recommending it literally.
Jim HornMicrosoft SQL Server Data DudeCommented:
( Just a wild thought, instead of asking a token 'How's it going, eh?')

What's the purpose for having separate databases with exactly the same schema for US and CANADA?
Seems like one database would be appropriate.

If necessary you can modify the front-end to say 'eh?' a lot for Canadian customers.
Jim HornMicrosoft SQL Server Data DudeCommented:
Thanks for the grade.  Good luck with your project.  -Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.