Why SQL Sucks

I have a site where I have to maintain an SQL database along side an
MV one. I recently got a support call because the client wasn't
getting the expected results when comparing some data between the two
systems. There was no actual problem with the data, just the way they
were looking at it.

The point is, in order for me to verify the data between the two
systems, I had write some queries of my own. Below is one of the SQL
queries, followed by what was needed for MV.

SELECT
category_minors.major_id
SUM(invoice_detail.qty_ship * product_master.pieces ) AS TotQty,

FROM
invoice_master
INNER JOIN invoice_detail ON
(invoice_master.invoice_id=invoice_detail.invoice_id)
INNER JOIN product_skus ON
(invoice_detail.sku_id=product_skus.sku_id)
INNER JOIN product_master ON
(product_skus.prod_id=product_master.prod_id)
INNER JOIN category_codes ON
(product_master.category_id=category_codes.category_id)
INNER JOIN category_minors ON
(category_codes.minor_id=category_minors.minor_id)
INNER JOIN category_majors ON
(category_minors.major_id=category_majors.major_id)

WHERE
(invoice_master.fiscal_year = 2008) AND
(invoice_master.fiscal_period = 4) AND
(category_minors.major_id = 'M10')

GROUP BY
category_minors.major_id

Here is what was required from the MV system to get the same result.

SORT INVOICES WITH F.YEAR = "2008" AND WITH F.PERIOD = "04" BY-EXP
MAJOR.ID = "M10" TOTAL PCS.QTY DET-SUPP

I do realize that if we were dealing with red and blue cars, SQL would
really shine. ;-)

--
Kevin Powick
.

Relevant Pages

  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: 2 crosstabs
    ... I have no problem using one query as the source for a line graph. ... problems comes when I want to combine both queries into one line graph. ... sense of it to generate my own SQL: ... Orders INNER JOIN (Products INNER JOIN [Order ...
    (microsoft.public.access.queries)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL Query ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... Can you provide a complete sample app ... selects with a single inner join perforn OK. ... When I run the query from SQL ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: sysindexes
    ... collation, but you should be able to use it for your column change as well. ... SQL Server MVP ... DECLARE @sql VARCHAR ... INNER JOIN information_schema.table_constraints tc ...
    (microsoft.public.sqlserver.server)