We help IT Professionals succeed at work.

help with crystal sql expression

Hi There Experts.
Please can you help, I cant get this to work correctly.
I have a table Invoice with a field invoice date.  I have created a table to hold euro exchange rates.
I want to use the exchange rate that is valid when the invoice was created to do calculations, so I thought best way was to create an SQL expression field, which I did as follows:
(SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidDate" =
(SELECT MIN(EuroRates."ValidDate")
FROM EuroRates
WHERE ABS(DATEDIFF(DAY,Invoice."InvDate",EuroRates."ValidDate"))=
(SELECT MIN(ABS(DATEDIFF(DAY,Invoice."InvDate",EuroRates."ValidDate")))
FROM EuroRates, Invoice))
)
I know I need to join the tables EuroRates and Invoice somehow but I can't seem to get it right!
Thanks in advance
Grace
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011
Commented:
This will be very slow query since you have multiple cross joins
Is it possible to change the structure of EuroRates table to have ValidFromDate and ValidToDate columns instead of just one column ValidDate. Then the query will be simple:

SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"  
AND Invoice."InvDate" < (EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)

ValidToDate will be equal to ValidFromDate for the next rate, for the last rate ValidTDate will be null

If you cannot change your table structure you may try to create a view which will expose ValidFrom and ValidTo dates for each rate.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
WHat fields are in the tables?

Are you getting an error on the expression or just the wrong data?

Do you have a Euro Rate for each date?

mlmcc
CERTIFIED EXPERT
Commented:
Having From and To dates, as vasto suggested, would probably simplify things.  If that's not an option, I guess ValidDate is when that rate takes effect, and you use that rate until the next ValidDate?  Assuming that you don't enter a new rate every day, do you do it on any kind of fixed schedule (eg. once a week or month), or just whenever?

 FWIW, your query seems way more complicated than necessary.  Off the top of my head, assuming that the rates are entered at irregular intervals and you want to use the most recent one from on or before the invoice date, I would think something like this:

(SELECT TOP 1 EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidDate"
ORDER BY EuroRates."ValidDate" DESC)


 Like I said, that's off the top of my head, but the idea is to select the rates from on or before the invoice date, sort them descending by ValidDate and use TOP 1 to get the most recent rate.

 James
CERTIFIED EXPERT

Commented:
Oh, I should add that Invoice."InvDate" is a field in your report, which is why I didn't include the Invoice table itself in the query.  The simplest/safest way to add that field to the query is to find it in the field explorer at the top and double-click it there to get CR to insert it into the query.  CR will add the SQL Expression to the report's main query as a sub-query, and they'll be linked together by the Invdate column.

 James

Author

Commented:
Hi there, thanks for all your comments.
Vasto, I put the extra field into the table as you suggested and put your query in.
I had to change the syntax as it came up with a syntax error so I changed the bracket:
(
SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"  
AND (Invoice."InvDate" < EuroRates."ValidToDate") OR (EuroRates."ValidToDate" IS NULL)
)

 but I am getting the following message when I put the field into the report:

subquery returned more than one value.  This is not permitted when the subquery follows =, < ...etc

James, I tried your query but it came up syntax error and I'm not sure why?

Cheers in advance
Grace
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
The problem is you have to find a single record in the EuroRates table that is the best match.
Your SQL evaluates the AND first then the OR so it returns the record you want and all records with a NULL ToDate.

Try it this way

(
SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"  
AND ((Invoice."InvDate" < EuroRates."ValidToDate") OR (EuroRates."ValidToDate" IS NULL))
)

mlmcc

CERTIFIED EXPERT

