Hi Richard,
I tend to use a series of CREATE VIEW AS SELECT type statements to
effectively nest my SELECT statements, making them more humanly
editable and manageable. Consequently, the size of the maximum sized
single SQL statement is reduced, though the total SQL statement
effectively executed may be reasonably large. So I don't know if such
a series would be of use to look at, for your purposes.
In any case, below is a series of nested CREATE VIEW ... AS SELECT
statements, which if combined into the one that effectively runs when
the first "Aligned_All_Accept" is run, is quite large (larger than
the sum of its parts). The longest single SQL statement is probably
the view called "Structured_Amicroe".
For the curious, this set of views modifies the varied source price
list data from five supplier price lists into one homogeneous view,
selectively adding calculated fields (such as image URLs) and
removing records with dodgy data.
Tom
CREATE VIEW Aligned_All_Accept AS
SELECT * FROM Aligned_All EXCEPT SELECT * FROM Aligned_All_Reject
CREATE VIEW Aligned_All_Reject AS
SELECT * FROM Aligned_All WHERE Buy = 0 OR Buy * 1.1 > RRP AND RRP
IS NOT NULL AND RRP > 0
CREATE VIEW Aligned_All AS
SELECT * FROM Aligned_Amicroe
UNION ALL
SELECT * FROM Aligned_Apple
UNION ALL
SELECT * FROM Aligned_BlueChipIT
UNION ALL
SELECT * FROM Aligned_MacSense
UNION ALL
SELECT * FROM Aligned_PowerMove
UNION ALL SELECT * FROM Aligned_Simms
CREATE VIEW Aligned_Amicroe AS
SELECT rowid, Supplier, Category, Category2, Category3, Code,
Description, NULL AS Detail, NULL AS Thumbnail, NULL AS Image, NULL
AS Manufacturer, NULL AS URL, NULL AS Warranty, Buy, NULL AS RRP,
Sell, NULL AS Stock, NULL AS Ordered, NULL AS Due, Modified FROM
Calculated_Amicroe
CREATE VIEW Aligned_Apple AS
SELECT rowid, Supplier, Category, Category2, Category3, Code,
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,
Modified FROM Calculated_Apple
CREATE VIEW Aligned_BlueChipIT AS
SELECT rowid, Supplier, Category, Category2, NULL AS Category3,
Code, Description, Detail, Thumbnail, Image, Manufacturer, NULL AS
URL, NULL AS Warranty, Buy, NULL AS RRP, Sell, Stock, Ordered, Due,
Modified FROM Calculated_BlueChipIT
CREATE VIEW Aligned_MacSense AS
SELECT rowid, Supplier, Category, Category2, Category3, Code,
Description, Detail, NULL AS Thumbnail, Image, NULL AS Manufacturer,
URL, NULL AS Warranty, Buy, RRP, Sell, NULL AS Stock, NULL AS
Ordered, NULL AS Due, Modified FROM Calculated_MacSense ORDER BY lower
(Category), lower(Category2), lower(Category3)
CREATE VIEW Aligned_PowerMove AS
SELECT rowid, Supplier, Category, Category2, NULL AS Category3,
Code, Description, Detail, NULL AS Thumbnail, Image, NULL AS
Manufacturer, NULL AS URL, NULL AS Warranty, Buy, RRP, Sell, Stock,
NULL AS Ordered, NULL AS Due, Modified FROM Calculated_PowerMove
CREATE VIEW Aligned_Simms AS
SELECT rowid, Supplier, Category, Category2, Category3, Code,
Description, Detail, NULL AS Thumbnail, Image, Manufacturer, NULL AS
URL, Warranty, Buy, RRP, Sell, Stock, NULL AS Ordered, NULL AS Due,
Modified FROM Calculated_Simms
CREATE VIEW Calculated_Amicroe AS
SELECT rowid, Category, Category2, Category3, Code,
Products.Description AS Description, Buy, Products.Modified AS
Modified, 'Amicroe' AS Supplier, Round(Buy * 1.1 * 1.13 + 0.5) AS
Sell FROM Structured_Amicroe AS Products JOIN Suppliers ON
Suppliers.Name='Amicroe' WHERE Buy IS NOT NULL AND Buy > 0.0
CREATE VIEW Calculated_Apple AS
SELECT rowid, Category, Category2, Category3, Code,
Products.Description AS Description, Detail, CASE WHEN Image LIKE '%
CPP00869.JPG' OR Image LIKE '%CPP00011.JPG' OR Image LIKE '%
CPP00014.JPG' OR Image LIKE '%simmsnoimage.jpg' THEN NULL ELSE
'http://www.simms.com.au/direct/' || Image END AS Image,
Manufacturer, Warranty, Buy, RRP, Stock, Products.Modified AS
Modified, 'Apple' AS Supplier, CASE WHEN RRP NOTNULL AND RRP > 0 AND
Round(Buy * 1.1 * (1 + Markup) + 0.5) > RRP THEN Round((Buy * 1.1 * 2
+ RRP * 8) / 10) ELSE Round(Buy * 1.1 * (1 + Markup) + 0.5) END AS
Sell FROM Structured_Apple AS Products JOIN Suppliers ON
Suppliers.Name='Apple'
CREATE VIEW Calculated_BlueChipIT AS
SELECT rowid, Category, Category2, Code, Products.Description AS
Description, Detail, Manufacturer, Buy, Stock, Ordered, Due, 'http://
www.bluechipit.com.au/content/_images/' || Code || '_sm.jpg' AS
Thumbnail, 'http://www.bluechipit.com.au/content/_images/' || Code ||
'_lg.jpg' AS Image, Products.Modified AS Modified, 'BlueChipIT' AS
Supplier, Round(Buy * 1.1 * (1 + Markup) + 0.5) AS Sell FROM
Structured_BlueChipIT AS Products JOIN Suppliers ON
Suppliers.Name='BlueChipIT' WHERE Category != 'Services'
CREATE VIEW Calculated_MacSense AS
SELECT rowid, Category, Category2, Category3, Code,
Products.Description AS Description, Detail, Image, Manufacturer,
URL, Buy, RRP, Compatibility, Products.Modified AS Modified,
'MacSense' AS Supplier, CASE WHEN RRP NOTNULL AND RRP > 0 AND Round
(Buy * 1.1 * (1 + Markup) + 0.5) > RRP THEN Round((Buy * 1.1 * 2 +
RRP * 8) / 10) ELSE Round(Buy * (1 + Markup) * 1.13 + 0.5) END AS
Sell FROM Structured_MacSense AS Products JOIN Suppliers ON
Suppliers.Name='MacSense'
CREATE VIEW Calculated_PowerMove AS
SELECT rowid, Category, Category2, Code, Description, Detail, Buy,
RRP, Stock, 'http://www.powermove.com.au/images/' || Code || '.jpg'
AS Image, Modified, 'PowerMove' AS Supplier, CASE WHEN RRP NOTNULL
AND RRP > 0 AND Round(Buy * 1.1 * 1.13 + 0.5) > RRP THEN Round((Buy *
1.1 * 2 + RRP * 8) / 10) ELSE Round(Buy * 1.1 * 1.13 + 0.5) END AS
Sell FROM Structured_PowerMove
CREATE VIEW Calculated_Simms AS
SELECT rowid, Category, Category2, Category3, Code,
Products.Description AS Description, Detail, CASE WHEN Image LIKE '%
CPP00869.JPG' OR Image LIKE '%CPP00011.JPG' OR Image LIKE '%
CPP00014.JPG' OR Image LIKE '%simmsnoimage.jpg' THEN NULL ELSE
'http://www.simms.com.au/direct/' || Image END AS Image,
Manufacturer, Warranty, Buy, RRP, Stock, Products.Modified AS
Modified, 'Simms' AS Supplier, CASE WHEN RRP NOTNULL AND RRP > 0 AND
Round(Buy * 1.1 * (1 + Markup) + 0.5) > RRP THEN Round((Buy * 1.1 * 2
+ RRP * 8) / 10) ELSE Round(Buy * 1.1 * (1 + Markup) + 0.5) END AS
Sell FROM Structured_Simms AS Products JOIN Suppliers ON
Suppliers.Name='Simms'
CREATE VIEW Desummarized_Amicroe AS
SELECT rowid, 'RAM' AS Category, (SELECT B.Code FROM
Supplier_Amicroe_RAM AS B WHERE B.rowid = (SELECT Max(C.rowid) FROM
Supplier_Amicroe_RAM AS C WHERE C.Code IS NOT NULL AND C.Code NOT
LIKE 'Reseller%' AND C.Description IS NULL AND C.rowid < A.rowid)) AS
Category2, A.Code AS Code, A.Description AS Description, A.ExTax AS
Buy, Modified FROM Supplier_Amicroe_RAM AS A WHERE A.Description IS
NOT NULL
CREATE VIEW Structured_Amicroe AS
SELECT rowid, Category, CASE WHEN Category2 LIKE 'G_ PowerBook %'
THEN 'PowerBook ' || substr( Category2, 1, 2 ) WHEN Category2 LIKE
'MacBook Pro %' THEN 'MacBook Pro' WHEN Category2 LIKE 'Mac Mini %'
THEN 'Mac Mini' WHEN Category2 LIKE 'PowerMac G_ %' THEN 'PowerMac'
|| substr( Category2, 9, 3 ) WHEN Category2 LIKE 'eMac %' THEN 'eMac'
WHEN Category2 LIKE 'iMac%' THEN 'iMac' WHEN Category2 LIKE 'iBook %'
THEN 'iBook' WHEN Category2 LIKE 'PowerBook G4 %' OR Category2 LIKE
'PowerBook G4 %' THEN 'PowerBook G4' WHEN Category2 LIKE 'xServe %'
THEN 'xServe' ELSE Category2 END AS Category2, CASE WHEN Category2
LIKE 'G_ PowerBook%' THEN substr( Category2, 14, 99 ) WHEN Category2
LIKE 'MacBook Pro %' THEN substr( Category2, 13, 99 ) WHEN Category2
LIKE 'Mac Mini %' THEN substr( Category2, 10, 99 ) WHEN Category2
LIKE 'PowerMac G_ %' THEN substr( Category2, 13, 99 ) WHEN Category2
LIKE 'eMac %' THEN substr( Category2, 6, 99 ) WHEN Category2 LIKE
'iMac %' THEN substr( Category2, 6, 99 ) WHEN Category2 LIKE 'iMac-
%' THEN substr( Category2, 7, 99 ) WHEN Category2 LIKE 'iBook %'
THEN substr( Category2, 8, 99 ) WHEN Category2 LIKE 'iBook %' THEN
substr( Category2, 7, 99 ) WHEN Category2 LIKE 'PowerBook G4 %' THEN
substr( Category2, 14, 99 ) WHEN Category2 LIKE 'PowerBook G4 %'
THEN substr( Category2, 15, 99 ) WHEN Category2 LIKE 'xServe %' THEN
substr( Category2, 8, 99 ) ELSE NULL END AS Category3, Code,
Description, Buy, Modified FROM Desummarized_Amicroe
CREATE VIEW Structured_Apple AS
SELECT rowid, Category, Category2, Category3, [Our Part] AS Code,
Description, Detail, Image, Vendor AS Manufacturer, CASE Warranty
WHEN 'N/A' THEN NULL ELSE Warranty END AS Warranty, [Price Ex GST] AS
Buy, [RRP Inc GST] AS RRP, [Stock Brisbane] + [Stock Sydney] + [Stock
Melbourne] AS Stock, Modified FROM Supplier_Simms WHERE Vendor LIKE
'Apple'
CREATE VIEW Structured_BlueChipIT AS
SELECT rowid, Category, [Group] AS Category2, Part AS Code,
Description, Details AS Detail, Manufacturer, Cost AS Buy, Qty AS
Stock, StockOnOrderQty AS Ordered, StockOnOrderDueDate AS Due,
Modified FROM Supplier_BlueChipIT
CREATE VIEW Structured_MacSense AS
SELECT rowid, Category, CASE WHEN Category2 IS NULL AND Category
NOT LIKE Section THEN Section ELSE Category2 END AS Category2, CASE
WHEN Category2 NOT NULL AND Category3 IS NULL AND Category2 NOT LIKE
Section THEN Section ELSE Category3 END AS Category3, Code, Title AS
Description, Description || CASE WHEN Features IS NULL THEN '' ELSE '
<br />
<br />
' || Features END AS Detail, Image, Supplier AS Manufacturer, CASE
WHEN [Manufacturer Website] LIKE 'www.%' THEN 'http://' ||
[Manufacturer Website] ELSE [Manufacturer Website] END AS URL, Buy,
RRP, Compatibility, Modified FROM Supplier_MacSense
CREATE VIEW Structured_PowerMove AS
SELECT rowid, Category, [Group] AS Category2, Code,
DescriptionNoLink AS Description, ExtendedDescription AS Detail,
Price AS Buy, RecommendedRetailPrice AS RRP, AvailableQuantity AS
Stock, Modified FROM Supplier_PowerMove_Web
CREATE VIEW Structured_Simms AS
SELECT rowid, Category, CASE Category2 WHEN 'ValueRAM' THEN
Category3 ELSE Category2 END AS Category2, CASE Category2 WHEN
'ValueRAM' THEN Category4 ELSE Category3 END AS Category3, [Our Part]
AS Code, Description, Detail, Image, Vendor AS Manufacturer, CASE
Warranty WHEN 'N/A' THEN NULL ELSE Warranty END AS Warranty, [Price
Ex GST] AS Buy, [RRP Inc GST] AS RRP, [Stock Brisbane] + [Stock
Sydney] + [Stock Melbourne] AS Stock, Modified FROM Supplier_Simms
WHERE NOT (Vendor LIKE 'Apple')
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
