DSXchange

Hi all

The below query when executed in the database, i am getting the error:

Incorrect syntax near 'Sun'

select Distinct
ProviderNumber,substring(printerexception,1,50) as PrinterException
from
providerpmg,AZ_locations

where
(PrinterException like 'Mon. - Fri.: 8:00 a.m. - 8:00 p.m.; Sat. - Sun.: 8:00 a.m. - 4:00 p.m.'
or PrinterException like 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m. or
PrinterException like 'Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)' )

the error is in 'Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)'

the data type of PrinterException is Text

so can anyone suggest how to rectify the error?

Hi,

I think a closing quote is missing after 10:00 p.m.

Try changing

or PrinterException like 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m. or

To

or PrinterException like 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m.' or

BR

Werner

HI Werner

i had tried with all the options like removing Closed bracket ')'

but in the database the value is

Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)

and the field is text

and if i remove substring
it is showing error like

Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
but i want only distinct records

Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
but i want only distinct records

Considering this a SQL Server DBMS you need to cast your ntext column to nvarchar:

Select distinct with ntext fields
Pasted from <http://social.msdn.microsoft.com/forums ... 930e288ad5>

Could the error be something as banal as the single "#" character being misinterpreted? Does the error go away when you remove the "#" (even if no rows are returned)?

hi

though i change into

Cast(PrinterException as NVarchar(50))

i am getting the same error

Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)

the above quote is a text value and field name is PrinterException

it doesn't matter i remove # or ph # 623-977-7211

Did you cast the fields in the where clause as well. if not please try it.

Note:
(NTEXT/TEXT wont allow any string manipulation (including comparision))

FYI:
Since ntext, text, and image data types marked as deprecated you should try to use different types or consider changing them at the next opportunity

For further details refer to the SQL Server 2008 R2 Documentation

ntext, text, and image (Transact-SQL)
Pasted from <http://msdn.microsoft.com/en-US/library/ms187993.aspx>

...
Important

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them
...

select Distinct
ProviderNumber,substring(printerexception,1,50) as PrinterException
from
providerpmg,AZ_locations

where
(PrinterException = 'Mon. - Fri.: 8:00 a.m. - 8:00 p.m.; Sat. - Sun.: 8:00 a.m. - 4:00 p.m.'
or PrinterException = 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m.' or
PrinterException = 'Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)' )

Could you try this. LIKE will work but is not required in your case as you are not using any wild characters.

HariK wrote:

select Distinct
ProviderNumber,substring(printerexception,1,50) as PrinterException
from
providerpmg,AZ_locations

where
(PrinterException = 'Mon. - Fri.: 8:00 a.m. - 8:00 p.m.; Sat. - Sun.: 8:00 a.m. - 4:00 p.m.'
or PrinterException = 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m.' or
PrinterException = 'Urgent Care (Sun. - Sat.: 12:00p.m. - 10:00 p.m.) ph # 623-977-7211)' )

Could you try this. LIKE will work but is not required in your case as you are not using any wild characters.

As already mentioned ntext/text fields neither support distinct nor string comparison (not even a '=' comparison).

I still believe that casting all the text/ntext fields (within select and where clauses) should solve this issue.

I agree with this diagnosis:

wernerg_at wrote:Hi,

I think a closing quote is missing after 10:00 p.m.

Try changing

or PrinterException like 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m. or

To

or PrinterException like 'Sun.-Sat. 9:00 a.m.. - 10:00 p.m.' or

BR

Werner

Have you looked into it?

I wonder if it's the "#" character ("not equal to")?