Query Engine Error?

I have a report which displays some customer information and their transactions. The data is coming from an MS Access Database. The user must specify a customer either by customer Number or by their Last Name. I have C#.NET code which does this. Unfortunatley the data doesn't display on the report if the customer doesn't have any transactions. I asked another developer and he said to change the joins to right joins. The closest option i have is right outer join so i selected that. When i do this and run the report i get "Query Engine Error". It also displays the filepath to the rpt file. The database has been built up over a number of years and is not well normalized. Therefore i have more tables in the report then I would like. The database is quite large and would take alot of time to normalize that database and the program using the database would have to be altered as well. As you may have guessed, that code has also been developed over a number of years and again would take alot of time to change that. Is there an easy solution to my problem. I am not too experienced with Crystal Reports and any help would be greatly appreciated.Run the right join query in Access and see if it works

Query Empty node

Hello,

I have an XML file which contains different shops, where a certain node (<openinhours>) is empty in some occasions.

The node displays as <openinghours /> in my xml file in that case.

Now when I try to get the openinghour value of all the shops, it shows an error that I cannot atomize.

Is there any way to check if the node contains text by using the value or exist?

Thanks in advance!

I will try to clarify myself.

In my db, I have every shop in a different row, with columns: ShopID, ShopName, ShopXML. ShopXML contains the rest of the info of te shop, and is xml-field.

In that xml, i have something like this:

<shopinfo><openinghours>9a.m. - 18p.m.</openinghours> ...more xml ...</shopinfo>

However, in some cases this just is:

<shopinfo><openinghours /> ...more xml... </shopinfo>

I want to get all the openinghours where available from my xml (and I use .value),

but using

ShopXML.value('(/shopinfo/openinghours)[1]','varchar(max)') doesn't work because of the empty nodes.

How can I solve this?

Thank you!

|||

Thanks for providing more information. I'll have to ask some more questions before I can help you.

In the previous post you said that you got an error saying that you cannot atomize. Which is the exact error message that you get? I would assume that it is a static error, which means that the ShopXML is typed with an XML schema collection. Is this true? If it is, what's the type of openinghours element?

Thanks,

Adrian

|||

Yes I have a schema collection. The type is string.

When I disable the schemas it works fine. But if I choose not to use the schema's, will it affect the speed of querying data in my XML field?

|||

It's better to use the schema collection if you can.

I still don't understand what exactly doesn't work when you have a schema collection. Do you get an error? If yes, what error do you get and when? Or is it that you don't want to have empty openinghours in your result?

Regards,

Adrian