My query doesn't work, urgent help needed please

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)

My query doesn't work, urgent help needed please

Author

Topic

shahzeb
Starting Member

6 Posts

Posted - 2008-06-21 : 11:04:58

Hi Guys, i am working on an application which is supposed to give me back "todays calls and emails", i have a query for this (not my own), and for some reason it just does'nt work and shows these errors

"Server: Msg 195, Level 15, State 10, Line 5
'TO_CHAR' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 8
'to_date' is not a recognized function name."

i am not very good at this so can any one help me, do u know what's going wrong, do u know why it says that it doesn't recognize to_date and TO_CHAR functions." The query is below. thanx in advance guys...

SELECT * FROM
(SELECT Id, ContactId, StartDate, TypeId, MediaTypeId, CAST('' AS VARCHAR2(64)) AS ListName
FROM interaction
WHERE (MediaTypeId = 'voice' OR MediaTypeId = 'email')
AND (TO_CHAR(StartDate, 'YYYY/MM/DD') = TO_CHAR(sysdate, 'YYYY/MM/DD'))
AND (OwnerId = '806')
UNION
SELECT CAST('' AS VARCHAR2(16)) AS Id, CONTACT.ID as ContactId, to_date('01-JAN-1970 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + ((time_stamp) / 86400) AS StartDate, CAST('Outbound Campaign' AS NVARCHAR2(32)) AS TypeId, CAST('Voice' AS NVARCHAR2(32)) AS MediaTypeId, LIST_NAME AS ListName
FROM OUTBOUND_LOG, CONTACT
WHERE OUTBOUND_LOG.PHONE = '' || CONTACT.@@PhoneNumber
AND to_char(to_date('01-JAN-1970 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + ((time_stamp) / 86400)) = to_char(to_date(sysdate, 'dd/mm/yyyy hh24:mi:ss'))
AND AGENT_ID = 7001
AND REQUEST_TYPE = 9)
ORDER BY StartDate DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:21:53

TO_CHAR function is an ORACLE function.
Are you really using MICROSOFT SQL Server 2000?

Or are you just translating a query from ORACLE to Microsoft SQL Server and don't bother to do it yourself?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:27:45

[code]SELECT Id,
ContactId,
StartDate,
TypeId,
MediaTypeId,
CAST('' AS NVARCHAR(64)) AS ListName
FROM interaction
WHERE MediaTypeId IN ('voice', 'email')
AND DATEDIFF(DAY, StartDate, getdate()) = 0
AND OwnerId = '806'

UNION ALL

SELECT CAST('' AS NVARCHAR(16)) AS Id,
CONTACT.ID as ContactId,
DATEADD(SECOND, time_stamp, '19700101') AS StartDate,
N'Outbound Campaign' AS TypeId,
N'Voice' AS MediaTypeId,
LIST_NAME AS ListName
FROM OUTBOUND_LOG
INNER JOIN CONTACT ON OUTBOUND_LOG.PHONE = COALESCE(CONTACT.PhoneNumber, '')
WHERE DATEDIFF(DAY, DATEADD(SECOND, time_stamp, '19700101'), getdate()) = 0
AND AGENT_ID = 7001
AND REQUEST_TYPE = 9

ORDER BY 3 DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

shahzeb
Starting Member

6 Posts

Posted - 2008-06-21 : 11:29:13

Well as i said its not my own query and i am not very good in writting queries so i didnt even knew that those are oracle functions. Could you please tell me how can i write the same query for SQL so that it works. which functions to use for SQL. it will be best if u just change the parts which need to be changed and past the entire working query for me... i would be really grateful of your. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 11:43:54

The query have never worked on Microsoft SQL Server, so you are not telling us the truth here.

E 12°55'05.25"
N 56°04'39.16"

shahzeb
Starting Member

6 Posts

Posted - 2008-06-21 : 12:02:11

Well my friend i am saying the truth. I am running the query on SQL server and it doesn't work, and you told me the reason why it was not working, since the functions being used are not SQL server functions, so it will never work. The other query u gave me, i tried to execute that... the syntax seemed fine, but it gave a problem with one of the tables, which i dont know why either... so now i will contact the guy who has originally written this query... thanx for the help... and why would i lie :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-21 : 13:41:39

quote:
Originally posted by shahzeb

Well my friend i am saying the truth. I am running the query on SQL server and it doesn't work, and you told me the reason why it was not working, since the functions being used are not SQL server functions, so it will never work. The other query u gave me, i tried to execute that... the syntax seemed fine, but it gave a problem with one of the tables, which i dont know why either... so now i will contact the guy who has originally written this query... thanx for the help... and why would i lie :)

What was the error you got with the solution provided?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-21 : 16:11:19

Maybe column CONTACT.PhoneNumber need to be written as CONTACT.[@@PhoneNumber]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources