SQL Command Connecting to New Database

[hoagieryder]

I have multiple databases on a server that represent different companies data. I wrote an sql command, but when I "update datasource locations" when I want to run a report on a different database it allows me to update all of the tables being used, but not the command. Therefore any data that was being pulled from the command is that from the prior database not the one that I am trying to update it with. Is there anyway to update the command so it is run for the new database?

Here is the SQL Command:

SELECT
Old.acctNumber, Old.acctBalance, Old.maturityDate, Old.typeCode, Old.AvgRate, old.BankCode, {?asofdate}
FROM

(SELECT
acctNumber, acctBalance,maturityDate, typeCode, classCode, AvgRate, BankCode
FROM Deposit
WHERE asOfDate= {?oldasofdate})
AS OLD

LEFT OUTER JOIN

(SELECT
acctNumber
FROM Deposit
WHERE asOfDate={?asofdate})
AS new

ON OLD.acctNumber =new.acctNumber

WHERE new.acctNumber Is Null
AND OLD.maturityDate >{?asofdate}

 

[Turkbear]

Hi,
Just to clarify, you have both tables and your SQL command in the report and the tables use the correct database but the command somehow does not?
What database and connection method are you using.
I have never seen a case where the SQL command could use a different connection than the tables in the report - I always assumed ( maybe wrongly) that the Command was just passed , as is, to whatever database was in use for the rest of the report objects - do you have to supply a new user/password - are the tables usually referenced by a fully qualified name based on the database ?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dgillz]

I have the exact same situation, I updated the location of tables in my report by using set datasource location, but there does not seem to be away of doing this with a SQL command.

Any help appreciated.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html