need help with multiple conditions in Crystal Report

Need to get conditions
 1. If part is on Open PO with a balance due > 0 and part has no PRI WH ID or PRI LOC ID, then display on report.
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 then display on report.
3, sort by PART_ID
wallis34sysadminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
use the select expert or filter in the where clause if you're using a stored procedure.
0
wallis34sysadminAuthor Commented:
See attached file for screen of the select expert.
CR-9-23-14-A.jpg
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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)
  )  
)
)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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])
                   )
             )
         )

Open in new window

0
mlmccCommented:
To do the sort use the SORT EXPERT under the REPORT Menu

mlmcc
0
wallis34sysadminAuthor Commented:
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
0
wallis34sysadminAuthor Commented:
it did not upload file sorry
CR-9-24-14-A-002.jpg
0
mlmccCommented:
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
0
wallis34sysadminAuthor Commented:
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
0
mlmccCommented:
Can you upload the RPT file?

mlmcc
0
wallis34sysadminAuthor Commented:
Yes, please see attached
Purch-Part-Without-Loc.rpt
0
mlmccCommented:
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} = ''
        )
    )
)

Open in new window


mlmcc
0
wallis34sysadminAuthor Commented:
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
0
wallis34sysadminAuthor Commented:
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
0
James0628Commented:
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
0
James0628Commented:
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
0
mlmccCommented:
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
0
PortletPaulfreelancerCommented:
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
      )

Open 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

Open in new window

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

mlmcc
0
wallis34sysadminAuthor Commented:
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?
0
mlmccCommented:
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
0
wallis34sysadminAuthor Commented:
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
0
mlmccCommented:
For test purposes try using this selection

{PURCHASE_ORDER.STATUS} In ["F", "R"] and
(
    (
        {PART.PRIMARY_WHS_ID} = ''
        OR
        {PART.PRIMARY_LOC_ID} = ''
    )

)

Open in new window


mlmcc
0
wallis34sysadminAuthor Commented:
returns nothing.
0
James0628Commented:
How about this?

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

 Just those two tests, by themselves.  That should give you any records where PRIMARY_WHS_ID is blank or null.

 If you don't get any results, we need to figure out why.  The only thing that comes to mind would be that PRIMARY_WHS_ID is only null/blank when there is no PART record, in which case you probably need to change the Join to PART to be an Outer Join.

 If you do get results, try each of those conditions separately (for a blank field and then for a null field), and see which one gives you results, or if you get results from both.  Then we will at least know whether you should be looking for blank or null fields, or possibly both.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Have to reverse the tests just in case the field is NULL

mlmcc
0
James0628Commented:
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
0
wallis34sysadminAuthor Commented:
OK that returned 1800 parts.
0
wallis34sysadminAuthor Commented:
each condition by itself returned Zero records, but together 1800 parts,,that seems pretty weird to me.
0
wallis34sysadminAuthor Commented:
I think i have something that will work, Thanks for the help, let me analyze this a little bit more.
0
James0628Commented:
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
0
wallis34sysadminAuthor Commented:
Thanks all for the help, I really appreciate it.
0
James0628Commented:
I'm curious.  Did you finally figure out what was going on?  Were the fields actually null or blank or ...?

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.