ok, I'm going to be as descriptive as possible...
I'm using Crystal Reports pretty heavily, but in order to create a report you must statically reference a specific database. In this case, I'm connecting to a bunch of dbf's through the ODBC Visual Foxpro driver. The only way for me to not statically link to a data source, is to create a SQL query which I link to instead. Again, that goes through the ODBC driver, and so I need to use the alternate VFP commands instead of straight SQL.
In my company's system, we have sales journals that are renamed each month. The current month's sales journal is always the same name. All the other months' journals are kept track of in a parameters file. There, it's just a list of the file names. Something like "LGA 1 OT-01 SE-01 AU-01", etc. The -01's being the months.
I wrote the following to give me the previous month's journal name:
select substr (DATA1,10,5)
from parameters
where KEYS = 'LGA 1'
At that point I get "OT-01". In this case, that's October of 2001, or OT-01.dbf, the October sales journal. woohoo.
Now the hard part, I need to use that string to reference whatever the previous month's journal is. The end result being that I could do this once, and the previous month's history would always be accessible. So my first thought was to try to do it like a SQL subquery, something like:
select P_I_NUMBER
from (select substr (DATA1,10,5)
from parameters
where KEYS = 'LGA 1')
...but I get a syntax error. I've tried a million variations, and I've confused myself on the MSDN documentation, but I can't figure it out.
I'm hoping someone might have a fresh perspective.
I'm using Crystal Reports pretty heavily, but in order to create a report you must statically reference a specific database. In this case, I'm connecting to a bunch of dbf's through the ODBC Visual Foxpro driver. The only way for me to not statically link to a data source, is to create a SQL query which I link to instead. Again, that goes through the ODBC driver, and so I need to use the alternate VFP commands instead of straight SQL.
In my company's system, we have sales journals that are renamed each month. The current month's sales journal is always the same name. All the other months' journals are kept track of in a parameters file. There, it's just a list of the file names. Something like "LGA 1 OT-01 SE-01 AU-01", etc. The -01's being the months.
I wrote the following to give me the previous month's journal name:
select substr (DATA1,10,5)
from parameters
where KEYS = 'LGA 1'
At that point I get "OT-01". In this case, that's October of 2001, or OT-01.dbf, the October sales journal. woohoo.
Now the hard part, I need to use that string to reference whatever the previous month's journal is. The end result being that I could do this once, and the previous month's history would always be accessible. So my first thought was to try to do it like a SQL subquery, something like:
select P_I_NUMBER
from (select substr (DATA1,10,5)
from parameters
where KEYS = 'LGA 1')
...but I get a syntax error. I've tried a million variations, and I've confused myself on the MSDN documentation, but I can't figure it out.
I'm hoping someone might have a fresh perspective.
