If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pass Through Query Translation
How do I translate this into a pass through query? I've been puzzled over
this for a day now? SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE ((Year([start_Date])=2008)); I keep getting an error '"YEAR": 'Invalid Identifier' Thanks. Kou |
#2
|
|||
|
|||
Pass Through Query Translation
Kou Vang wrote:
How do I translate this into a pass through query? I've been puzzled over this for a day now? SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE ((Year([start_Date])=2008)); I keep getting an error '"YEAR": 'Invalid Identifier' A pass-through query to What? SQL Server? Oracle? Something else? At first glance I would say that the server being sent that SQL does not have a function named "Year". Even if supported using an expression and testing its results in a WHERE clause is very inefficient. No index can be used and the entire table must scanned. Better would be something like... SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE [start_Date] = '2008-01-01' AND [start_Date] '2009-01-01' |
#3
|
|||
|
|||
Pass Through Query Translation
What is the database you are using as the data source?
Oracle MS SQL MySQL Some other database engine. Perhaps the the most universal would be to construct a date range for the pass through query SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE Start_Date = '2008-01-01' and Start_Date ='2008-12-31' In MS SQL (Transact-Sql) there is a Year function, so the query could be SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE Year(Start_Date) = 2008 There is also a DatePart function in Transact SQL SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE DatePart(yyyy,Start_Date) = 2008 John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Kou Vang wrote: How do I translate this into a pass through query? I've been puzzled over this for a day now? SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE ((Year([start_Date])=2008)); I keep getting an error '"YEAR": 'Invalid Identifier' Thanks. Kou |
#4
|
|||
|
|||
Pass Through Query Translation
Kou Vang wrote:
How do I translate this into a pass through query? I've been puzzled over this for a day now? SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE ((Year([start_Date])=2008)); I keep getting an error '"YEAR": 'Invalid Identifier' What is the database you're passing this through to? -- HTH, Bob Barrows |
#5
|
|||
|
|||
Pass Through Query Translation
Year is not a valid function in the database that you are trying to use. You
need to find out what kind of database and what function you can use in it. For example something like below should work with Oracle. WHERE to_number(to_char([startdate],'YYYY')) = 2008 ; or WHERE to_char([startdate],'YYYY') = '2008' ; -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kou Vang" wrote: How do I translate this into a pass through query? I've been puzzled over this for a day now? SELECT STORET1_TSRFDACT.START_DATE FROM STORET1_TSRFDACT WHERE ((Year([start_Date])=2008)); I keep getting an error '"YEAR": 'Invalid Identifier' Thanks. Kou |
Thread Tools | |
Display Modes | |
|
|