Selecting Data from multiple tables using wildcard

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting Data from multiple tables using wildcard

Author

Topic

guygs18
Starting Member

4 Posts

Posted - 2012-04-18 : 11:00:42

Hi

I have multiple tables, with identical column names, that I join to a number of other unique tables. I then use Case to select data from these tables to omit null values:

e.g. case when x is null then y else x etc etc

However I am concerned that my queries are not slow due to the high number of case functions I have to include. Every time there is a change in our forms, a new copy of this form table is produced, and for historic reporting I have to query each copy to obtain the relevant data. I also have a table that stores the names (and IDs) of all tables & was wondering whether I could use a wildcard to query this table to automatically query all historic tables. As all the column headers are the same, I was hoping to use a subquery like:

select x, y, x
from (
select form_name
from table_of_table_names
where form_name like '%mytypeofform%'
)a

And I could then join this data as normal, but obviously the above doesn't work. This would also avoid me having edit my queries every time a change occurred. It could be that what I'm trying to achieve is no faster than my full outer joins & multiple case functions, but every time I add an additional case function the reports appear to run much slower.

Many thanks in advance for any assistance anybody can provide.

Guy Southcott

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 01:05:38

for iterating over tablenames and using query involving it you need dynamic sql.
do you mean to say that you use case expression in join condition to determine joining column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

guygs18
Starting Member

4 Posts

Posted - 2012-04-19 : 04:59:10

I use case in the select statements - I may have 3 tables that I need to query, but I only want the data in a single column from these tables. Please see the script below - sorry for pasting it here, but thought it might help:

e.g.

SELECT DISTINCT
FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID AS 'Case ID',
FLODS_CLASSIFICATION_D00.TYPE AS 'Type',
CASE WHEN FLODS_101000385_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN (CASE WHEN FLODS_101000386_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN (CASE WHEN FLODS_101000488_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN (CASE WHEN FLODS_101000489_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN (CASE WHEN FLODS_101000508_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN (CASE WHEN FLODS_101000509_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL
THEN 'No Payment Status' ELSE FLODS_101000509_SPECIAL_UP_C00.PAYMENTSTATUS end)ELSE FLODS_101000508_SPECIAL_UP_C00.PAYMENTSTATUS end)
ELSE FLODS_101000489_SPECIAL_UP_C00.PAYMENTSTATUS END)
ELSE FLODS_101000488_SPECIAL_UP_C00.PAYMENTSTATUS END)
ELSE FLODS_101000386_SPECIAL_UP_C00.PAYMENTSTATUS END)
ELSE FLODS_101000385_SPECIAL_UP_C00.PAYMENTSTATUS END AS 'Payment Status',
FLODS_101000311_SPECIAL_UP_E00.CMBO_LOCATION AS 'Location', FLODS_101000311_SPECIAL_UP_E00.TXT_DESCRIPTION AS 'Description',
FLODS_101000311_SPECIAL_UP_E00.TXT_OTHER AS 'Other Location', CONVERT(varchar(10), FLODS_TIMELINE_D00.TIMELINE_DT, 103) AS 'Release Date',
FLODS_PARTY_ADDRESS_D00.ADDRESS_NUM AS 'Property Number', FLODS_PARTY_ADDRESS_D00.ADDRESS_LINE_1 AS 'Address Line 1',
FLODS_PARTY_ADDRESS_D00.ADDRESS_LINE_2 AS 'Address Line 2', FLODS_PARTY_ADDRESS_D00.POST_CODE AS 'Post Code',
CASE WHEN ltrim(rtrim(ADDRESS_LINE_4)) IN ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown',
'Torrance', 'Westerton') THEN upper(substring(ltrim(rtrim(ADDRESS_LINE_4)), 1, 1)) + lower(substring(ltrim(rtrim(address_line_4)), 2, 99))
ELSE (CASE WHEN ltrim(rtrim(address_line_3)) IN ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown',
'Torrance', 'Westerton') THEN upper(substring(ltrim(rtrim(ADDRESS_LINE_3)), 1, 1)) + lower(substring(ltrim(rtrim(address_line_3)), 2, 99))
ELSE (CASE WHEN ltrim(rtrim(address_line_2)) IN ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown',
'Torrance', 'Westerton') THEN upper(substring(ltrim(rtrim(ADDRESS_LINE_2)), 1, 1)) + lower(substring(ltrim(rtrim(address_line_2)), 2, 99)) ELSE 'Other' END) END)
END AS Area, FLODS_INDIVIDUAL_D00.TITLE + ' ' + FLODS_INDIVIDUAL_D00.SURNAME AS 'Name',
CASE WHEN FLODS_101000385_SPECIAL_UP_C00.AMOUNT IS NULL
THEN (CASE WHEN FLODS_101000386_SPECIAL_UP_C00.AMOUNT IS NULL
THEN (CASE WHEN FLODS_101000488_SPECIAL_UP_C00.AMOUNT IS NULL
THEN (CASE WHEN FLODS_101000489_SPECIAL_UP_C00.AMOUNT IS NULL
THEN (CASE WHEN FLODS_101000508_SPECIAL_UP_C00.AMOUNT IS NULL
THEN (CASE WHEN FLODS_101000509_SPECIAL_UP_C00.AMOUNT IS NULL
THEN '£0.00' ELSE FLODS_101000509_SPECIAL_UP_C00.AMOUNT END)
else FLODS_101000508_SPECIAL_UP_C00.AMOUNT end)
ELSE FLODS_101000489_SPECIAL_UP_C00.AMOUNT END)
ELSE FLODS_101000488_SPECIAL_UP_C00.AMOUNT END)
ELSE FLODS_101000386_SPECIAL_UP_C00.AMOUNT END)
ELSE FLODS_101000385_SPECIAL_UP_C00.AMOUNT END AS 'Amount'

