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."
(SELECT MIN(ABS(DATEDIFF(DAY,Invoi
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
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."
(SELECT MIN(ABS(DATEDIFF(DAY,Invoi
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
James
ASKER
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
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
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
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
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
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
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)
SELECT EuroRates."EuroRate"
FROM EuroRates, Invoice
WHERE EuroRates."ValidFromDate" <= Invoice."InvDate"
AND (Invoice."InvDate" < EuroRates."ValidToDate" OR EuroRates."ValidToDate" IS NULL)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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
(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
ASKER
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!
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!
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
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
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
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
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
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
Sorry , I forgot to remove "TOP 1" from the last sql it may just mask a problem
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
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
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 ?
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 ?
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
GracefBase can you post the sql for the main report ?
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.
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".
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
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".
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
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.
ASKER
Tried to share points out fairly as there were a lot of different comments and was a joint effort between different people.
Are you getting an error on the expression or just the wrong data?
Do you have a Euro Rate for each date?
mlmcc