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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help on Select Statement

Author  Topic 

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-07 : 09:34:23
Hi,

I am trying to run the following select Statement. However Qry Analyser reports an Error :-

SqL Server database error : Line No 44 Incorrect Syntax near '>'

The statement looks something like so :-

SELECT a.materialgroup, a.mtrdoctype, a.mtrno, a.priority, e.codevaluedesc as prior, f.codevaluedesc as mtrtype, a.receiveddate, a.consigneecode as consignee, a.regdate, a.mtrapproveddate as appdate, a.parentdocno as parentdoc,
g.codevaluedesc as mtrstat, b.itemcode, b.vocab, b.partno,
Upper(c.description) as descript, b.doq, ISNULL(b.demandqty, 0) as demandqty, isnull(b.totalissueqty, 0) as totissued, b.ismtitemclosed as itemclosed, (isnull(stk.catastk, 0) + isnull(stk.catbstk, 0)) as serstk, isnull(stk.catdstk, 0) as repstk, isnull(stk.catcstk, 0) as catcstk, j.fieldname, 'Remarks' = Case When j.fieldvalue is null Then 'No Remarks' Else Ltrim(Rtrim(j.fieldvalue)) End, suppart.editpartno, 'Storecode' = Case When stk.Storecode Is Null Then 'NTC' Else Upper(stk.Storecode) End, 'Aircraftno' = Case When a.aircraftno Is Null Then 'N/A' Else Upper(a.aircraftno) End
FROM Tbmtmtr a (NOLOCK)
INNER JOIN Tbmtmtritems b (NOLOCK) ON b.MTRNo = a.MTRNo
INNER JOIN tbGLItem_M c (NOLOCK) ON b.ItemCode = c.ItemCode
iNNER JOIN tbglcodevalues e (NOLOCK) ON a.priority = e.Codevalue
INNER JOIN tbglcodevalues f (NOLOCK) ON a.requesttype = f.Codevalue
INNER JOIN tbglcodevalues g (NOLOCK) ON a.mtrstatus = g.Codevalue
inner join tbglSupplierParts_m suppart (NOLOCK)
on b.itemcode = suppart.itemcode
left join tbmtmtritemsmodifyhistory j (NOLOCK)
on b.mtrno = j.mtrno and b.itemcode = j.itemcode
and j.amendmentno = (select max(amendmentno) from
tbmtmtritemsmodifyhistory k (NOLOCK) where k.mtrno = b.mtrno and
k.itemcode = b.itemcode AND J.FIELDNAME = 'Consignor Remarks')
left join
(SELECT A.ITEMCODE, a.storecode, (SELECT ISNULL(SUM(QTY), 0) FROM tBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4401) AS CATASTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4402) AS CATBSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4403) AS CATCSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4404) AS CATDSTK,
SUM(QTY) AS TOTALSTK
FROM TBSPSTORESTOCKSTATUS A (nolock)
WHERE upper(a.storecode) = ' SITE4 '
GROUP BY a.itemcode, a.storecode) Stk
on b.itemcode = stk.Itemcode
where a.priority in (2903) AND
g.codevaluedesc = 'Incoming' and b.ismtitemclosed != 'Y' and a.consignorcode = '26ED' and
suppart.Isdefaultpartno = 'Y' and
Case
When a.receiveddate is null
Then Convert(Varchar(12), a.senioritydate, 105) >= '01-01-1999' -- Error
Else Convert(Varchar(12), a.receiveddate, 105) >= '01-01-1999'
End
and
Case
When a.receiveddate is null
Then Convert(Varchar(12), a.senioritydate, 105) <= '01-01-2009' -- Error
Else Convert(Varchar(12), a.receiveddate, 105) <= '01-01-2009'
End
and
(Upper(stk.Storecode) = 'SITE4' or stk.Storecode is null)
order by b.vocab, suppart.editpartno,
Case When a.receiveddate is null Then a.Senioritydate Else a.Receiveddate End


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:47:10
it should be as below:-

