need help with multiple conditions in Crystal Report

use the select expert or filter in the where clause if you're using a stored procedure.

See attached file for screen of the select expert.
CR-9-23-14-A.jpg

okay, so wrap what you have in parens . . . I'm going to call that X

(X)  AND
(
(
  (
    isnull(PRI WH ID)  or
    isnull(PRI Loc ID)
  )  OR
(  INSP_REQD = "Y"
   AND
  (
    isnull(INSP WH ID)  or
    isnull(INSP Loc ID)
  )  
)
)

NO POINTS please

@Kyle, there's one too many (

      AND (
            (
               ISNULL([PRIMARY_WH_ID])
            OR ISNULL([PRIMARY_Loc_ID])
            )
          OR (
               INSP_REQD = "Y"
               AND (
                      ISNULL([INSPECT_WH_ID])
                   OR ISNULL([INSPECT_Loc_ID])
                   )
             )
         )

Select allOpen in new window

To do the sort use the SORT EXPERT under the REPORT Menu

mlmcc

Thanks guys really appreciate it. Kyle, I do just what you said putting my conditions in () but when I save it and regenerate, CR returns nothing and changes the syntax to the attachment. This is CR 8.5 if that matters. TIA

it did not upload file sorry
CR-9-24-14-A-002.jpg

What do you mean Crystal changes the syntax?

DO you know there are records that meet the conditions?

Are the purchase order statuses single characters?
As you have it the LIKE is essentially an EQUAL comparison.  

I assume from the  picture you have multiple tables.
Since your filter is based on more than the main table, Crystal treats the JOINS as INNER so you probably aren't getting the POs that have NULL values in the other 2 tables.

You may have to build the query in the database or build in in a Crystal COMMAND and put the NULL tests into the joins rather than the WHERE clause.

mlmcc

mlmcc,

 I input the criteria just like pictured in CR-9-23-14-A.jpg,,,, inside parens as suggested, and after I save it and update the results I go back into the Record Selection Formula Editor and it looks like screen shot in file CR-9-24-A-002.jpg, it splits up the first 2 criterion, I just wanted to make sure that was not breaking the query.

The PO Status are single alpha characters


Yes there are 3 tables in this query. I attached a screen of the tables and they are default joins.

I have not made any views in oracle before, so would like to be able to do this in Crystal. can you guys help me out?

TIA Bill
CR-Linking-Expert.jpg

Can you upload the RPT file?

mlmcc

Yes, please see attached
Purch-Part-Without-Loc.rpt

You have checked the Report Option
     CONVERT DATABASE NULL VALUES TO DEFAULT

That option converts the NULL values you are looking for to a default value.  In your case since the fields a strings it converts to the empty string '' or ""

Use

{PURCHASE_ORDER.STATUS} like ["F", "R"] and
{PURC_ORDER_LINE.TOTAL_AMT_RECVD} < {PURC_ORDER_LINE.TOTAL_AMT_ORDERED}
AND
(
    (
        {PART.PRIMARY_WHS_ID} = ''
        OR
        {PART.PRIMARY_LOC_ID} = ''
    )
    OR
    (
        {PART.INSPECTION_REQD} = 'Y'
        AND
        (
            {PART.INSPECT_WHS_ID} = ''
            OR
            {PART.INSPECT_LOC_ID} = ''
        )
    )
)

Select allOpen in new window

mlmcc

here is SQL, It is still not returning anything. I am sure there are Open PO's that have parts with no locations for them.

SELECT
    PURC_ORDER_LINE."PURC_ORDER_ID", PURC_ORDER_LINE."DESIRED_RECV_DATE", PURC_ORDER_LINE."TOTAL_AMT_RECVD", PURC_ORDER_LINE."TOTAL_AMT_ORDERED",
    PURCHASE_ORDER."STATUS",
    PART."ID", PART."DESCRIPTION", PART."STOCK_UM", PART."COMMODITY_CODE", PART."INSPECTION_REQD", PART."PRIMARY_WHS_ID", PART."PRIMARY_LOC_ID", PART."INSPECT_WHS_ID", PART."INSPECT_LOC_ID"
FROM
    "SYSADM"."PURC_ORDER_LINE" PURC_ORDER_LINE,
    "SYSADM"."PURCHASE_ORDER" PURCHASE_ORDER,
    "SYSADM"."PART" PART
WHERE
    PURC_ORDER_LINE."PURC_ORDER_ID" = PURCHASE_ORDER."ID" AND
    PURC_ORDER_LINE."PART_ID" = PART."ID" AND
    (PURCHASE_ORDER."STATUS" LIKE 'R' OR
    PURCHASE_ORDER."STATUS" LIKE 'F') AND
    (PART."PRIMARY_WHS_ID" = '' OR
    PART."PRIMARY_LOC_ID" = '' OR
    PART."INSPECTION_REQD" = 'Y' AND
    (PART."INSPECT_WHS_ID" = '' OR
    PART."INSPECT_LOC_ID" = ''))
ORDER BY
    PART."ID" ASC

yes there is 1 that I got by simplifying the criteria, see file part example, and then 2nd file is just looking at parts on open PO's with outstanding qty's that require an incoming inspection, i took a screen of 1 part that does not have a inspection WH ID or Loc ID.

Also, where do you choose CONVERT DATABASE NULL VALUES TO DEFAULT ? maybe that is where this is going to crap?
Part-Example.jpg
simple-code.jpg

I don't think the "Convert Database NULL Values to Default" option is a factor in this.  That applies to how CR treats null fields.  If the tests in your record selection formula are being passed to the server, which seems to be the case, based on the query that you posted, then that CR setting shouldn't be an issue.

 IOW, I think you need to go back to using IsNull in your record selection formula, instead of testing for blank fields.  This is assuming, of course, that the fields will actually be null on the server, and not just blank.

 BTW, I don't think the changes to the record selection formula that you were seeing earlier were a problem.  CR seemed to be rearranging the tests slightly, putting the new ones in between the two that you already had, but it shouldn't affect the logic.

 Also, as mentioned earlier, you're using Like to look for single character values ("F" or "R").  Like is meant for pattern matching, with wildcards (eg. "F*" for any string that starts with an "F").  If you're really just looking for "F" or "R", then replace Like with In --- {PURCHASE_ORDER.STATUS} In ["F", "R"]

 It shouldn't affect the logic, but using Like when you don't actually need it can only lead to confusion, and could be less efficient if the server actually tries to do pattern matching, instead of a simple comparison.


 So, having said all of that, I don't see why the modified record selection formula in your CR-9-24-14-A-002.jpg screenshot would not produce records, if there were any that matched those conditions.

 Do you get records with your original record selection formula, before you added the new conditions?

 When you refer to PART fields that are null, like PART.PRIMARY_WHS_ID, _why_ are they null?  Is there a matching PART record and the fields are null, or are they null because there is no matching PART record?

 Your test for PART.INSPECTION_REQD = 'Y' and other PART fields that are null suggests the former (the record is there, and the fields are null).  But if it's the latter (no PART record at all), then you need an Outer Join to PART.

 James

I hadn't seen your last post when I wrote my last post.

 To answer your question about the "Convert ..." option, it's under File > "Report Options".  But, like I said, that affects how CR handles nulls internally.  If your record selection formula is being passed to the server, which seems to be the case, then that option won't affect it.  But if you have any other formulas that reference fields that could be null, it would affect those formulas.

 James

WHen I changed the SELECTION FORMULA in the report he attached to use  comparison to '' rather than IsNull I got results.  The NULLs are converted before the data hits the report and before the selection/filtering is done.

mlmcc

1. If part is on Open PO
          with a balance due > 0
               and part has no PRI WH ID or PRI LOC ID        --<< I can see this
2, If part is on open PO
          with a balance due > 0
                and INSP REQD = Y and part has no INSP WH ID or INSP LOC ID         --<< I can see this

Is "on Open PO"? status F or R?
  Regarding status, "LIKE 'F'" or LIKE 'R'" doesn't make sense unless you are using the % wildcard, so use equal instead
  or you could use: "STATUS" IN  ('F','R')

I don't see logic for balance > 0

You need another pair of parentheses I believe

WHERE (
           PURCHASE_ORDER."STATUS" = 'R'    -- changed to equal, could use IN ('F','R') instead
        OR PURCHASE_ORDER."STATUS" = 'F'    -- changed to equal
        )
 AND (
           PART."PRIMARY_WHS_ID" = ''
        OR PART."PRIMARY_LOC_ID" = ''
        OR (                                -- new parenthesis
            PART."INSPECTION_REQD" = 'Y'
              AND (
                     PART."INSPECT_WHS_ID" = ''
                  OR PART."INSPECT_LOC_ID" = ''
                  )
           )                                -- new parenthesis
      )

Select allOpen in new window

I would also encourage use of ANSI join syntax

SELECT
        PURC_ORDER_LINE."PURC_ORDER_ID"
      , PURC_ORDER_LINE."DESIRED_RECV_DATE"
      , PURC_ORDER_LINE."TOTAL_AMT_RECVD"
      , PURC_ORDER_LINE."TOTAL_AMT_ORDERED"
      , PURCHASE_ORDER."STATUS"
      , PART."ID"
      , PART."DESCRIPTION"
      , PART."STOCK_UM"
      , PART."COMMODITY_CODE"
      , PART."INSPECTION_REQD"
      , PART."PRIMARY_WHS_ID"
      , PART."PRIMARY_LOC_ID"
      , PART."INSPECT_WHS_ID"
      , PART."INSPECT_LOC_ID"
FROM "SYSADM"."PURC_ORDER_LINE" PURC_ORDER_LINE
INNER JOIN "SYSADM"."PURCHASE_ORDER" PURCHASE_ORDER 
                  ON  PURC_ORDER_LINE."PURC_ORDER_ID" = PURCHASE_ORDER."ID"
INNER JOIN "SYSADM"."PART" PART
                  ON  PURC_ORDER_LINE."PART_ID" = PART."ID"
WHERE (
           PURCHASE_ORDER."STATUS" = 'R'    -- changed to equal, could use IN ('F','R') instead
        OR PURCHASE_ORDER."STATUS" = 'F'    -- changed to equal
        )
 AND (
           PART."PRIMARY_WHS_ID" = ''
        OR PART."PRIMARY_LOC_ID" = ''
        OR (                                -- new parenthesis
            PART."INSPECTION_REQD" = 'Y'
              AND (
                     PART."INSPECT_WHS_ID" = ''
                  OR PART."INSPECT_LOC_ID" = ''
                  )
           )                                -- new parenthesis
      )
ORDER BY PART."ID" ASC

Select allOpen in new window

Are you trying to do this in a Crystal command or in the select expert?

mlmcc

As a part time CR writer and general noob at any programming or SQL,  i use Record Selection Formula Editor. I think the logic you guys have offered is correct.

We want to find parts on open PO (Status R or F) that  have demand ({PURC_ORDER_LINE.TOTAL_AMT_RECVD} < {PURC_ORDER_LINE.TOTAL_AMT_ORDERED}) that do not have primary warehouse or primary location ID's assigned or are flagged to require an inspection (PART."INSPECTION_REQD" = 'Y') and do not have inspection warehouse or locations set.

It seems when i try to get the report to look at the warehouse and location fields, that are not filled in, the report breaks. Isnull does not return any results and neither does this syntax ({PART.PRIMARY_WHS_ID} = '') so is there another thing i can try?

WHen I modified the report you uploaded to use the ='' instead of IsNull I got data back (refilter using saved data).

Can you upload the report that has the formula that doesn't return data?

mlmcc

Please see attached. I also attached a screen showing parts with no warehouse locations that should be returned in other report that is looking for parts with no warehouse locations.
Purch-Part-Without-Loc-Current.rpt
Screen-with-select-expert-and-parts-with

returns nothing.

Have to reverse the tests just in case the field is NULL

mlmcc

Oh, duh, yeah.  You're right, of course.  Thanks for the correction.

 The tests that I posted should be:

IsNull ({PART.PRIMARY_WHS_ID}) or
{PART.PRIMARY_WHS_ID} = ''

 James

OK that returned 1800 parts.

each condition by itself returned Zero records, but together 1800 parts,,that seems pretty weird to me.

I think i have something that will work, Thanks for the help, let me analyze this a little bit more.

each condition by itself returned Zero records, but together 1800 parts,,that seems pretty weird to me.

 If you're talking about the tests in my last post, I don't think that's possible.  Something else must have been going on.

 James

Thanks all for the help, I really appreciate it.

I'm curious.  Did you finally figure out what was going on?  Were the fields actually null or blank or ...?

 James