Modify SQL statement

[phoggy]

Hi all,

I could use some advice on the following: I have an inventory table and a related messages table, but the messages table stores messages from various places and the flag used to indicate where the message comes from is doc_category (in this case it would be 'ST' for stock).
So my question is, after I have linked the inventory and stock table, I need an additional SQL clause that says something like "..and where messages.doc_type = 'ST'. Now I cannot find any place in Crystal where I can modify the SQL statement. I thought that this should be a fairly simple to do, anyone can give me some suggestions on how this should be done.

Thanks for any help.

 

[lbass]

Go to report->selection formula->record and enter:

{messages.doc_type} = 'ST'

-LB

 

[synapsevampire]

Post your Crystal version and the database used.

There are different ways that SQL statements are prepared in Crystal, depending upon the version.

As an IT Manager, you must understand that when inquiring about any software in any forum, this basic information is critical to the solution.

If you use the Crystal GUI to add tables, then use Report->Selection Formula-Record (the exact menu selections varies by version as well) and place something like:

{table.stock} = "ST"

To see what is being passed to the database, use Database->Show SQL Query.

Modifying the SQL is dependent upon the version as well, in 8.5 and below you could modify part of what was passed, or use an ADO connection and paste in a basic SQL statement, in later versions you could use a Command Object (listed as Add Command under the data connection) and paste in SQL and create and insert parameters within the SQL.

-k

 

[phoggy]

OOps, I think I posted in the wrong Crystal forum, I don't have BusinessObjects Enterprise (if thats what this forum is for). I have Crystal XI Professional.

 

[phoggy]

Thanks for the fast answer guys, you guys are faster than the speed of light.

 

[phoggy]

Ah well, that won't work. Let me clarify a little. The stock table and messages table are linked by a left outer join, so I want all the parts in the inventory, but only those messages related to a part that are flagged as 'ST' in the messages table. If I add {messages.doc_category} = 'ST' to the record selection, I will get only those parts that have 'ST' in the messages table, and not all the parts. Hope this makes more sense.
BTW: I'm using MS SQL (and I'm not really an IT manager, just someone who occasionally uses Crystal reports. I made that mistake when registering and there was no way to change it)

 

[lbass]

Then don't use a record selection at all, and instead create a formula like:

if isnull({messages.doc_type}) or
{messages.doc_type} <> "ST" then "" else
{messages.message}

Place this in the detail section.

-LB

 

[phoggy]

Thanks for your help lbass. Do you mean conditionally suppress the detail and add the formula there? When I do that I get an error "The formula result must be a boolean"

 

[synapsevampire]

So what you are saying is that you want all messages, which is the equivalent to those with a null linked field. Then you add in a criteria on the nulled link field (child table) where the category = "ST", which means that you have conflicting requirements from a logic standpoint, you want the blank ones and you want category = "ST". Can't have both, right?

Again, please include your Crystal version and the database/connectivity used.

You don't understand how LEFT OUTER joins work, nor Crystal, so either read up on them, or provide the information required to provide your best solution.

-k

 

[synapsevampire]

Post your software and database if you care about doing this properly.

What I assume you are asking for is a common request, you only want those with "ST from the child, and all rows from the parent table.

This is SQL 101 stuff, and the solution offered will provide the information, but it does so via suppression of rows returned, you should first attempt to filter it on the database, and learning how LEFT OUER joins work would be beneficial.

Anwyay, I give.

-k

 

[phoggy]

I already included all the information above and yes I believe I do unterstand an outer join and no i do NOT want all the messages, I want ALL the parts (re-read what i posted above). This is not complicated. I have two tables, stock and messages. Messages contains notes for the entire system, that could be quotes, sales order, stock, etc., so there is a flag to indicate for wich document I want to show the messages for. In this case I need 'ST' for stock. There should be a simple solution for this.

 

[phoggy]

I already posted the software and db. Crystal Reports XI and MS SQL. Are you not able to see my posts above?

 

[phoggy]

MS SQL 2005 that is. I wish these posts were editable

 

[synapsevampire]

My apologies, I missed it in the middle there, I was tuning out on this post.

And yes, I understood the requirements.

In CR XI you can copy and paste SQL in as the database source by using Add Command listed below your SQL Server connection.

So a query should be used to handle this, as in:

select <fields> from
table1
LEFT JOIN (select messages, ID from table2 where category = "ST") table2 ON table1.ID = table2.ID

Using derived tables with the criteria already applied gets around this, I don't have SQL Server here, and I can't recall the syntax right now, but the above syntax is similar.

Another solution is to create a view of:

select messages, ID from table2 where category = "ST"

Then just join that View to the table using a left outer and you're done.

Ask your SQl Server dba about how this works. And no, you don't fully understand the sunbleties of how a LEFT OUTER joins work.

The big plus to CR 9 and abovre is that you can use REL sql in a command object, though I suggest using dataabse objects, the last choice should be using suppression in Crystal (though I do so on occasion, and often offer solutions based on that here to avoid teaching SQL).

-k

 

[phoggy]

Ok, can't use the command because I need to be able to update the datasource location and that can only be done by adding it to a repositry (which requires Businessobjects Ent.), can't do the view because i'm not allowed to mess with SQL server, so only option I have seems derived table. Don't know what that is and will investigate further. Thanks

 

[synapsevampire]

Derived table would be within a Command object, so you have the same concern.

Not sure what "can't use the command because I need to be able to update the datasource location and that can only be done by adding it to a repositry" means, you can update command objects without a repository...

-k

 

[phoggy]

Thanks, I did not know that I can update a command object without adding it to a repository. I searched the knowledge base and thats all I could find. How is it done?
THX

 

[lbass]

I think you didn't implement my suggestion correctly--I think you created the formula in the wrong place. I wasn't suggesting any suppression or record selection. You just add {inventory.parts} field to the details and then create the formula (field explorer->formulas->new):

if isnull({messages.doc_type}) or
{messages.doc_type} <> "ST" then "" else
{messages.message}

...and add it to the details section. All parts will be displayed, and those that have messages will have the message displayed.

This will return all records, but it is a simple way to accomplish what you are trying to do.

-LB

 

[synapsevampire]

Use the Database->Set Location.

Here's the old CR 10 whitepaper:

http://technicalsupport.businessobj...sliceId=&dialogID=5024826&stateId=1 0 5032085

LB is suggesting that you allow the database to return all rows, and then output nothing if isnull{messages.doc_type}) or
{messages.doc_type} <> "ST", otherwise output the field

SO you may return more data than required, but the result should be the same except that you might need to build conditional formulas for summaries.

-k