QUERY - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

QUERY

Author

Topic

MonkeyHanger
Starting Member

5 Posts

Posted - 2007-03-22 : 04:32:22

SearchString = "SELECT RopeMaterial, RopeOD, Max(RopeRevision) AS MaxRevision" & _
" FROM MaterialRopeFillerSizes" & _
" WHERE (((Approved)=1) AND (([RopeOD] And [RopeMaterial]) Not In (select [RopeOD]=" & FindKeyLeft(Mid(Me.RopeDiaSelect, (Len(Me.filler_mat) + 10)), "m") & " and [RopeMaterial]='" & filler_mat & "' from ropefillersizes where approved=1 and archive=1)) AND ((RopeMaterial)='" & filler_mat & "') AND ((RopeOD)=" & FindKeyLeft(Mid(Me.RopeDiaSelect, (Len(Me.filler_mat) + 10)), "m") & "))" & _
" GROUP BY RopeMaterial, RopeOD;"

Debuggin gives this search string:

SELECT RopeMaterial, RopeOD, Max(RopeRevision) AS MaxRevision
FROM MaterialRopeFillerSizes
WHERE (((Approved)=1) AND (([RopeOD] And [RopeMaterial]) Not In (select [RopeOD]=6 and [RopeMaterial]='Polypropylene' from ropefillersizes where approved=1 and archive=1)) AND ((RopeMaterial)='Polypropylene') AND ((RopeOD)=6)) GROUP BY RopeMaterial, RopeOD;

I am getting the error:

Incorrect syntax near the keyword 'And'

Can anyone help?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 04:38:45

[code]SELECT
m.RopeMaterial, m.RopeOD, Max(m.RopeRevision) AS MaxRevision
FROM
MaterialRopeFillerSizes m LEFT JOIN ropefillersizes r
On
m.RopeOD = r.RopeOD and m.approved = r.approved and r.archive=1
and m.RopeMaterial = r.RopeMaterial
WHERE
m.Approved=1 AND
r.RopeOD IS NULL And
r.RopeMaterial IS NULL and
m.RopeMaterial = 'Polypropylene'
AND m.RopeOD=6
GROUP BY
m.RopeMaterial, m.RopeOD[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

MonkeyHanger
Starting Member

5 Posts

Posted - 2007-03-22 : 05:25:16

Thanks for the response.

This code seems to work fine for this particular query.

I am however, struggling to convert this code back into the generic search string. Any further help would be much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources