Is their a way to create an If statement in either Crystal Reports Select Statement or SQL Query in Crystal?

Basically I want to create an if statement on my report (preferably as part of my SQL query as that seems to always run much faster) that will compare two fields and if they match filter out specific data and if they don't match filter out different data.

My current SQL statement reads as:

SELECT DISTINCT jp.JobNum AS jpJobNum,
jp.TargetJobNum,
jh.JobNum AS jhJobNum
FROM JobHead jh LEFT JOIN JobProd jp
ON jh.JobNum = jp.TargetJobNum
WHERE jh.JobNum = '{?Pm-Command.jpJobNum}'
OR jp.JobNum = '{?Pm-Command.jpJobNum}'


This is the version that returns everything. I want to add an if statement that will say

 if jp.JobNum = jp.TargetJobNum then WHERE jp.JobNum LIKE 'R%' ELSE return the WHERE statement from above.

Is this possible in the SQL Query? If not can it be done through a selection formula in Crystal? Or if you have any other recommendations I'm open to them as well.

Thanks in advance for the help!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
Jarred MeyerProduction ManagerAuthor Commented:
Ok, I've got something that is working!

In my subreport I changed my SQL statement to read;

SELECT DISTINCT jp.JobNum jpJobNum,
jp.TargetJobNum,
jh.JobNum jhJobNum,
FROM JobHead jh
LEFT JOIN JobProd jp
ON jh.JobNum = jp.TargetJobNum
OR jh.JobNum = jp.JobNum
WHERE jh.JobNum = '{?Pm-Command.jpJobNum}'
OR jh.JobNum = '{?Pm-Command.jpJobNum}'

Then I used the exact Selection Formula from above:

if {?Pm-Command.jpJobNum} = {Command.TargetJobNum} then {Command.jpJobNum} like 'r*' and {?Pm-Command.jpJobNum} = {Command.TargetJobNum}
else {Command.jpJobNum} = {?Pm-Command.jpJobNum}


This does exactly what I need... Sorry for this being so complicated:(
0
 
peter57rCommented:
WHERE ( jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%')
Or  jh.JobNum = '{?Pm-Command.jpJobNum}'  OR jp.JobNum = '{?Pm-Command.jpJobNum}'

0
 
Jarred MeyerProduction ManagerAuthor Commented:
Give me a minute to figure out how I just screwed up where I was before I added your answer.. :) Somehow I messed up where I was at from the previous post and can't get my records back.. I'll let you know if that pans out after I get the darn thing working again.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
By filtering on the joined table, Crystal will treat this as an inner join

mlmcc
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Alright well I got back to square one and tried the code but it is filtering out all of the extra stuff from all of the jobs.. Do you know if there is a way to use an If statement as part of the Where clause?
0
 
peter57rCommented:
There is a problem, as mlmcc has indicated , with the left join here.  I didn't notice the join type before.

But your 'if' requirement is implemented as I have shown, using a compound set of conditons in the Where clause.

However to get round the left join issue the query will have to be re-written.  Just to be clear about this... your current query is NOT working as you think it is.  The left join is not being utilised.  It is as if your query used an inner join.

The OR condition makes the situation tricky to deal with.  
I think it requires two levels of query...Try

Select * from
(SELECT DISTINCT jp.JobNum AS jpJobNum,jh.JobHum as jhjobnum
jp.TargetJobNum,
jh.JobNum AS jhJobNum
FROM JobHead jh LEFT JOIN JobProd jp
ON jh.JobNum = jp.TargetJobNum) as jq
WHERE ( TargetJobNum is not null and jpJobNum LIKE 'R%')
Or  jhJobNum = {?Pm-Command.jpJobNum}  OR jpJobNum = {?Pm-Command.jpJobNum}

You may need to fix punctuation for your own database.

0
 
Jarred MeyerProduction ManagerAuthor Commented:
Ok well I ran that through and I'm getting some very randon results.. I'm thinking that I may have to explain this in a bit more detail perhaps.. I'll get a more detailed explanation after a little bit.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
The original statement I have posted works almost exactly how I need it to. I should have mentioned that this statement is used in a subreport which is placed in the detail section of the main report. All I needed to to is add the if then statement into the SQL query or the subreports Selection formula..

Does this make any better since?

0
 
mlmccCommented:
You can have a where clause like

if jp.JobNum = jp.TargetJobNum then
     jp.JobNum LIKE 'R%'
ELSE
    jh.JobNum = '{?Pm-Command.jpJobNum}'
    OR
    jp.JobNum = '{?Pm-Command.jpJobNum}'

mlmcc


0
 
Jarred MeyerProduction ManagerAuthor Commented:
That definitely seems like what I'm looking for however, I got an error.. Here is the error I got;
 ErrorMsg
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Here is what the code looked like with the If statement;

SELECT DISTINCT jp.JobNum jpJobNum,
jp.TargetJobNum,
jh.JobNum jhJobNum
FROM JobHead jh
LEFT JOIN JobProd jp
ON jh.JobNum = jp.TargetJobNum
--WHERE jh.JobNum = '{?Pm-Command.jpJobNum}'
--OR jp.JobNum = '{?Pm-Command.jpJobNum}'
WHERE
if jp.JobNum = jp.TargetJobNum
THEN
     jp.JobNum LIKE 'R%'
ELSE
    jh.JobNum = '{?Pm-Command.jpJobNum}'
    OR
    jp.JobNum = '{?Pm-Command.jpJobNum}'

P.S. There was actually a second error that popped up first;

 ErrorMsg2
0
 
James0628Commented:
 mlmcc & Peter,

 I get the impression that he's using a CR Command.  If so, does the thing about CR changing an Outer join to Inner apply?  I wouldn't think it would do that in a Command, but I don't normally use them.

 James
0
 
James0628Commented:
 SeyerIT,

 What db are you using?  If it's MS SQL, you can't use IF/THEN in a WHERE clause like that.  mlmcc was wrong about that.  Syntactically, the WHERE that Peter posted first should work:

WHERE ( jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%')
Or  jh.JobNum = '{?Pm-Command.jpJobNum}'  OR jp.JobNum = '{?Pm-Command.jpJobNum}'


 I'm not sure if it's what you want, logically, but the syntax looks OK.

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Yes it is a Crystal Command..
0
 
Jarred MeyerProduction ManagerAuthor Commented:
The command you have there doesn't appear to work any differently than what I already have.. In this circumstance anyways I guess. It keeps returning all of the information rather than filtering out everything except for the R jobs in the instance that the targetjobnum = jp.jobnum.

I guess the best shot at this is for me to explain what I'm trying to accomplish in it's entirety and see if you guys can offer any suggestions..

Here goes:) (give me a sec as it will take a bit to type up.. )
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I have a main report setup which pulls a main job and all of it's subjobs. Before I get into the setup of the report I'll explain the format of how all the jobs tie together cause I think that may be the most important part of how to get this working.

We have Jobs which we complete in our plant. The jobs encompass labor transactions, operations, assembly parts, detail parts, etc. That is probably pretty straight forward. Now if we have any rework that needs to be done on that job, we will create a subjob that starts with an 'R' and followed by the core jobs six numbers. e.g. the core job is 10500-1 and the reworks for that job would be R10500-1-1, R10500-1-1A, R10500-1-31 and so on.. Next is SubJobs themselves; If we have a very large job we will break some of the assemlies of the core job into subjobs. So in other words, subjobs can either be rework or they can be an actual subjob. The last thing with the actual subjobs is that they are very similar to the main jobs in which they could even have rework jobs within them.

Here is an example of how a job could look.. Visually anyways;

10500-1
    R10500-1-1A
    R10500-1-31
    R10500-1-12
    10500-2
          R10500-2-23
    10500-3
    10500-4
          R10500-4-2
          R10500-4-2A

Now the way these jobs all tie together in the database is through JobProd.TargetJobNum. I also use the JobHead table which houses all of the header info for the jobs in my setup as well. JobHead connects to JobProd by means of JobHead.JobNum = JobProd.JobNum.

What I'm trying to accomplish in the creation of this report is to allow a user to type in the main job (10500-1) in the parameter and to have the report populate all of the data pertaining to hours and costs for the main job as well as all of the main jobs' rework jobs, subjobs, and subjobs' rework jobs.

So onto my current setup;

In all the Crystal Reports I'm connecting directly to the SQL database by means of the SQL Query Commands.

I was thinking if I utilized the main report to bring in all of the jobs and sub jobs, not including rework and a few other misc sub jobs like planning (planning jobs start with eng or end with pln) I could use it as a gateway for a subreport placed in the detail section to pull each individual jobs' data. In other words for the example above, the main report will return the following;

Parameter Job entered is: 10500-1
Details Section: 10500-1    <subreport>
Details Section: 10500-2    <subreport>
Details Section: 10500-3    <subreport>
Details Section: 10500-4    <subreport>

The queries get a little confusing particularly because of the way I have to tie the subjobs, incl. rework to there corresponding main job or subjobs.. Looking at the example above with the rework the way the targetjob field would look is:


jp.JobNum                            jp.TargetJobNum
10500-1              
    R10500-1-1A                             10500-1
    R10500-1-31                              10500-1
    R10500-1-12                              10500-1
    10500-2                                      10500-1
          R10500-2-23                        10500-2
    10500-3                                      10500-1
    10500-4                                      10500-1
          R10500-4-2                          10500-4
          R10500-4-2A                       10500-4

Now after looking at that example I created the following query in the Main report to pull just the Main Job and it's corresponding SubJobs (not including rework because that pulls in the subreports)
({?JobNum} is the parameter that prompts the user for a job number)


SELECT DISTINCT jp.JobNum AS jpJobNum,
jp.TargetJobNum,
jh.JobNum AS jhJobNum
FROM JobProd jp LEFT OUTER JOIN JobHead jh
ON jh.JobNum = jp.TargetJobNum
WHERE (jh.JobNum = '{?JobNum}'
OR jp.JobNum = '{?JobNum}')
AND jp.JobNum NOT LIKE 'R%'
AND jp.JobNum NOT LIKE '%PLN%'
AND jp.JobNum NOT LIKE '%ENG%'


This give me the results as I put them in the example above. Here are they are again:
Parameter Job entered is: 10500-1
Details Section: 10500-1    <subreport>
Details Section: 10500-2    <subreport>
Details Section: 10500-3    <subreport>
Details Section: 10500-4    <subreport>

Next, I created the subreport which is placed in the detail section of the main crystal report which uses the following query;

SELECT DISTINCT jp.JobNum jpJobNum,
jp.TargetJobNum,
jh.JobNum jhJobNum
FROM JobHead jh
LEFT JOIN JobProd jp
ON jh.JobNum = jp.TargetJobNum
WHERE jh.JobNum = '{?Pm-Command.jpJobNum}'
OR jp.JobNum = '{?Pm-Command.jpJobNum}'


This is the query that I have been replacing my WHERE clause with the one Peter supplied. Note the one thing I had not got working in this query is the subreport that is located on the detail line with the main report is not currently bringing back the main job into the subreport. It is however returning the subjobs (which is what the question was originally about, need to keep these from showing up here).

And so back to the original question, how can I prevent the subreport which is located on the detail section of the main report from returning the subjobs of the main job? I only want it to return the Main job itself and the main jobs Rework jobs. As for the subreport located in the detail sections of the main report where there are subjobs located, the subreports should be returning the subjob and the subjobs' rework jobs (which it is currently).

In the end, the setup should look like this;

Parameter Job entered is: 10500-1

Details Section: 10500-1    <subreport>
                                                   Details Section: 10500-1
                                                   Details Section: R10500-1-1A
                                                   Details Section: R10500-1-31
                                                   Details Section: R10500-1-12  
Details Section: 10500-2    <subreport>
                                                   Details Section: 10500-2
                                                   Details Section: R10500-2-23
Details Section: 10500-3    <subreport>
                                                   Details Section: 10500-3
Details Section: 10500-4    <subreport>
                                                   Details Section: 10500-4
                                                   Details Section: R10500-4-2
                                                   Details Section: R10500-4-2A

However, it is currently looking like this;

Details Section: 10500-1    <subreport>
                                                   Details Section: 10500-1 (this is not showing up)
                                                  Details Section: 10500-2 (Don't want this to show up here)
                                                   Details Section: 10500-3 (Don't want this to show up here)
                                                   Details Section: 10500-4 (Don't want this to show up here)
                                                  Details Section: R10500-1-1A
                                                   Details Section: R10500-1-31
                                                   Details Section: R10500-1-12  
Details Section: 10500-2    <subreport>
                                                   Details Section: 10500-2
                                                   Details Section: R10500-2-23
Details Section: 10500-3    <subreport>
                                                   Details Section: 10500-3
Details Section: 10500-4    <subreport>
                                                   Details Section: 10500-4
                                                   Details Section: R10500-4-2
                                                   Details Section: R10500-4-2A
0
 
Jarred MeyerProduction ManagerAuthor Commented:
That was a load of information which hopefully makes since:)

I wish I could add more points and understand if this goes above what you guys are willing to tackle.. Just figured it was going to be to difficult to make since unless the entire explanation was there.
0
 
James0628Commented:
That WHERE will return all of your original records, _plus_ any records where jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%'.

 If you want to start with the original records, but then only include the ones in that set where jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%', join the conditions with an AND:


WHERE ( jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%')
AND (jh.JobNum = '{?Pm-Command.jpJobNum}'  OR jp.JobNum = '{?Pm-Command.jpJobNum}')


 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Using that method makes all the data in my subreports disappear... See if my explanation above helps to understand the craziness I've got going on :)

Thanks all of you for your time!
0
 
James0628Commented:
For the record, I posted that last message before I saw your detailed post.  I've looked over your detailed post, and I'm not sure if that's what you're looking for or not.  :-)  The simplest thing might be to just give it a try.

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I was thinking that you probably posted that before or your an insanely fast reader:) I did give it a try though..
0
 
James0628Commented:
OK, cross-posting again.  :-)  I'll take another look at it.
0
 
James0628Commented:
This may go back to what mlmcc and Peter were saying about CR changing Outer joins to Inner.  It will do that when you're using tables as the datasource.  I would have expected it to leave Commands alone (that's one of the reasons to use a Command, so that you have complete (?) control over the query), but I don't normally use Commands, so I can't say for sure.

 Just out of curiousity, what do you get if you change the WHERE to:

WHERE ( jp.JobNum = jp.TargetJobNum and  jp.JobNum LIKE 'R%')


 IOW, just leave out the tests on the {?Pm-Command.jpJobNum} parameter.  Of course without those tests, you may get a _lot_ of data, but if you can give it a try, do you get those records, or nothing at all?

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
That doesn't return any records...

On a side note, I did make progress with a Crystal Selection formula to remove the Sub jobs from the subreport on just the main job.. Even though i'm still missing my main job from that subreport.:(

The selection formula as I have it on the Subreports;

if {?Pm-Command.jpJobNum} = {Command.TargetJobNum} then {Command.jpJobNum} like 'r*' and {?Pm-Command.jpJobNum} = {Command.TargetJobNum}
else {Command.jpJobNum} = {?Pm-Command.jpJobNum}

Although since I haven't got the main job to show up in this subreport I'm not sure if the part about the And {?Pm-Command.jpJobNum} = {Command.TargetJobNum} is working.. I removed this temporarily so I can determine when I get the SQL query correct  to incorporate the main job only in the subreport located at the detail section of the main report with the main job..


0
 
mlmccCommented:
My code was for the Crystal selection expert not for a command.

mlmcc
0
 
Jarred MeyerProduction ManagerAuthor Commented:
mlmcc, ah.. Sorry about that..
0
 