FROM FLODS_CLASSIFICATION_D00 FULL OUTER JOIN
FLODS_CASE_ENQUIRY_D00 FULL OUTER JOIN
FLODS_101000489_SPECIAL_UP_C00 FULL OUTER JOIN
FLODS_101000508_SPECIAL_UP_C00 full outer JOIN
FLODS_CASE_ENQUIRY_F00 ON FLODS_101000508_SPECIAL_UP_C00.CASE_ENQUIRY_ID = FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID full outer JOIN
FLODS_101000509_SPECIAL_UP_C00 ON
FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID = FLODS_101000509_SPECIAL_UP_C00.CASE_ENQUIRY_ID FULL OUTER JOIN
FLODS_101000488_SPECIAL_UP_C00 ON FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID = FLODS_101000488_SPECIAL_UP_C00.CASE_ENQUIRY_ID ON
FLODS_101000489_SPECIAL_UP_C00.CASE_ENQUIRY_ID = FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID ON
FLODS_CASE_ENQUIRY_D00.FLODS_ID = FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID ON
FLODS_CLASSIFICATION_D00.FLODS_ID = FLODS_CASE_ENQUIRY_F00.CLASSIFICATION_ID FULL OUTER JOIN
FLODS_TIMELINE_D00 ON FLODS_CASE_ENQUIRY_F00.START_TIMELINE_ID = FLODS_TIMELINE_D00.FLODS_ID FULL OUTER JOIN
FLODS_INDIVIDUAL_D00 ON FLODS_CASE_ENQUIRY_F00.OBJECT_ID = CAST(FLODS_INDIVIDUAL_D00.FLODS_ID AS varchar(80)) FULL OUTER JOIN
FLODS_PARTY_ADDRESS_M00 ON FLODS_INDIVIDUAL_D00.FLODS_ID = FLODS_PARTY_ADDRESS_M00.PARTY_ID FULL OUTER JOIN
FLODS_PARTY_ADDRESS_D00 ON FLODS_PARTY_ADDRESS_M00.PARTY_ADDRESS_ID = FLODS_PARTY_ADDRESS_D00.FLODS_ID FULL OUTER JOIN
FLODS_101000385_SPECIAL_UP_C00 ON
FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID = FLODS_101000385_SPECIAL_UP_C00.CASE_ENQUIRY_ID FULL OUTER JOIN
FLODS_101000386_SPECIAL_UP_C00 ON
FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID = FLODS_101000386_SPECIAL_UP_C00.CASE_ENQUIRY_ID FULL OUTER JOIN
FLODS_101000311_SPECIAL_UP_E00 ON FLODS_CASE_ENQUIRY_F00.CASE_ENQUIRY_ID = FLODS_101000311_SPECIAL_UP_E00.CASE_ENQUIRY_ID

WHERE (FLODS_TIMELINE_D00.TIMELINE_DT > '2011-10-12') AND (FLODS_CLASSIFICATION_D00.REASON = 'Special Uplifts')
and flods_case_enquiry_D00.STATUS in (@Status)
and FLODS_CLASSIFICATION_D00.TYPE in (@Type)
and FLODS_TIMELINE_D00.TIMELINE_DT between @DateFrom and @DateTo
and
CASE WHEN FLODS_101000385_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL THEN
(CASE WHEN FLODS_101000386_SPECIAL_UP_C00.PAYMENTSTATUS IS NULL THEN
(case when FLODS_101000488_SPECIAL_UP_C00.PAYMENTSTATUS is null then
(case when FLODS_101000489_SPECIAL_UP_C00.PAYMENTSTATUS is null then
(case when FLODS_101000508_SPECIAL_UP_C00.PAYMENTSTATUS is null then
(case when FLODS_101000509_SPECIAL_UP_C00.PAYMENTSTATUS is null then
'No Payment Status' else FLODS_101000509_SPECIAL_UP_C00.PAYMENTSTATUS end)
else FLODS_101000508_SPECIAL_UP_C00.PAYMENTSTATUS end)
ELSE FLODS_101000489_SPECIAL_UP_C00.PAYMENTSTATUS END)
else FLODS_101000488_SPECIAL_UP_C00.PAYMENTSTATUS end)
else FLODS_101000386_SPECIAL_UP_C00.PAYMENTSTATUS end)
ELSE FLODS_101000385_SPECIAL_UP_C00.PAYMENTSTATUS END in (@PaymentStatus)
and CASE WHEN ltrim(rtrim(ADDRESS_LINE_4)) in ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown', 'Torrance', 'Westerton')
then upper(substring(ltrim(rtrim(ADDRESS_LINE_4)),1,1))+lower(substring(ltrim(rtrim(address_line_4)),2,99)) else
(case when ltrim(rtrim(address_line_3)) in ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown', 'Torrance', 'Westerton')
then upper(substring(ltrim(rtrim(ADDRESS_LINE_3)),1,1))+lower(substring(ltrim(rtrim(address_line_3)),2,99)) else
(case when ltrim(rtrim(address_line_2)) in ('Bearsden', 'Kirkintilloch', 'Milngavie', 'Twechar', 'Milton of Campsie', 'Lenzie', 'Bishopbriggs', 'Lennoxtown', 'Torrance', 'Westerton')
then upper(substring(ltrim(rtrim(ADDRESS_LINE_2)),1,1))+lower(substring(ltrim(rtrim(address_line_2)),2,99)) else
'Other' end)end) end in (@Area)
ORDER BY 'Case ID'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources