sql select statement against a csv file, using JET.OLEDB driver

Question

I'm trying to execute the following SQL statement (built and tested in SQL Server 2005)

 Select *
 From mytesttable
 where myPk in
 (
      select t3 from (
          select field1, substring(field3, charindex(":", field3),6) t2, min(mypk) t3
          from mytesttable
          group by field2, substring(field3, charindex(":", field3),6)
      ) t
  )

I know I can't use substring or charindex. So the innermost select looks like this in vbs:

strsql = "select mid(field3, instr(1, field3, ":")), min(mypk) from "
strsql = strsql & myCSVFileName 
strsql = strsql & myCSVFileName & " GROUP By mid(field3, instr(1, field3, ":")) "

This runs fine.

But when I try to add the next select to wrap the most inner select, it fails. The code looks like this:

strsql = "select mypk from ( select mid(field3, instr(1, field3, ":")), min(mypk)     from "
strsql = strsql & myCSVFileName 
strsql = strsql & myCSVFileName & " GROUP By mid(field3, instr(1, field3, ":")) )"

The error message I get is that there is

No value given for one or more required parameters

Any suggestions? Thanks.

score 1 · Answer 1 · answered May 10 '12 at 15:56

1

I don't know about Jet, but in SQL Server and other platforms, derived tables must be aliased. In other words, you'd need

... FROM (select ... ) AS YouMustProvideAnAliasForThisDerivedTable

Also be careful to use the same casing each time you mention a column (mypk vs. myPk), though that doesn't seem to be the problem here.

answered May 10 '12 at 15:56

Steve Kass

7,144
20
26

It is not necessary to alias derived tables when working with MS Access (Jet/ACE) – Fionnuala May 31 '12 at 13:00

score 0 · Accepted Answer · answered May 11 '12 at 13:35

There are a few errors in your string, but providing an alias for a derived table is not necessary in Jet.

You have repeated the line with the csv name, you have used double quotes when single quotes or two double quotes should be used, and you have not got a field called mypk to return. This works for me, but only if field 3 always contains text with a colon.

myCSVFileName = "Table.csv"
strsql = "select pk from ( select mid(field3, instr(1, field3, ':')), min(mypk) as pk from "
strsql = strsql & myCSVFileName & " GROUP By mid(field3, instr(1, field3, ':')) )"

sql select statement against a csv file, using JET.OLEDB driver

2 Answers2