Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with record selection including empty fields

Status
Not open for further replies.

dwalsh514

Technical User
Jul 4, 2006
5
US
Hello all, I am trying to setup a record selection that uses a parameter to query a field called Workorder.phone on an Oracle 8.1 database. My goal is to report on all records that do not have ie, '11/06', in the Workorder phone field. So far, I've managed to pull the records that do not have '11/06' in this field, but I know there are many more records, of which all of them are empty, or Null. Below is the record selection I'm using.

If {?PHONE} <> "NONE" then
if isnull({WORKORDER.PHONE}) then
{WORKORDER.PHONE} <> {?PHONE}
else If {?PHONE} = "NONE"then
true
)
Here is the Show SQL:
SELECT
WORKORDER."WONUM", WORKORDER."STATUS", WORKORDER."WORKTYPE", WORKORDER."DESCRIPTION", WORKORDER."HISTORYFLAG", WORKORDER."WOPRIORITY", WORKORDER."PHONE", WORKORDER."WOPM1", WORKORDER."ESTATAPPRLABHRS"
FROM
"GAFMAX"."WORKORDER" WORKORDER
WHERE
WORKORDER."WOPM1" = 'SH' AND
WORKORDER."PHONE" IS NULL AND
WORKORDER."PHONE" <> '11/06' AND
WORKORDER."STATUS" <> 'COMP' AND
(WORKORDER."WOPRIORITY" = 3 OR
WORKORDER."WOPRIORITY" = 1) AND
WORKORDER."HISTORYFLAG" = 'N'

The Show SQL looks good to me, in that it should do what I expect it to, but the records with an empty workorder.phone are not being returned. Any ideas would be greatly appreciated. Thank you all. Daniel
 
The SQL is not quite right. these two conditions cannot be true simultaneously.

WORKORDER."PHONE" IS NULL AND
WORKORDER."PHONE" <> '11/06' AND

replace with

(WORKORDER."PHONE" IS NULL OR
WORKORDER."PHONE" <> '11/06') AND

Is this a command you are using or is this the SQL generated by Crystal?

Ian
 
I created the sql statement in Crystal record selection. Once I ran the report, the Show Sql that is posted above is what was returned. I did fail to say that the report return empty, no records were selected at all.
If you could look at the Crystal record selection and suggest how to correct it to use an OR, that would be great.
 
Try:

(
If {?PHONE} <> "NONE" then
(
isnull({WORKORDER.PHONE}) or
{WORKORDER.PHONE} <> {?PHONE}
) else
If {?PHONE} = "NONE" then
true
)

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top