Commented:
What was the syntax error you got with my query?  On which part of the code?

 mlmcc's last post is partially correct.  As he said, a SQL Expression has to return a single row with a single value.  But your code, and his version of it, won't do that, because it joins to the Invoice table, so it will return at least one row for each row in Invoice (except any invoices that don't have a corresponding rate in EuroRates).  That's why I didn't include the Invoice table in my query.

 Also, based on your code, I guess you have From and To dates in EuroRates now?  Just checking.

 James
CERTIFIED EXPERT
Top Expert 2011

Commented:
Sorry, I put the bracket on the wrong place:

SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"  
AND (Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
As James states by including the Invoice table you are creating extra records.
I missed the 2 tables and was just fixing the error I found in the where clause.

Try this one

(
SELECT EuroRates."EuroRate"
FROM EuroRates
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"  
AND ((Invoice."InvDate" < EuroRates."ValidToDate") OR (EuroRates."ValidToDate" IS NULL))
)

mlmcc

Author

Commented:
Hi all,
Thanks for help so far but nothing is working.
With MLMCC and Vasto's formulas I get the same message - subquery returned more than one value.

Yes James as I put in my previous comment I added the extra field.  
Regarding syntax error, that must have been my mistake as it accepted your formula this time, however when I copy into the invoice, the result is £0.  The invoice date is 5/6/11.  The EuroRates has From Date 1/6/11, To Date 31/10/11, EuroRate 0.89.

Please help again.
Thanks
CERTIFIED EXPERT
Commented:
Are any of the rates in EuroRates actually 0?  I'm guessing "no", but if they are, then maybe my query is simply using the wrong rate.  If you have From and To dates now, that could be the problem, since my query didn't include a To date.

 Can you try my query directly in the db?  Just replace Invoice."InvDate" with a specific date for testing purposes.


 As for the error from mlmcc's query, do you have any date ranges in EuroRates that overlap?  That would explain the "more than one value" error.  If that's the case, you could try adding "TOP 1" and "ORDER BY" clauses like the ones in my query (That's why they're there.  Just in case there was more than one matching row), but the question is, if there is more than one row in EuroRates for the same date, which row do you want to get the rate from?

 James

Author

Commented:
Hi,
That's really weird - I did the query directly in the database as you said James with a fixed date and the result came up correctly (0.89)  
I then put the same fixed date into the sub query in Crystal and it was still showing the result 0.00 in the report.
 I then verified the database, refreshed the report data and it brought up the correct figure 0.89.  I then put your original formula back in crystal(except with validFromDate instead of ValidDate as the table has changed) and refreshed and shows 0.89 which is correct.  
So just to clarify there will only be one valid rate.  There will not be any overlapping dates.  
This is the formula I have put in combining James with the bit about valid to dates from Vasto and mlmcc's:

(
SELECT Top 1 EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate" AND (Invoice."InvDate" < EuroRates."ValidToDate" ) OR (EuroRates."ValidToDate" IS NULL)
ORDER BY EuroRates."ValidFromDate" DESC
)

Just have to work out how to share the points now!
Thanks all!
CERTIFIED EXPERT

Commented:
There's an error in that expression.  There should be () around the "< ValidTodate OR NULL" part (combining those two conditions).  vasto and mlmcc included them, but it seems that you missed them.

(Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)

 Without those (), the query would probably include every row where the invoice date was between the From and To dates, _and_ every row where ValidToDate is null.  If that's what you were using with mlmcc's/vasto's query, that probably explains the "more than one value" error.  If the dates don't overlap, you shouldn't need the TOP 1, once you add those ().  Try this:

(
SELECT EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate" AND
(Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)
)


 Of course, having said that, there's no harm in including the "TOP 1" and "ORDER BY", just in case.

 James

Author

Commented:
Hi James,
tried that last one and getting the error of 'subquery returned more than 1 value'.
No I copied and pasted Vasto and MLMCC's queries directly into the field so it wasn't missed brackets.
Still don't really understand why the error message is coming up, but the Top and order seems to fix it.
I've now added the extra brackets that were in MLMCCs query and tested is working fine.
Thanks a lot!
CERTIFIED EXPERT

Commented:
Too stubborn to just let this go ...  :-)

 OK, what if you try that last query I posted, without the TOP 1 and with Invoice."InvDate" replaced with a literal date, in the db?  Do you get more than 1 row?

 James
CERTIFIED EXPERT

Commented:
OK, I just thought of a potential problem with that last idea of mine.  There could be some dates that get more than one result and some that only get one, so the query may only return more than one result if you happen to try the right date.  I'd still give it a try, maybe with a few different dates, just to see if anything unexpected pops up.

 I know you said that there aren't any overlapping date ranges in EuroRates, but I don't see how else you could be getting more than one result for a given invoice date.  I assume that the idea is that each rate will have a ValidFromDate and ValidToDate, except for the current rate, where ValidToDate will be null.  When the rate changes, you'll change the null ValidToDate to the ending date for that rate, and add a new rate with a ValidFromDate and a null ValidToDate.

 James

CERTIFIED EXPERT
Top Expert 2011

Commented:
GracefBase it should not be possible to have more than 1 rate per one period. If the rate changes during the day use time in the date columns but you should always have one active record at specific time. Do you have more than one currency in this table ? You may need to include Currency ID in the query.

I am not sure how the sql will work in the given scenario. There is no restriction for InvoiceID so the sql :
(
SELECT Top 1 EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate" AND (Invoice."InvDate" < EuroRates."ValidToDate" ) OR (EuroRates."ValidToDate" IS NULL)
ORDER BY EuroRates."ValidFromDate" DESC
)

Will return the first rate for the list of invoices with their rates. Do you assume that the report will match Invoice."InvDate" with the invoice date of the currently active record  ? I am not sure it will be able to do it ( I don't know this for sure)

You may need to add Invoice table to the sql and InvoiceId to the WHERE clause:
(
SELECT Top 1 EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate" AND (Invoice."InvDate" < EuroRates."ValidToDate" ) OR (EuroRates."ValidToDate" IS NULL)
AND Invoice."ID" = @InvoiceID
ORDER BY EuroRates."ValidFromDate" DESC
)

I assume you have @InvoiceID parameter or formula


CERTIFIED EXPERT
Top Expert 2011

Commented:
Sorry , I forgot to remove "TOP 1" from the last sql it may just mask a problem
CERTIFIED EXPERT

Commented:
 vasto,

 This is (I think) being used as a SQL Expression in a CR report.  The Invoice."InvDate" field comes from the report data.  Basically, CR adds the Expression to the main report query as a sub-query, using the Invoice."InvDate" field to link the two.  SQL Expressions have to produce a single row with a single value, because the sub-query is added as an additional column in the main query, so it's producing a value for that column in each row of the main query.

 Hope that made sense.  :-)

 James
CERTIFIED EXPERT
Top Expert 2011

Commented:
James, If the SQLExpression is used as you explained the report SQL will be turned to a cursor which will execute a separate SQL ( the sql expression) per each record in the returned recordset. This is very expensive way to get some data which is available with a simple join. My assumtion is this is not the case.

However If this is the case the table EuroRates should be added to the main report and joined with the table Invoice ( there is many to one relation between Invoice and EuroRates) and SQLExpression should not be used.

GracefBase  where are you using this SQLExpression in the details section or in the headers ?
CERTIFIED EXPERT

Commented:
I don't think it's a cursor, but, then again, I've never used one, so I know little about them.  Basically, the report query ends up looking like this:

SELECT <list of main report columns>,
(
SELECT EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate" AND
(Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)
)
FROM <main report tables>
WHERE <record selection criteria>


 Adding EuroRates to the main tables of the report, instead of using a SQL Expression, is an interesting idea.  I guess that might work, as long as there will never be more than one matching row in EuroRates.  I was focused on trying to get the SQL Expression to work and hadn't thought of that.

 James

Author

Commented:
James, If I take the Top 1 out and put the query directly into the database with the date of 1st June 2011 it brings up two rates.
here are the contents of the table.  

ValidFromDate                             ValidToDate                             EuroRate
2011-05-01 00:00:00.000      2011-10-31 00:00:00.000      0.89
2011-11-01 00:00:00.000      NULL                                      0.90

Vasto, I will be using this field in calculations on every record on the invoice.  However there will just be one invdate per invoice.  I already tried joining the Eurorates table to the invoice table but it didn't work, maybe I joined it incorrectly.  
CERTIFIED EXPERT
Top Expert 2011
Commented:
GracefBase  let's fix first the SQL as it is now and then it may worth to just join the EuroRates table inside the report
I don't know what is the current SQL you are using in the SQLExpression but if it is tis one:
SELECT Top 1 EuroRates."EuroRate"
FROM EuroRates
WHERE
Invoice."InvDate" >= EuroRates."ValidFromDate"
AND (Invoice."InvDate" < EuroRates."ValidToDate" )
OR (EuroRates."ValidToDate" IS NULL)
ORDER BY EuroRates."ValidFromDate" DESC

The problem is comming from the OR clause. As it is now the SQL will return:
every record which satisfies the condition InvoiceDate between ValidFromDate and ValidToDate
AND
every record with ValidToDate = null

To avoid returning 2 records, change :
AND (Invoice."InvDate" < EuroRates."ValidToDate")  OR (EuroRates."ValidToDate" IS NULL)
to
AND (Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)

If you decide to move EutoRates to the main report you have to use the same join as in the SQLExpression.

CERTIFIED EXPERT
Top Expert 2011

Commented:
GracefBase  can you post the sql for the main report ?
CERTIFIED EXPERT
Top Expert 2011

Commented:
James I am not using SQLExpressions very often so please correct me if I am wrong .   SQLexpressions are useful  to get some information in a separate call and show it somehwere in the report or page header. If SQLexpression is part of the details section it will be executed as many times as records are returned in the details section . So if the report shows 100 invoices there will be 1 call to the database which will run 101 queries. If there is just one invoice it will not matter too much and will not worth the time to move the code from SQLexpression to the main SQL.
CERTIFIED EXPERT

Commented:
 GracefBase,

 You said earlier that you had () around the OR conditions.  If you do, I don't see how you could get those results.  The first condition is Invoice."InvDate" >= EuroRates."ValidFromDate".  If you replace Invoice."InvDate" with June 1, 2011, that condition is obviously not true for the second row, so it shouldn't be included.  If you don't have those (), then, as vasto said, that would explain it.


 vasto,

 I've never used SQL Expressions in my reports, largely because almost all of my reports use stored procedures, and you can't use SQL Expressions with a stored procedure (it's not even an option).  But from my experiments (mainly while trying to answer questions like this on EE), the SQL Expression is simply added to the main db query as a sub-query, as opposed to a query that CR passes separately to the db, so I don't think CR makes any separate "calls" to the db for it.  It seems the same as including a sub-query in a query that you write yourself.  I've used sub-queries a number of times in stored procedures and not noticed any performance issues.  IOW, I don't think it's a problem.

 FWIW, where you use the SQL Expression doesn't seem to make any difference.  I tried one and put it in a detail section and then moved it to a group header.  Either way, it was integrated into the report's main query the same way.

 James
CERTIFIED EXPERT
Top Expert 2011

Commented:
James0628 , I am in exactly the same situation with SQLExpressions. Most of my reports are based on SP and SQLExpression cannot be used. However is some legacy reports based on SQLs ssqlexpressions were helpful. For eexample getting the rate without joining the expression with the report data but using the report parameters will run the sqlexpression separately from the main report.

Author

Commented:
Tried to share points out fairly as there were a lot of different comments and was a joint effort between different people.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.