Solved

need help with multiple conditions in Crystal Report

Posted on 2014-09-23
33
133 Views
Last Modified: 2014-10-01
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
0
Comment
Question by:wallis34
  • 14
  • 9
  • 6
  • +2
33 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40340260
use the select expert or filter in the where clause if you're using a stored procedure.
0
 

Author Comment

by:wallis34
ID: 40340273
See attached file for screen of the select expert.
CR-9-23-14-A.jpg
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40340303
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40340545
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40340559
To do the sort use the SORT EXPERT under the REPORT Menu

mlmcc
0
 

Author Comment

by:wallis34
ID: 40341446
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
 

Author Comment

by:wallis34
ID: 40341450
it did not upload file sorry
CR-9-24-14-A-002.jpg
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40341830
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
 

Author Comment

by:wallis34
ID: 40342014
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40342065
Can you upload the RPT file?

mlmcc
0
 

Author Comment

by:wallis34
ID: 40342264
Yes, please see attached
Purch-Part-Without-Loc.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40342380
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
 

Author Comment

by:wallis34
ID: 40342484
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
 

Author Comment

by:wallis34
ID: 40342680
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
 
LVL 34

Expert Comment

by:James0628
ID: 40342755
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
 
LVL 34

Expert Comment

by:James0628
ID: 40342767
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 100

Expert Comment

by:mlmcc
ID: 40342852
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40343087
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40343173
Are you trying to do this in a Crystal command or in the select expert?

mlmcc
0
 

Author Comment

by:wallis34
ID: 40343986
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40343996
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
 

Author Comment

by:wallis34
ID: 40344207
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 40344295
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
 

Author Comment

by:wallis34
ID: 40344434
returns nothing.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 400 total points
ID: 40344500
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40344531
Have to reverse the tests just in case the field is NULL

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40344644
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
 

Author Comment

by:wallis34
ID: 40344723
OK that returned 1800 parts.
0
 

Author Comment

by:wallis34
ID: 40344728
each condition by itself returned Zero records, but together 1800 parts,,that seems pretty weird to me.
0
 

Author Comment

by:wallis34
ID: 40344742
I think i have something that will work, Thanks for the help, let me analyze this a little bit more.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40344826
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
 

Author Closing Comment

by:wallis34
ID: 40355229
Thanks all for the help, I really appreciate it.
0
 
LVL 34

Expert Comment

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

 James
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now