SQL 2000 view debug help

110 pts.
Tags:
SQL 2000
SQL Server 2000
SQL Server 2000 administration
SQL Server 2000 queries
I have the following (admittedly convoluted) view that I cannot get right for some reason that I cannot see. Consider the following:

SELECT     s.s_SAMPLEID AS LimsSampleId, s.U_CLIENT_SAM_NO AS ADASampleId, MAX(s.SAMPLEDESC) AS SampleDescription,                       MAX(CASE d.paramlistid WHEN i.paramid THEN d.paramlistid WHEN '1.Purity I' THEN pm.paramdesc WHEN '2.Purity Analysis' THEN 'Purity Analysis' WHEN                        '5.Germination' THEN pm.paramdesc END) AS Analysis,                       MAX(CASE i.paramid WHEN d.paramlistid THEN p.PARAMLISTDESC WHEN 'Purity %' THEN p.PARAMLISTDESC WHEN 'Inert %' THEN 'Purity Analysis' WHEN                        'Other crop %' THEN 'Purity Analysis' WHEN 'WEED %' THEN 'Purity Analysis' WHEN 'Germination %' THEN p.PARAMLISTDESC END)                       AS AnalysisDescription,                       MAX(CASE i.paramid WHEN d.paramlistid THEN i.EnteredText WHEN 'Purity %' THEN i.EnteredText WHEN 'Germination %' THEN i.EnteredText ELSE NULL                        END) AS Result,                       MAX(CASE i.paramid WHEN d.paramlistid THEN i.DisplayUnits WHEN 'Purity %' THEN i.EnteredUnits WHEN 'Germination %' THEN i.EnteredUnits ELSE                        NULL END) AS Units, MAX(CASE WHEN i.paramid = 'Maximum Guarantee' THEN i.EnteredText ELSE NULL END) AS [Max],                       MAX(CASE WHEN i.paramid = 'Minimum Guarantee' THEN i.EnteredText ELSE NULL END) AS [Min],                       MAX(CASE WHEN i.paramid = 'Violation' THEN i.EnteredText ELSE NULL END) AS Violation,                       MAX(CASE d.paramlistid WHEN '2.Purity Analysis' THEN NULL ELSE b.U_COMMON_NAME END) AS CropName,                       MAX(CASE d.paramlistid WHEN '2.Purity Analysis' THEN NULL ELSE b.U_GENUS END) AS Genus,                       MAX(CASE d.paramlistid WHEN '2.Purity Analysis' THEN NULL ELSE b.U_SPECIES END) AS Species,                       MAX(CASE WHEN i.paramid = 'Other crop %' THEN i.EnteredText ELSE NULL END) AS OtherCrop,                       MAX(CASE WHEN i.paramid = 'Inert %' THEN i.EnteredText ELSE NULL END) AS Inert,                       MAX(CASE WHEN i.paramid = 'WEED %' THEN i.EnteredText ELSE NULL END) AS Weed,                       MAX(CASE i.paramid WHEN 'Date of Analysis' THEN CONVERT(DateTime, i.EnteredText)                       WHEN 'Purity %' THEN ic.EnteredText WHEN 'Germination %' THEN ic.EnteredText ELSE NULL END) AS DateOfAnalysis,                       MAX(CASE d.paramlistid WHEN '2.Purity Analysis' THEN NULL ELSE Floor(CAST(U_COLLECTDT AS Decimal(20, 10))) END) AS DateCollected,                       MAX(CASE d.paramlistid WHEN '2.Purity Analysis' THEN 99 ELSE 0 END) AS SortOrder01,                       MAX(CASE d.paramlistid WHEN i.paramid THEN '0' WHEN '1.Purity I' THEN ib.displayvalue WHEN '2.Purity Analysis' THEN 'Z' WHEN '5.Germination' THEN                        ib.displayvalue END) AS SortOrder02 FROM         dbo.Samples s INNER JOIN                       dbo.SampleData d ON s.s_SAMPLEID = d.KEYID1 INNER JOIN                       dbo.SampleDataItems WITH (INDEX (IX_SampleDataItems_paramid)) i ON d.KEYID1 = i.KEYID1 AND d.DATASET = i.DATASET AND                       d.PARAMLISTID = i.PARAMLISTID AND d.PARAMLISTVERSIONID = i.PARAMLISTVERSIONID AND d.VARIANTID = i.VARIANTID AND                       i.PARAMID <> 'CROP ID' LEFT OUTER JOIN                       dbo.SampleDataItems WITH (INDEX (IX_SampleDataItems_paramid)) ib ON i.KEYID1 = ib.KEYID1 AND i.SDCID = ib.SDCID AND                       i.DATASET = ib.DATASET AND ib.PARAMID = 'CROP ID' LEFT OUTER JOIN                       dbo.SampleDataItems WITH (INDEX (IX_SampleDataItems_paramid)) ic ON i.KEYID1 = ic.KEYID1 AND i.SDCID = ic.SDCID AND ic.DATASET = '1.0' AND                       ic.PARAMID = 'Date of Analysis' INNER JOIN                       dbo.ParameterLists p ON d.PARAMLISTID = p.PARAMLISTID AND d.PARAMLISTVERSIONID = p.PARAMLISTVERSIONID AND                       d.VARIANTID = p.VARIANTID INNER JOIN                       dbo.Parameters pm ON i.PARAMID = pm.PARAMID LEFT OUTER JOIN                       dbo.Botany b ON b.U_BOTANYID = ib.DISPLAYVALUE WHERE     (i.PARAMID IN ('Violation', 'Minimum Guarantee', 'Maximum Guarantee', 'Date of Analysis', 'Purity %', 'Inert %', 'WEED %', 'Other crop %',                       'Germination %') OR                       i.PARAMID = i.PARAMLISTID) AND (i.PARAMLISTID <> '3.Purity Analysts') GROUP BY s.U_CLIENT_SAM_NO, s.s_SAMPLEID, ib.DISPLAYVALUE, d.PARAMLISTID

