Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
jpraveen
Participant
Posts: 71 Joined: Sat Jun 06, 2009 7:10 am
Location: HYD
Post
by jpraveen » Tue Oct 05, 2010 1:08 am
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?
Jaypee
wernerg_at
Premium Member
Posts: 30 Joined: Thu Jan 14, 2010 5:46 am
Location: Austria
Post
by wernerg_at » Tue Oct 05, 2010 1:29 am
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
jpraveen
Participant
Posts: 71 Joined: Sat Jun 06, 2009 7:10 am
Location: HYD
Post
by jpraveen » Tue Oct 05, 2010 1:36 am
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
Jaypee
wernerg_at
Premium Member
Posts: 30 Joined: Thu Jan 14, 2010 5:46 am
Location: Austria
Post
by wernerg_at » Tue Oct 05, 2010 1:46 am
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 >
ArndW
Participant
Posts: 16318 Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:
Post
by ArndW » Tue Oct 05, 2010 2:01 am
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)?
jpraveen
Participant
Posts: 71 Joined: Sat Jun 06, 2009 7:10 am
Location: HYD
Post
by jpraveen » Tue Oct 05, 2010 2:43 am
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
Jaypee
wernerg_at
Premium Member
Posts: 30 Joined: Thu Jan 14, 2010 5:46 am
Location: Austria
Post
by wernerg_at » Tue Oct 05, 2010 2:55 am
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))
wernerg_at
Premium Member
Posts: 30 Joined: Thu Jan 14, 2010 5:46 am
Location: Austria
Post
by wernerg_at » Tue Oct 05, 2010 3:03 am
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
...
HariK
Participant
Posts: 68 Joined: Thu May 17, 2007 1:50 am
Post
by HariK » Thu Oct 07, 2010 12:47 am
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.
wernerg_at
Premium Member
Posts: 30 Joined: Thu Jan 14, 2010 5:46 am
Location: Austria
Post
by wernerg_at » Thu Oct 07, 2010 1:06 am
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.
truenorth
Participant
Posts: 139 Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio
Post
by truenorth » Thu Oct 07, 2010 10:12 pm
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?
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Thu Oct 07, 2010 10:19 pm
I wonder if it's the "#" character ("not equal to")?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.