Query Help 2

[JasonMcConnell]

Hi I have created a crystal report in crystal XI and copied and pasted the SQL query generated by crystal into SQL plus in oracle 9

SELECT
PA_VIEW."APPLICATION_NUMBER", PA_VIEW."APPLICATION_TYPE", PA_VIEW."DEVELOPMENT_ADDRESS", PA_VIEW."DEVELOPMENT_DESCRIPTION", PA_VIEW."RECEIVED_DATE", PA_VIEW."SYS_STATUS", PA_VIEW."WARD"
FROM
"LAND"."PA_VIEW" PA_VIEW
WHERE

(PA_VIEW."SYS_STATUS" = '4' OR
PA_VIEW."SYS_STATUS" = '7')


I get the following error message: - ERROR at line 3 ORA-00911: invalid Character

Can you please tell me what is wrong the above query?

Many thanks for you help

 

[Dagon]

Syntactically, it seems fine when I try to run it. Did you have a blank line after the "where" clause ? If so, remove that before pasting it in.

 

[JasonMcConnell]

Hi Dagon,

You are correct about the blank line.

However I just realised that I posted the wrong query

it should be

SELECT
PA_VIEW."APPLICATION_NUMBER", PA_VIEW."APPLICATION_TYPE", PA_VIEW."DEVELOPMENT_ADDRESS", PA_VIEW."DEVELOPMENT_DESCRIPTION", PA_VIEW."RECEIVED_DATE", PA_VIEW."SYS_STATUS", PA_VIEW."WARD"
FROM
"LAND"."PA_VIEW" PA_VIEW
WHERE
PA_VIEW."RECEIVED_DATE" >= {ts '2007-07-01 00:00:00.00'} AND
PA_VIEW."RECEIVED_DATE" <= {ts '2007-07-04 00:00:00.00'} AND
(PA_VIEW."SYS_STATUS" = '4' OR
PA_VIEW."SYS_STATUS" = '7');

I get the following error message: - ERROR at line 3 ORA-00911: invalid Character

Thanks for your help

 

[SantaMufasa]

Jason,

Your code that appears as "{ts '2007-07-01 00:00:00.00'}" is like no Oracle code that I've ever seen. I'm not aware that Oracle supports syntax that assigns meaning to "{" and "}" characters.

Or course, this may be some new feature that I must learn about.

If "RECEIVED_DATE" is a TIMESTAMP datatype, then try this code fragment in place of your current code:

...WHERE PA_VIEW."RECEIVED_DATE" between
    TO_TIMESTAMP('2007-07-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2') and
    TO_TIMESTAMP('2007-07-04 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2') AND...
*****************************************************************************

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[JasonMcConnell]

Hi SantaMufasa

I have changed the query to

SELECT "PA_VIEW"."APPLICATION_NUMBER", "PA_VIEW"."APPLICATION_TYPE", "PA_VIEW"."DEVELOPMENT_ADDRESS", "PA_VIEW"."DEVELOPMENT_DESCRIPTION", "PA_VIEW"."WARD", "PA_VIEW"."RECEIVED_DATE", "PA_VIEW"."SYS_STATUS"
FROM "LAND"."PA_VIEW" "PA_VIEW"
WHERE ("PA_VIEW"."RECEIVED_DATE between
 TO_TIMESTAMP('2007-07-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2') and TO_TIMESTAMP('2007-07-04 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2') AND ("PA_VIEW"."SYS_STATUS"='4' OR "PA_VIEW"."SYS_STATUS"='7');


I now get ERROR - ORA-00972: Identifer is too long

Thanks for your help

 

[Dagon]

You're missing a closing " after received_date, so Oracle thinks the whole thing is a single column name. You are also missing a closing bracket somewhere.

SELECT "PA_VIEW"."APPLICATION_NUMBER",
"PA_VIEW"."APPLICATION_TYPE", "PA_VIEW"."DEVELOPMENT_ADDRESS",
"PA_VIEW"."DEVELOPMENT_DESCRIPTION", "PA_VIEW"."WARD", "PA_VIEW"."RECEIVED_DATE", "PA_VIEW"."SYS_STATUS"
FROM "LAND"."PA_VIEW" "PA_VIEW"
WHERE ("PA_VIEW"."RECEIVED_DATE" between
TO_TIMESTAMP('2007-07-01 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2') and TO_TIMESTAMP('2007-07-04 00:00:00.00','YYYY-MM-DD HH24:MI:SS.FF2')
AND ("PA_VIEW"."SYS_STATUS"='4' OR "PA_VIEW"."SYS_STATUS"='7'))