Outer join query does not give values

[rrgkanth]

Hi,

My report uses 2 tables that have a right outer join.

I have a set of selection conditions checking for parameter values where i checked for null values in the inner table too.

I am trying to display one of the fields of the outer table.

If i enter parameter values, such that i get values for all fields, i am able to c the output.

If i enter a parameter value, where the inner table is null and outertable has values, i am not able to see the output.

I traced the query in sql, and sql returns expected results for that query - null for inner table and values for outer table.

anyone knows why this is happening? am i missing something here?

thanks for help,
rrgkanth.

 

[synapsevampire]

Interesting...

Please post:

Crystal version
Database/connectivity used (looks like SQL Server)
Example data
Expected output

My first advice is to use a View or SP on SQL Server instead of relying on the client side to pass the SQL, if you're allowed to create objects.

Sounds like a Left Outer might suit your needs better.

-k

 

[rrgkanth]

Hi,

Here are the details.

CR XI.
db - sql server

Parameters passed:
------------------
FRUID
RecordYear - formula for current year
FRUTypeCd
OfficerSetupId

My query being passed:
------------------------
SELECT "FRU_Officer"."FRUID", "FRU_Officer"."RecordYear", "FRU_Officer"."IndividualID",
"FRU_OfficerSetup"."OfficerSetupID", "FRU_OfficerSetup"."OfficerDesc", "FRU_OfficerSetup"."FRUTypeCd"
FROM "MMMS"."dbo"."FRU_Officer" "FRU_Officer"
RIGHT OUTER JOIN "MMMS"."dbo"."FRU_OfficerSetup" "FRU_OfficerSetup"
ON "FRU_Officer"."OfficerSetupID"="FRU_OfficerSetup"."OfficerSetupID"
WHERE
("FRU_Officer"."FRUID"=510 OR "FRU_Officer"."FRUID" IS NULL )
AND ("FRU_Officer"."RecordYear"=2005 OR "FRU_Officer"."RecordYear" IS NULL )
AND "FRU_OfficerSetup"."OfficerSetupID"=34
AND "FRU_OfficerSetup"."FRUTypeCd"='CHAPTER'

My output in SQL is:

FRUID RecordYear Officersetupid OfficerDesc FRUTypeCd
------ ---------- -------------- ----------- ---------
NULL NULL 34 Administrator CHAPTER

My report displays OfficerSetup.OfficerDesc.

If i have values that dont have null values above, I get an output.

If the query returns as above, I dont display anything.

Thanks,
rrgkanth.

 

[synapsevampire]

Might be simpler for us thick headed sorts were you to post what you have as data, and what you want.

If you have a query that works correctly in SQL Server, use a Command Object in CR and paste it in as the data source.

Anyway, I'd post that you want All of the rows in table X, and associated rows in table Y, if X.table = value or isnull or some such.

>My output in SQL is:
Meaning what is output as a result of the SQL passed from Crystal, or if you paste it into SQL Server?

>My report displays OfficerSetup.OfficerDesc.
OK< I see that

>If i have values that dont have null values above, I get >an output.
Your example shows that you do get nulls???

>If the query returns as above, I dont display anything.
Uhhhh, Okey-dokey shmokey.

-k

 

[rrgkanth]

I have 2 tables
FRU_Officer
FRU_OfficerSetup.

The setup tables is the main tables, contains officersetupid, frutypecd, officerdesc. The main aim of using that table is for me to retreive the officer desc.

The other table fru_Officer has data which basically refers to the officersetupid of the previous table, and lists other details about that.

So what i have to display is:
when i pass in parameters for fruid, frutypecd, officersetupid, i need to display the officername, desc etc for the current year. sometimes, there may not be an officer for that officersetupid for the current year.. but i still need to see the officerdesc..

>If i have values that dont have null values above, I get >an output.
What i mean by that is: if i enter a set of parameters such that there is no officer available, that is when i get null values for fields in the officer table, i do not see any output even for fields in officersetup table.

However, if i do enter a set of parameters such that there is an officer available, then I get an output.

Hope this clarifies my previous explanations.

Thanks,
rrgkanth.

 

[synapsevampire]

Ahhh, I think I'm starting to understand.

Honestly though, it's simple to show example data and the required output as a result, rather than trying to "explain" it. Technical documentation rarely uses your approach.

So you need to display officer name, yet you don't state which table that field is in. Do you understand my difficulties?

Example data
Expected output

Or just keep repeating the same process and expect a different result

You might try reversing the tables and using a Left Outer, that's what first came to mind when I read your descriptions.

-k

 

[rrgkanth]

Hi,

Tried changing to left outer join..

changed query is:
SELECT "FRU_Officer"."FRUID", "FRU_Officer"."RecordYear", "FRU_OfficerSetup"."OfficerSetupID", "FRU_OfficerSetup"."OfficerDesc", "FRU_OfficerSetup"."FRUTypeCd"
FROM "MMMS"."dbo"."FRU_OfficerSetup" "FRU_OfficerSetup" LEFT OUTER JOIN "MMMS"."dbo"."FRU_Officer" "FRU_Officer" ON "FRU_OfficerSetup"."OfficerSetupID"="FRU_Officer"."OfficerSetupID"
WHERE ("FRU_Officer"."FRUID"=510 OR "FRU_Officer"."FRUID" IS NULL ) AND "FRU_OfficerSetup"."OfficerSetupID"=34 AND ("FRU_Officer"."RecordYear"=2005 OR "FRU_Officer"."RecordYear" IS NULL ) AND "FRU_OfficerSetup"."FRUTypeCd"='CHAPTER'

Same query result as before and same output as before.

The name is in a different table, which i have chosen to ignore for the timebeing. This report will go into another one as a subreport.

If there is an officer i will show the individual id else i will show it as 'Office of' & officerdesc.
So I basically need to be able to see officerdesc even if there are nulls from the officer table.


example data:

fru_officersetup

officersetupid officerdesc frutypecd
--------------------------------------
34 admin chapter
39 secretary chapter

fru_officer
officerid recordyear fruid individualid officersetupid
----------------------------------------------------------
1 2005 510 34878343 39
2 2005 510 34898393 41
3 2004 510 34873483 34

for fruid = 510, recordyear = 2005(currentyear), officersetupid = 34

expected data
officerdesc individualid
---------------------------
admin null

i get
officerdesc individualid
---------------------------
null null


for fruid = 510, recordyear = 2005(currentyear), officersetupid = 39

expected data
officerdesc individualid
---------------------------
secretary 34878343

i get
officerdesc individualid
---------------------------
secretary 34878343

thanks,
r.