left join behaving like an equal join

[CJSilver]

I am using CR11.

I have my primary table, lets call it A, it has every order that we have ever created, opened, closed, does not matter.

Then I have table B.

Then I have Table C. It only has orders that we have worked on, it tracks how long different workcenters take to build their part of the job. So any new orders that have not been started yet will not show up in table C.

I have a left outer join From table A to Table B and
two left outer joins from table A to Table C.

This report is a scheduling report, it shows every order that we need to manufacture. Some may be in process some may not be started.

I only want to use table C to filter out jobs from each workcenter as they finish their part of the operation. I tried doing this by adding to the selection expert:
not(c.wc='1st END' and c.stat_flag='finished')

So if the "1st end" workcenter showed a status of finished I did not want to see it on the report. But when I do this it causes my entire report to only show information that exists in table C. I no longer see orders that are only in table A.

Here is my SQL statment:

 SELECT "A"."MO_NUMBER", "A"."COMPONENT", "A"."ITEM", "A"."ORDER_QTY", "A"."QUANTITY", "A"."SCHED_DATE"
 FROM   "FSDBMR"."dbo"."A" "A"


 SELECT "B"."TEAM", "B"."CUSTOMER NUMBER", "C"."WC", "C"."STAT_FLAG", "C"."MO_Number", "B"."PART_NO"
 FROM   "Silverado"."dbo"."C" "C" CROSS JOIN "Silverado"."dbo"."B" "B"
 ORDER BY "B"."TEAM"

I do not understand the SQL statement either, in the CR linking expert it shows the joins I mentioned above, but I do not see that in the SQL statement, I don't see table A mentioned at all in the join.

C. Johnson

http://www.SilveradoCable.com

 

[IanWaterman]

Your left outer is behaving like and equal because of this.

"I only want to use table C to filter out jobs from each workcenter as they finish their part of the operation. I tried doing this by adding to the selection expert:
not(c.wc='1st END' and c.stat_flag='finished')"

If you have a conditiobn on B the same will happen.

As soon as you put a condition on C it can no longer be null which it could be with a left outer from a to c.

Change select condition to

(isnull(c.wc or c.wc <> '1st END') and
(isnull(c.stat_flag or c.stat_flag <> 'finished')

If you have conditions on B make sure you do the same isnull test.

Your SQL statement is most odd, was that generated within Crystal?

Ian Waterman
UK Crystal Reports Consultant

 

[CJSilver]

Hello Ian,

Thank you for replying.

I thought that might be the case, but I could not see how to say that I wanted my condition, plus what was not in the table.

I tried your suggestion, but I keep getting an error message telling me that a ) is missing. Which there is, 2 are missing actually. But I tried it many different ways and it still gives me the same error message:

isnull(actual.wc or actual.wc <> '1st END') and
isnull(actual.stat_flag or actual.stat_flag <> 'finished')

(isnull(actual.wc or actual.wc <> '1st END') and
isnull(actual.stat_flag or actual.stat_flag <> 'finished'))

(isnull(actual.wc or actual.wc <> '1st END')) and
(isnull(actual.stat_flag or actual.stat_flag <> 'finished'))

These all give the same error, that ) is missing.

I thought the SQL statement was strange too. Yes it was generated by Crystal. In the linking expert it just shows that I have left outer join from A to B and 2 left joins from A to C. I certainly am not a SQL expert, and it seems to be working, but I don't understand the syntax at all, I don't understand why table A does not show up in the join.

C. Johnson

http://www.SilveradoCable.com

 

[GJParker]

Try This

isnull(actual.wc) or actual.wc <> '1st END' and
isnull(actual.stat_flag) or actual.stat_flag <> 'finished'


Gary Parker
MIS Data Analyst
Manchester, England

 

[CJSilver]

Hello Gary,

Thanks for responding.

I get the same error, ) is missing.

C. Johnson

http://www.SilveradoCable.com

 

[kskid]

Post your fomula.

 

[IanWaterman]

Sorry missed out a couple of brackets

(isnull(c.wc) or c.wc <> '1st END') and
(isnull(c.stat_flag) or c.stat_flag <> 'finished')

Which is pretty much what Gary suggested.

You seem to be joining two different databases together
"Silverado"."dbo" and
"FSDBMR"."dbo"

Whilst Crystal will allow you to do this, it should have complained, and could be the route of your problem. Do all 3 tables exist in the same database eg "Silverado"."dbo"

Try repointing all tables to "Silverado"."dbo" and see if
A Problem goes away
B Your SQL staement looks more sensible.

Ian

 

[CJSilver]

Hello Ian,

The tables are in 2 different databases, actually it is two different SQL servers. I know that Crystal says that you can't really do this, but I do it all the time and it is only under certain situations that I run into problems. When I was using CR8.5 I would run into problems when I had more than 3 tables. For some reason I would get ODBC errors. I do not get them in CR10 or CR11.

I tried linking the servers and creating a view in the SIlverado database that pointed to the other server, but when I did this the reports ran unbelievably slow, far to slow to be useful. I am not sure why, I would have thought it would have run better that way.

I am still getting the same error. Here is my entire selection formula.

{@dateconv} = {?Ending Date} and
(isnull(actual.wc) or actual.wc <> '1st END') and
(isnull(actual.stat_flag) or actual.stat_flag <> 'finished')

C. Johnson

http://www.SilveradoCable.com

 

[IanWaterman]

The SQL makes sense now, Crystal has generated two sub queries one of table A and the other of tables b and c.

However, I can not see what Crytal is doing behind the scenes as your joins are from a to b and a to C. and that is why the select statement is complaining. As it is obvious that there is no ) missing.

I think your only option is the slow route of buiding a view in one schema.

I am not good with MSSQL SQL but will look something like
create or replace view NEWVIEW( MO_NUMBER, COMPONENT etc...)
AS
SELECT "A"."MO_NUMBER", "A"."COMPONENT", "A"."ITEM", "A"."ORDER_QTY", "A"."QUANTITY", "A"."SCHED_DATE","B"."TEAM", "B"."CUSTOMER NUMBER", "C"."WC", "C"."STAT_FLAG", "C"."MO_Number", "B"."PART_NO"

FROM "FSDBMR"."dbo"."A" "A" outer join "Silverado"."dbo"."C" "C",
"FSDBMR"."dbo"."A" "A" inner join "Silverado"."dbo"."B" "B"

where
(isnull(c.wc) or c.wc <> '1st END') and
(isnull(c.stat_flag) or c.stat_flag <> 'finished')

isnull should be replaced with MSSQL isnull (Oracle it is {FieldName} is null)

YOu should then be able to point your report at this view.

Ian