Prev: Sql server 2008 r2 hangs up
Next: Top 2 from count
From: --CELKO-- on 11 Aug 2010 14:25
>> I agree with the 'tbl' prefix. And please don't jump to conclusions as we did not create this database we are just pulling some data from it for our customer. Those of us not in the "ivory tower" world have many weird things we have to deal with, including designs from other vendors. <<

LOL! Ever work with SAP? The "Teutonic numonics" will drive you
nuts.

>> .. We have to SUM detail from 4 other header/detail tables. If I join them here also then I will get X times more total than I want. Any ideas on the best way to proceed? Should I build 3 summary views and then JOIN them on the item_id to get a single summary by ItemId (named part_id in the one table)? <<

Changing data element names can be worse than silly "tbl-" prefixes.
What about a UNION? Specifically an outer union? It gives you a wide
table but puts the data together. Here is a skeleton.

WITH Monster (item_id, ..)
AS
(
SELECT (item_id, <source A columns>, <NULLs for B>, <NULLs for C>,
<NULLs for D>
FROM Source_A
UNION ALL
SELECT (item_id, <NULLs for A>, <source B columns>, <NULLs for C>,
<NULLs for D>
FROM Source_B
UNION ALL
SELECT (item_id, <NULLs for A>, <NULLs for B>, <source C coumns>,
<NULLs for D>
FROM Source_C
UNION ALL
SELECT (item_id, <NULLs for A>, <NULLs for B>, <NULLs for C>, <source
D columns>
FROM Source_D
),

ConsolidatedMonster (item_id, ..)
AS
(
SELECT item_id, MAX(A.c1),MAX(A.c2), ..MAX(B.c1), ..MAX(<C columns>),
MAX(<D columns>)
FROM Monster
GROUP BY item_id)

<< insert base select here >> ;

The idea is to use MAX (which works on all data types) to keep data
from each of the four sources and put them next to each other in a
single row. I would also use CAST (NULL AS <data type>) in the dummy
columns to be safe and to help the compiler.

I am assuming since they are diffrent tables, they model different
data elements.

FYI, OUTER UNION is part of the SQL Standards but nobody has
implemented it as far as I know.

First  |  Prev  | 
Pages: 1 2
Prev: Sql server 2008 r2 hangs up
Next: Top 2 from count