SQL Expresion - Syntax for SQL Server 2000

[NixyJ]

Hi All,

Crystal version 10
Microsoft SQL Server 2000

I am trying to create SQL expressions to enable me to sort groups on a formula in the group footer.

Thread767-874782 and Thread767-899216 both deal with this particular problem but I cannot get the required SQL expressions to save.

Sample Data - tblStock
SKU UnitsUploaded Units Sold
1234 10 0
1234 0 1
1234 0 3

I have a group on SKU and in the group footer I have the sellthru% which is sum(UnitsSold,SKU)/sum(UnitsUploaded,SKU). I want the report to sort the SKU groups, based on the Sellthru%.

I have followed the intstructions in the above threads (create 2 SQL expressions, one that sums upload, one that sums sold, and then create a formula that does the final calculation) but the SQL expressions keep generating syntax errors.

As an example, for the first expression to sum Units Uploaded I have

(
SELECT SUM(AKA.'UnitsUploaded')
FROM tblStock AKA
WHERE AKA.'SKU' = tblStock.'SKU'
)

Using single quotation marks generates a syntax error. If I change to double quotes (which is what Crystal itself uses if you select a field from the field tree) then the message I get is "The column prefix 'tblStock' does not match with a table name or alias name used in the query"

Please could somebody help me out with the correct syntax - I just cannot get this to work and after a couple of days I'm absolutely pulling my hair out. As ever this is urgent, and I would greatly appreciate any help to resolve this - I can't get any further until this is solved!

Many thanks in advance!
NixyJ x

 

[IanWaterman]

Why are you using single quotes around the table name. If you write a SQL statement in a query tool you do not need them.
Try
(
SELECT SUM(AKA.UnitsUploaded)
FROM tblStock AKA
WHERE AKA.SKU = tblStock.SKU
)

Ian

 

[NixyJ]

Hi Ian,

Many thanks for your really speedy reply - unfortunately I am still getting the same error message if I remove all quotes, single or double!

Any other ideas?
Nx

 

[lbass]

NixyJ,

Try adding a field name in the SQL expression expert and observe how the tables/fields are displayed. Then use this same syntax in the formula.

-LB

 

[cmmrfrds]

This is not valid SQL. When a table is aliased then all references to the table must use the alias.

What is this tblStock.SKU ?

SELECT SUM(AKA.UnitsUploaded)
FROM tblStock AKA
WHERE AKA.SKU = What do you want here?

Do you want?
WHERE AKA.SKU = ?yourParameter

 

[NixyJ]

Hi there,

Many thanks - I tried doing that as per a thread you helped me with some time back now.

When I do this it uses "tblStock"."UnitsUploaded"... I have tried using this sytax throughout the expression but am continuing to get the same error message.

I have however made a little progress. I found changing the expression to
(
SELECT SUM(AKA.upload)
FROM tblTrial, tblTrial AKA
WHERE AKA."SKU" = "tblTrial"."sku"
)

It seems that for some reason I can't refer to a table that has an alias declared by its actual name.... the error implies that tblTrial is not used in the query - because it's been decalred with an alias instead..

I no Longer got the error message and was able to save the expression..... BUUUUUTTTTTTT

When I put that field into the details section then the actual number its coming up with is wrong....

Actual data is

SKU UPLOAD SOLD
1234 10 5
1234 0 3
4231 20 15

the SQL expression in the detail section is showing 40(!) and therefore the max function is also showing 40 in the SKU group footer.

I'm not sure this is progress at all really - does this help??

Please keep trying, I appreciate all of your efforts!!
Nx

 

[NixyJ]

Sorry all... I hit Submit rather than edit Post

The new code should read

(
SELECT SUM(AKA.upload)
FROM tblTrial, tblTrial AKA
WHERE AKA.SKU = tblTrial.SKU
)


Many thanks,
Nx

 

[NixyJ]

Hi cmmrfrds,

Sorry for skipping over you earlier on - the site seems so screwy today - I didn't see you response until a minute ago.

Thanks for your comments above - I must admit to not being the greatest SQL bod in the world - I'm pretty much following Lbass' method verbatim without question... as per the threads I mentioned in my original post.