James0628Commented:
OK, a basic question.  In your first post, you said:

 > if jp.JobNum = jp.TargetJobNum then WHERE jp.JobNum LIKE 'R%'
 > ELSE return the WHERE statement from above.

 That test is looking for JobProd records where JobNum and TargetJobNum are the same (jp.JobNum = jp.TargetJobNum), _and_ the value (in both fields) starts with an "R".  Is that really what you want?

 FWIW, in the sample data in your post from this morning, none of the TargetJobNum values start with an "R".  For that matter, jp.JobNum and jp.TargetJobNum never seem to be the same either.  Maybe you just didn't happen to include examples of either of those conditions in the sample data, but I'm thinking that the condition from your original post (quoted above) was wrong.

 James
0
 
James0628Commented:
Cross-posting again.  I hadn't seen your last post before I posted that.
0
 
James0628Commented:
FWIW, you're doing the (jh.JobNum = '{?Pm-Command.jpJobNum}') test twice in the WHERE.  I'm sure it doesn't hurt anything, but you could remove one of the tests.

 You're also doing the ({?Pm-Command.jpJobNum} = {Command.TargetJobNum}) test twice in the record selection.  Again, there's no real harm, but it's not necessary.  You could shorten it to:

if {?Pm-Command.jpJobNum} = {Command.TargetJobNum} then {Command.jpJobNum} like 'r*'
else {Command.jpJobNum} = {?Pm-Command.jpJobNum}

 OTOH, if you prefer having the test twice (for whatever reason), you can just leave it the way it is.

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Honestly I struggle to understand it as well but I can tell you that basically what it's doing is on each subreport in the detail section it is looking at the jp.JobNum which is coming over from the Main report through PM.Command.jp.JobNum. Then the sql command on the subreport pulls all of the subjobs' rework jobs and in the case of the Main job will incidentally pull the subjobs again. So to filter out these sub jobs only when the main job pulls to the subreport I use the TargetJobNum because when placed into the detail section of the main jobs' sub report it will display the Mainjob number and will never display anything else so that is the number the filter picks up and so it will see the Main Job # = TargetJobNum and then it knows to only display the R jobs (after looking back at this it seems I may have been able to instead tell it to use TargetJobNum = "" as well since that is actually what shows up on the report for the jpJobNum.) On the subreports where there are actually subreports pulling, the targetjobnum will display the mainjob # and will not equal the subjob #.

