sql query problem

[paulw1982]

Hiya,

I have used the following query to extract certain sales data from an sql database.

The problem is that my WHERE statement must be incorrect because the records that are returned have an estimated close date of before and after the date I have specified.

If anybody can tell me where i'm going wrong I would be most grateful.

Here is the statement i'm using at the moment:-

SELECT USERINFO.USERNAME, OPPORTUNITY.DESCRIPTION, OPPORTUNITY.SALESPOTENTIAL, OPPORTUNITY.CLOSEPROBABILITY, OPPORTUNITY.ESTIMATEDCLOSE, PY3_OPPORTUNITY.EST_ORDER, PY3_OPPORTUNITY.MARGIN, ACCOUNT.ACCOUNT, USERINFO.DIVISION
FROM { oj ((Saleslogix.sysdba.OPPORTUNITY OPPORTUNITY LEFT OUTER JOIN Saleslogix.sysdba.ACCOUNT ACCOUNT ON OPPORTUNITY.ACCOUNTID = ACCOUNT.ACCOUNTID) LEFT OUTER JOIN Saleslogix.sysdba.USERINFO USERINFO ON OPPORTUNITY.ACCOUNTMANAGERID = USERINFO.USERID) LEFT OUTER JOIN Saleslogix.sysdba.PY3_OPPORTUNITY PY3_OPPORTUNITY ON OPPORTUNITY.OPPORTUNITYID = PY3_OPPORTUNITY.OPPORTUNITYID}
WHERE userinfo.username = '&quot;+Request.Form(&quot;sman&quot+&quot;' AND (OPPORTUNITY.CLOSEPROBABILITY >= 10 AND OPPORTUNITY.CLOSEPROBABILITY <= 90) AND OPPORTUNITY.ESTIMATEDCLOSE >= '01/10/2003'
ORDER BY OPPORTUNITY.ESTIMATEDCLOSE ASC


Thank you

 

[Cheech]

SELECT USERINFO.USERNAME, OPPORTUNITY.DESCRIPTION, OPPORTUNITY.SALESPOTENTIAL, OPPORTUNITY.CLOSEPROBABILITY, OPPORTUNITY.ESTIMATEDCLOSE, PY3_OPPORTUNITY.EST_ORDER, PY3_OPPORTUNITY.MARGIN, ACCOUNT.ACCOUNT, USERINFO.DIVISION
FROM { oj ((Saleslogix.sysdba.OPPORTUNITY OPPORTUNITY LEFT OUTER JOIN Saleslogix.sysdba.ACCOUNT ACCOUNT ON OPPORTUNITY.ACCOUNTID = ACCOUNT.ACCOUNTID) LEFT OUTER JOIN Saleslogix.sysdba.USERINFO USERINFO ON OPPORTUNITY.ACCOUNTMANAGERID = USERINFO.USERID) LEFT OUTER JOIN Saleslogix.sysdba.PY3_OPPORTUNITY PY3_OPPORTUNITY ON OPPORTUNITY.OPPORTUNITYID = PY3_OPPORTUNITY.OPPORTUNITYID}
WHERE userinfo.username = '&quot;+Request.Form(&quot;sman&quot+&quot;' AND (OPPORTUNITY.CLOSEPROBABILITY >= 10 AND OPPORTUNITY.CLOSEPROBABILITY <= 90) AND OPPORTUNITY.ESTIMATEDCLOSE > '30/09/2003'
ORDER BY OPPORTUNITY.ESTIMATEDCLOSE ASC



[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net

 

[paulw1982]

Its coming back with this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

 

[vbkris]

maybe because of this:
AND (OPPORTUNITY.CLOSEPROBABILITY >= 10 AND OPPORTUNITY.CLOSEPROBABILITY <= 90) AND OPPORTUNITY.ESTIMATEDCLOSE >= '01/10/2003'


cant tell more without more info...

Known is handfull, Unknown is worldfull

 

[paulw1982]

I have tried taking that section out so that the date selection part is the only part of the where statement but it begaves in the same way.

is there a command that can be put directly into the query to tell it to convert the variable into a date format?

 

[paulw1982]

I have tried taking that section out so that the date selection part is the only part of the where statement but it behaves in the same way.

is there a command that can be put directly into the query to tell it to convert the variable into a date format?

 

[vbkris]

er,
OPPORTUNITY.ESTIMATEDCLOSE >= '01/10/2003'

must be:
OPPORTUNITY.ESTIMATEDCLOSE = '01/10/2003'

Known is handfull, Unknown is worldfull

 

[paulw1982]

If I do that then I will only see the records that are exactly that date. I need to see all records on AND after that date.

I've tried looking on the web. Do you know anything about a CONVERT command?

 

[vbkris]

well try a simple statement with a greater than sign(one table only) does it work???

Known is handfull, Unknown is worldfull

 

[paulw1982]

I've worked it out

Just incase anyone is interested how its like this...

SELECT USERINFO.USERNAME, OPPORTUNITY.DESCRIPTION, OPPORTUNITY.SALESPOTENTIAL, OPPORTUNITY.CLOSEPROBABILITY, OPPORTUNITY.ESTIMATEDCLOSE, PY3_OPPORTUNITY.EST_ORDER, PY3_OPPORTUNITY.MARGIN, ACCOUNT.ACCOUNT, USERINFO.DIVISION
FROM { oj ((Saleslogix.sysdba.OPPORTUNITY OPPORTUNITY LEFT OUTER JOIN Saleslogix.sysdba.ACCOUNT ACCOUNT ON OPPORTUNITY.ACCOUNTID = ACCOUNT.ACCOUNTID) LEFT OUTER JOIN Saleslogix.sysdba.USERINFO USERINFO ON OPPORTUNITY.ACCOUNTMANAGERID = USERINFO.USERID) LEFT OUTER JOIN Saleslogix.sysdba.PY3_OPPORTUNITY PY3_OPPORTUNITY ON OPPORTUNITY.OPPORTUNITYID = PY3_OPPORTUNITY.OPPORTUNITYID}
WHERE userinfo.username = '&quot;+Request.Form(&quot;sman&quot+&quot;' AND (OPPORTUNITY.CLOSEPROBABILITY >= 10 AND OPPORTUNITY.CLOSEPROBABILITY <= 90) AND OPPORTUNITY.ESTIMATEDCLOSE >= CONVERT(smalldatetime,'30/09/2003', 103)
ORDER BY OPPORTUNITY.ESTIMATEDCLOSE ASC

Thanks for your help vbkris