HELP with ORACLE Error Message 4

[BoniR]

hello!
I was pointed to this forum after posting in an SQL one by mistake...hope I am in the right place now...
I am using Oracle 8i and am having a problem with an SQL query I wrote to be used in Crystal Reports 9...
The error message that I am getting is Oracle error: 'ORA-00920' which I think means that I have an invalid operator somewhere...unfortunately I can not find it...any help would be appreciated!

select 
ch.company_id company,
substr(phys.last_name || ', ' || phys.first_name,1,25) physician,
phys.short_name physician_short,
phys.phys_id physician_idnum,
substr(p.last_name || ', ' || p.first_name,1,25) patient ,
p.patient_number pat_num,
substr(f.last_name || ', ' || f.first_name,1,25) guarantor,
'CHG' type,
ch.post_fromdate dos,
substr(substr(cp.procedure_code,1,5) || ch.Proc_modifier,1,9) cpt,
cp.proc_desc description,
ch.charge_amount charge_amount,
ch.remain_amount remain_amount,
ch.Date_entered entered,
substr(wu.last_name || ', ' || wu.first_name,1,20) user_name,
fc.description fin_class,
'1' transtype
from patient p, physician phys,family f, charge_history ch, 
company_procedure cp,westland_user wu,financial_class fc
where ch.company_id = {?Company}
and {?DateRangeType} >= {?StartDate}
and {?DateRangeType} < ( {?EndDate} + 1)
and cp.procedure_id = ch.procedure_id
and phys.phys_id = ch.physician_id
and p.patient_id = ch.patient_id
and p.active_yn = 'Y'
and p.company_id = ch.company_id
and f.family_id = p.family_id
and f.company_id = p.company_id
and wu.user_id = ch.Last_mod_user_id
and fc.financial_class_id = ch.financial_class_id_1
order by physician, patient,pat_num,dos,transtype

Thanks again for any help you can provide!
Boni




Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[oradba101]

Hi, Boni

On the FROM line shouldn't there be an underscore between "physician" and "phys" and "phys" and "family"?

This is the error from the Oracle documentation.

ORA-00920 invalid relational operator

Cause: A search condition was entered with an invalid or missing relational operator.

Action: Include a valid relational operator such as =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition.

Regards,


William Chadbourne
Oracle DBA

 

[lewisp]

I'm not familiar with Crystal reports. What does this part mean?

and {?DateRangeType} >= {?StartDate}
and {?DateRangeType} < ( {?EndDate} + 1)

It doesnt seem to correlate to anything within the query, so where does DateRangeType come from and what does it resolve to when the report runs?

 

[BoniR]

oradba101-
I am using table alias (p, phy, f, etc) for the tables patient, physician, family, etc...so, is there supposed to be an underscore there?
Can you only use single character aliases?

lewisp-
Crystal Reports is a reporting software that allows you to use SQL queries to generate the data that needs reporting on...unfortunately I don't usually do my reports through a query but rather I go straight to the tables and pull my data...but this is a report that someone else did and I am trying to fix it...ugh!
The part that you mentioned in your reply is a date range that is prompted for at the beginning of the report...
I am really starting to think that they might be the problem...or at least that I am using them in an incorrect manor...
I think that I will try taking them out and seeing what I get...

Thank you both for all of your help!
Boni




Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269

 

[SantaMufasa]

Boni,

If you are running this in the Oracle world, then Oracle will have trouble with both curly braces "{ }" and your question marks in your (e.g., "?StartDate"). You can include a "?" in your naming in the Oracle world by always (creation and all subsequent uses) referring to the name withing double quotes, which you haven't done here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Sorry if my post was late and duplicative on this matter...I had to go change a grandkid's diaper and the world passes me by while he passes his food.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
In this part of your code:

where ch.company_id = {?Company}
[COLOR=red]
and {?DateRangeType} >= {?StartDate}
and {?DateRangeType} < ( {?EndDate} + 1)
[/color]
and cp.procedure_id = ch.procedure_id
and phys.phys_id = ch.physician_id
and p.patient_id = ch.patient_id
and p.active_yn = 'Y'
and p.company_id = ch.company_id
and f.family_id = p.family_id
and f.company_id = p.company_id
and wu.user_id = ch.Last_mod_user_id
and fc.financial_class_id = ch.financial_class_id_1

You appear to be using a CR parameter instead of a database field when comparing to the StartDate and EndDate CR parameters.
When this query gets to the database,Oracle has no clue as to what {?DateRangeType}is..This can cause bad parsing of the rest of the query..remove that part and test...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BoniR]

SantaMufasa and Turkbear-
the '?' was my problem! Thanks...once I removed those it accepted my changes with no error...
thanks again for your help...
SantaMufasa-
I totally understand the needs of children...hehe...sometimes they don't wait!

Have an EXCELLENT weekend!
Boni



Boni J. Rychener
Hammerman Associates, Inc.
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter

http://www.hammerman.com

800-783-2269