Lbass - please can you explain how the SQL you wrote is supposed to work?

I'm one step closer now....
(
SELECT SUM(tblTrial.upload)
FROM tblTrial
WHERE tblTrial.SKU = tblTrial.SKU
)

The total is now reading 30 which is the straight sum of everything in the UnitsUploaded field - this would be the report total though - I need this to sum to the SKU level only....

does this help anybody get any closer to fixing this?
Thanks everyone for your help so far.....
Nx

 

[lbass]

If the following syntax is giving you no errors:

(
SELECT SUM(tblTrial.upload)
FROM tblTrial
WHERE tblTrial.SKU = tblTrial.SKU
)

Then try:

(
SELECT SUM(AKA.upload)
FROM tblTrial AKA
WHERE AKA.SKU = tblTrial.SKU
)

-LB

 

[NixyJ]

Hi there,

Aggggghhhh ..... nope - still no joy - if I do that it gives me the Column prefix error again.

This is driving me mad... I'm in so much trouble if I can't solve this!!

Please can you explain why the SQL that I've got is summing to a report total as opposed to the SKU group?

Many Thanks, as ever I'm so grateful for your help...
NixyJ

 

[lbass]

I believe you need the alias to force the expression to evaluate at the group level. Is "tblTrial" your actual table name? You must use the actual table name in the expression ("AKA" is the alias table name).

-LB

 

[cmmrfrds]

NixyJ, in Crystal 10 there is a Command where you can write the SQL Statement and base the report on that SQL. This is what I do since the Crystal SQL expressions seems quite limited and convoluted to me.

 

[synapsevampire]

I agree with cmmrfrds, or just create a View with the subquery in it and base the report on that.

Remember to check the Database->Show SQL Query if you insist upon using a SQL Expression to see what is being passed.

There are more meaningful example shere, check:

thread149-780360
thread149-249863

This post has been beaten up pretty badly, hopefully reading existing posts will resolve, if not, post what you tried and any difficulties associated and I'll try to work it out.

-k

 

[NixyJ]

Hi all,

SQL expressions seem entirely unfathomable to me! I agree with you both cmmrfrds and synapsevampire. The only reason I started down this route was because of related posts that I'd seen before about how to solve my exact problem! I'm not entirely sure why Lbass' solution works for everybody else but mine is having none of it!

Thanks for pointing me to those related posts - I've picked up some good info from them.

I'm going to try to write a view as that is seeming to be the only possible way - all this just to sort a few groups!

The problem is simply trying to sort groups based on a formula that uses 2 summaries in the group footer...soemthing other reporting software seems to do effortlessly! I'll see how I get on with creating this view but may have to trouble you again for some help... meantime, if anyone has any ideas, I'll be grateful as ever!

Thank you so very much for all of the time you have all spent trying to help me, I really do appreciate it...
Watch this space.....
Nx

 

[fredp1]

Nixyj

If you ever get time, you could try and post the SQL that Crystal generates, Database, Show SQL query.

That way everyone can see exactly whats been passed to SQL Server. It may help with your view that you'lll create.

Fred

 

[vidru]

Nixy,

What type of connectivity are you using? With CR 8 and 8.5, I rarely had problems using SQL Expressions with subqueries, regardless of connectivity. But with CR 9, if I'm using a "Native" (OLEDB connection in 9.0), I get all kinds of errors, most of which shut Crystal down completely. If I use an ODBC connection with CR 9 to SQL Server, it usually plays nice with SQL Expressions.

-dave

 

[ChrisMan2]

Are you creating a SQL Expression Field or using a SQL Command? SQL Expressions do not need Select or From or Where and I don't think they allow you to do summary operations.

 

[fredp1]

Chris,

A SQL Expression can have Select/from statement, (inside brackets).
Essentialy when you use a 'select' statement in a SQL Expression, it is normally used as a correlated query.

Most examples about SQL Expressions are simple database functions to reduce the load on CR, but a SQL expression used as a correlated query can be of great help at times for those complex queries.

Just think of it as returning one value within the select part of the SQL statement

Fred