I'm thinking it would have made more since now that I look at it to simply say

if {Command.TargetJobNum} = "" then {Command.jpJobNum} like 'r*'
and {?Pm-Command.jpJobNum} = {Command.TargetJobNum}
else {Command.jpJobNum} = {?Pm-Command.jpJobNum

However, since the Mainjob shows up in the detail section as the targetjobnum and it shows as "" in the header, the filter is just picking up the targetjobnum from the details.

I hope this clears things up a bit although I kind of think I may have made it worse:)

It is definitely a very confusing one to piece together.. And in fact now I'm thinking I may end up trying to do this all in one report since I just discovered I can't put a subreport in a subreport :)

0
 
Jarred MeyerProduction ManagerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for SeyerIT's comment http:/Q_27418081.html#37045723

for the following reason:

Figured out how to combine a selection formula with a SQL Where clause
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I think there was a reason it had to run twice.. I believe if I remember without wanting to look at it again (to avoid a headache:), it had to validate the information coming over through the SQL query then I needed it again to locate and filter the Main job correctly in the subreport..

In any case I did start trying to conquer this using just one SQL statement.. I'm currently trying to use Union but am not too familiar with it.. Perhaps I could swing this question into this as it is still trying to accomplish the same end result as the rest of the conversation but instead of using subreports its being done with just one SQL query?
0
 
Jarred MeyerProduction ManagerAuthor Commented:
If an admin can let me know if we can kind of switch this question to being solved using a different method I will continue.. Otherwise I can just close this and create a new question?
0
 
James0628Commented:
 > if {Command.TargetJobNum} = "" then {Command.jpJobNum} like 'r*'
 > and {?Pm-Command.jpJobNum} = {Command.TargetJobNum}
 > else {Command.jpJobNum} = {?Pm-Command.jpJobNum

 The problem with that is that you're saying "if TargetJobNum is empty, then if TargetJobNum = your_parameter", and that's never going to be true unless the parameter is empty, which I assume it should never be (because it's always going to be a job number).

 So, FWIW, I don't think that logic works either.

 As for continuing or starting a new question, mlmcc can probably give you an answer on that, since he's also a zone advisor here.

 James
0
 
mlmccCommented:
Have you found an answer that is sartisfactory?
If so post it and this can be closed.

If you need more discussion then it can continue here or if the question is going to change it is probably better to close this and start fresh.

mlmcc
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I think to keep this from being any more confusing I will close this out based on the solution that I found using a parameter and a sql statement above.. That seems to be working just fine..

James, I'm probably not explaining it right.. Sorry again/ here is my stab at it.. And this is how it is functioning as well;

In the Record Selection Formula Editor I am telling it if the parameter coming from the main report is equal to the targetjobnum then return jobs starting with R AND return jobs where the parameter equals the targetjobnumber Otherwise return jobnums that equal the parameter.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Found a way to utilize a SQL command as well as filtering the remaining data in a Crystal Record Selection Formula
0
 
James0628Commented:
I'm still not really sure how the record sets ("TargetJobnum = parameter" vs "jobs starting with R") intersect, but as long as you've got something that works.

 James
0
 
Jarred MeyerProduction ManagerAuthor Commented:
It is working perfectly.. Just kind of hard to explain..
0
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.

All Courses

From novice to tech pro — start learning today.