SELECT a.materialgroup, a.mtrdoctype, a.mtrno, a.priority, e.codevaluedesc as prior, f.codevaluedesc as mtrtype, a.receiveddate, a.consigneecode as consignee, a.regdate, a.mtrapproveddate as appdate, a.parentdocno as parentdoc, 
g.codevaluedesc as mtrstat, b.itemcode, b.vocab, b.partno,
Upper(c.description) as descript, b.doq, ISNULL(b.demandqty, 0) as demandqty, isnull(b.totalissueqty, 0) as totissued, b.ismtitemclosed as itemclosed, (isnull(stk.catastk, 0) + isnull(stk.catbstk, 0)) as serstk, isnull(stk.catdstk, 0) as repstk, isnull(stk.catcstk, 0) as catcstk, j.fieldname, 'Remarks' = Case When j.fieldvalue is null Then 'No Remarks' Else Ltrim(Rtrim(j.fieldvalue)) End, suppart.editpartno, 'Storecode' = Case When stk.Storecode Is Null Then 'NTC' Else Upper(stk.Storecode) End, 'Aircraftno' = Case When a.aircraftno Is Null Then 'N/A' Else Upper(a.aircraftno) End
FROM Tbmtmtr a (NOLOCK)
INNER JOIN Tbmtmtritems b (NOLOCK) ON b.MTRNo = a.MTRNo
INNER JOIN tbGLItem_M c (NOLOCK) ON b.ItemCode = c.ItemCode
iNNER JOIN tbglcodevalues e (NOLOCK) ON a.priority = e.Codevalue
INNER JOIN tbglcodevalues f (NOLOCK) ON a.requesttype = f.Codevalue
INNER JOIN tbglcodevalues g (NOLOCK) ON a.mtrstatus = g.Codevalue
inner join tbglSupplierParts_m suppart (NOLOCK)
on b.itemcode = suppart.itemcode
left join tbmtmtritemsmodifyhistory j (NOLOCK)
on b.mtrno = j.mtrno and b.itemcode = j.itemcode
and j.amendmentno = (select max(amendmentno) from
tbmtmtritemsmodifyhistory k (NOLOCK) where k.mtrno = b.mtrno and
k.itemcode = b.itemcode AND J.FIELDNAME = 'Consignor Remarks')
left join
(SELECT A.ITEMCODE, a.storecode, (SELECT ISNULL(SUM(QTY), 0) FROM tBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4401) AS CATASTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4402) AS CATBSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4403) AS CATCSTK,
(SELECT ISNULL(SUM(QTY), 0) FROM TBSPSTORESTOCKSTATUS (NOLOCK)
WHERE ITEMCODE = A.ITEMCODE AND ITEMCAT = 4404) AS CATDSTK,
SUM(QTY) AS TOTALSTK
FROM TBSPSTORESTOCKSTATUS A (nolock)
WHERE upper(a.storecode) = ' SITE4 '
GROUP BY a.itemcode, a.storecode) Stk
on b.itemcode = stk.Itemcode
where a.priority in (2903) AND
g.codevaluedesc = 'Incoming' and b.ismtitemclosed != 'Y' and a.consignorcode = '26ED' and
suppart.Isdefaultpartno = 'Y' and
Case
When a.receiveddate is null
Then Convert(Varchar(12), a.senioritydate, 105)
Else Convert(Varchar(12), a.receiveddate, 105)
End>= '01-01-1999'
and
Case
When a.receiveddate is null
Then Convert(Varchar(12), a.senioritydate, 105)
Else Convert(Varchar(12), a.receiveddate, 105)
End<= '01-01-2009'

and
(Upper(stk.Storecode) = 'SITE4' or stk.Storecode is null)
order by b.vocab, suppart.editpartno,
Case When a.receiveddate is null Then a.Senioritydate Else a.Receiveddate End
Go to Top of Page

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-07 : 10:24:57
Visakh16,

Bingo. Thanx a million
Go to Top of Page
   

- Advertisement -