SQL generates the following error:

Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near 'i'.

 

Anyone have ideas? What am I not seeing?



Software/Hardware used:
SQL 2000
ASKED: January 28, 2011  6:23 PM
UPDATED: February 1, 2011  2:57 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Reposted using the code tool. italics indicate line 27 in my Query Analyser

<pre>
SELECT s.s_SAMPLEID AS ‘LimsSampleId’, s.U_CLIENT_SAM_NO AS ‘ADASampleId’, MAX(s.SAMPLEDESC) AS ‘SampleDescription’,
MAX(CASE d .paramlistid WHEN i.paramid THEN d .paramlistid WHEN ’1.Purity I’ THEN pm.paramdesc WHEN ’2.Purity Analysis’ THEN ‘Purity Analysis’ WHEN ’5.Germination’
THEN pm.paramdesc END) AS ‘Analysis’,
MAX(CASE i.paramid WHEN d .paramlistid THEN p.PARAMLISTDESC WHEN ‘Purity %’ THEN p.PARAMLISTDESC WHEN ‘Inert %’ THEN ‘Purity Analysis’ WHEN ‘Other crop %’
THEN ‘Purity Analysis’ WHEN ‘WEED %’ THEN ‘Purity Analysis’ WHEN ‘Germination %’ THEN p.PARAMLISTDESC END) AS ‘AnalysisDescription’,
MAX(CASE i.paramid WHEN d .paramlistid THEN i.EnteredText WHEN ‘Purity %’ THEN i.EnteredText WHEN ‘Germination %’ THEN i.EnteredText ELSE NULL END)
AS ‘Result’,
MAX(CASE i.paramid WHEN d .paramlistid THEN i.DisplayUnits WHEN ‘Purity %’ THEN i.EnteredUnits WHEN ‘Germination %’ THEN i.EnteredUnits ELSE NULL END)
AS ‘Units’, MAX(CASE WHEN i.paramid = ‘Maximum Guarantee’ THEN i.EnteredText ELSE NULL END) AS ‘Max’,
MAX(CASE WHEN i.paramid = ‘Minimum Guarantee’ THEN i.EnteredText ELSE NULL END) AS ‘Min’,
MAX(CASE WHEN i.paramid = ‘Violation’ THEN i.EnteredText ELSE NULL END) AS ‘Violation’, MAX(CASE d .paramlistid WHEN ’2.Purity Analysis’ THEN NULL
ELSE b.U_COMMON_NAME END) AS ‘CropName’, MAX(CASE d .paramlistid WHEN ’2.Purity Analysis’ THEN NULL ELSE b.U_GENUS END) AS ‘Genus’,
MAX(CASE d .paramlistid WHEN ’2.Purity Analysis’ THEN NULL ELSE b.U_SPECIES END) AS ‘Species’,
MAX(CASE WHEN i.paramid = ‘Other crop %’ THEN i.EnteredText ELSE NULL END) AS ‘OtherCrop’,
MAX(CASE WHEN i.paramid = ‘Inert %’ THEN i.EnteredText ELSE NULL END) AS ‘Inert’, MAX(CASE WHEN i.paramid = ‘WEED %’ THEN i.EnteredText ELSE NULL END)
AS ‘Weed’, MAX(CASE i.paramid WHEN ‘Date of Analysis’ THEN CONVERT(DateTime, i.EnteredText)
WHEN ‘Purity %’ THEN ic.EnteredText WHEN ‘Germination %’ THEN ic.EnteredText ELSE NULL END) AS ‘DateOfAnalysis’,
MAX(CASE d .paramlistid WHEN ’2.Purity Analysis’ THEN NULL ELSE Floor(CAST(U_COLLECTDT AS Decimal(20, 10))) END) AS ‘DateCollected’,
MAX(CASE d .paramlistid WHEN ’2.Purity Analysis’ THEN 99 ELSE 0 END) AS ‘SortOrder01′,
MAX(CASE d .paramlistid WHEN i.paramid THEN ’0′ WHEN ’1.Purity I’ THEN ib.displayvalue WHEN ’2.Purity Analysis’ THEN ‘Z’ WHEN ’5.Germination’ THEN ib.displayvalue
END) AS ‘SortOrder02′
FROM dbo.Samples AS s INNER JOIN
dbo.SampleData AS d ON s.s_SAMPLEID = d.KEYID1 INNER JOIN
<i>dbo.SampleDataItems AS i WITH (INDEX (IX_SampleDataItems_paramid)) ON d.KEYID1 = i.KEYID1 AND d.DATASET = i.DATASET AND</i> d.PARAMLISTID = i.PARAMLISTID AND d.PARAMLISTVERSIONID = i.PARAMLISTVERSIONID AND d.VARIANTID = i.VARIANTID AND
i.PARAMID <> ‘CROP ID’ LEFT OUTER JOIN
dbo.SampleDataItems AS ib WITH (INDEX (IX_SampleDataItems_paramid)) ON i.KEYID1 = ib.KEYID1 AND i.SDCID = ib.SDCID AND i.DATASET = ib.DATASET AND
ib.PARAMID = ‘CROP ID’ LEFT OUTER JOIN
dbo.SampleDataItems AS ic WITH (INDEX (IX_SampleDataItems_paramid)) ON i.KEYID1 = ic.KEYID1 AND i.SDCID = ic.SDCID AND ic.DATASET = ’1.0′ AND
ic.PARAMID = ‘Date of Analysis’ INNER JOIN
dbo.ParameterLists AS p ON d.PARAMLISTID = p.PARAMLISTID AND d.PARAMLISTVERSIONID = p.PARAMLISTVERSIONID AND d.VARIANTID = p.VARIANTID INNER JOIN
dbo.Parameters AS pm ON i.PARAMID = pm.PARAMID LEFT OUTER JOIN
dbo.Botany AS b ON b.U_BOTANYID = ib.DISPLAYVALUE
WHERE (i.PARAMID IN (‘Violation’, ‘Minimum Guarantee’, ‘Maximum Guarantee’, ‘Date of Analysis’, ‘Purity %’, ‘Inert %’, ‘WEED %’, ‘Other crop %’, ‘Germination %’) OR
i.PARAMID = i.PARAMLISTID) AND (i.PARAMLISTID <> ’3.Purity Analysts’)
GROUP BY s.U_CLIENT_SAM_NO, s.s_SAMPLEID, ib.DISPLAYVALUE, d.PARAMLISTID</pre>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I would recommend posting the query again using the editor's "code" tool, so we at least know what is in line 27.
    65,110 pointsBadges:
    report
  • Hlx
    Looks to me that you have answered you own question as the original problem was in your FROM clause where you had table alias used as well as WITH clause. Reversing the order on these will compile successfully.
    690 pointsBadges:
    report
  • carlosdl
    I think Hlx is right. I didn't see anything wrong with the final query, but didn't even look at the original one. Now that I took a look, I realize it certainly had misplaced table aliases.
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following