[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

Inserting a sub query to MS Access generates an error "Reserved error (-3025); there is no message for this error."

Im using MS Access 2003 built in from Office 2k3
and im making SQL statements as stored procedures.

Generally, formally and actually, the statement below WORKS FINE

     insert into tblSample(field1, field2) select 'literal1', 'literal2';

But why is it this statement doesnt work (no syntax error, but when you try to run it, it generates the error

     insert into tblSample(field1, field2) select (select field1 from tblSample2), 'literal2';
 

this one is not working!
------------------------
INSERT INTO tblC ( Field1_ID, Field2_ID, NewDate, Notes )
VALUES (
     (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     [@NewDate],
     [@Notes]
);

while this one works!
----------------------
INSERT INTO tblC ( Field1_ID, Field2_ID, NewDate, Notes )
SELECT
     (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     [@NewDate],
     [@Notes]
FROM tblA;

Take note:
-------------
Why I do NOT like the SECOND SQL insert statement even it works:

  1. Actually, its a faulty, lets say tblA has 7 rows,
        you will be adding 7 rows at once!, it means you
        you should add TOP 1 to the select statement
  2. What if your tblA has no entry? you can't add anything man, and everything will be unpredictable
  3. tblA has no real relevance to the querry whaa
  4. Generally, its not a good programming practice

Assumptions:
----------------
1. No typographical errors
2. No mismatching fields
3. No db rules violations and what so ever
4. Sub query select statements returns only 1 row otherwise replace "SELECT" to "SELECT TOP 1"

My Questions:
----------------
1. How can I insert a sub query using VALUES clause instead of SELECT clause?
2. How can I insert a sub query using SELECT clause without the FROM clause?
    (hey its possible.. read again)
3. Why does this kind of scenario is happenning? Is it a bug or something?
4. What is the best workaround that fits my programming practice?
    (look for "Take Note:" section)

I will highly appreciate any efforts that will be given to my questions. Thanks.

0
anthon007r
Asked:
anthon007r
  • 10
  • 6
  • 5
  • +1
6 Solutions
 
Gustav BrockCIOCommented:
> But why is it this statement doesnt work (no syntax error, but when you try to run it, it generates the error

     insert into tblSample(field1, field2) select (select field1 from tblSample2), 'literal2';

Probably because it doesn't know where 'literal2' "comes from".

Try this:

     insert into tblSample (field1, field2) select (select field1, 'literal2' from tblSample2);

/gustav
0
 
anthon007rAuthor Commented:
hey cactus_data, tnx for the reply ^^
yes, you've got point there, but if we are going to assume that the statement

    insert into tblSample(field1, field2) select (select field1 from tblSample2), 'literal2';

is wrong, then the SQL statement below should also be wrong

    insert into tblSample(field1, field2) select 'literal1', 'literal2';

but the above statement just works fine even though we didnt specified where 'literal2' or even the 'literal1'  "came from"
and I think it is not really necesary to specify what table does a literal came from, because a literal is a value itself (please correct me if im wrong, im no master of SQL hehe)

and another point is that if we're going to have 2 sub queries, doing that kind of workaround is a bit of annoyance, you will be choosing on the 2 sub queries where you want 'literal2' to be included just like what you did (except that we had no choice because we only had one subquery on the first statement), that's why to prevent this, i seperated all the items which are not related just as 'literal1' is not related to 'literal2' because the two of them doesn't have in common.

now going back to this statement:

this one is not working!
------------------------
INSERT INTO tblC ( Field1_ID, Field2_ID, NewDate, Notes )
VALUES (
     (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     [@NewDate],
     [@Notes]
);

Maybe I should revise the 1st question to: if a sub query can be passed to the VALUE clause, then how could I be able to insert 2 sub queries in a VALUE clause? The same also applies to the 2nd question. (Pls refer to the original question above). As I know, you could treat a sub query as if it is column or ordinary field. (I had tried using the "AS" clause, and it didn't work).
0
 
ALL41_EggyCommented:
If you use the VALUES clause you are effectively constructing single rows. So your example

  insert into tblSample(field1, field2) select 'literal1', 'literal2';

will work fine as the select statement is one row long. The VALUES clause can only be used for single row appends and therefore the select statement is invalid as this will return a recordset, even it it's only one row.

As for your comments about the statement that works...

 1. Actually, its a faulty, lets say tblA has 7 rows,
        you will be adding 7 rows at once!, it means you
        you should add TOP 1 to the select statement
  2. What if your tblA has no entry? you can't add anything man, and everything will be unpredictable
  3. tblA has no real relevance to the querry whaa
  4. Generally, its not a good programming practice

1. If tblA has seven rows that match the criteria, but you only want one row then your databse design is at fault, if you only want to return a single row then your should identify that record by it's primary key, just by adding TOP 1 you cannot determine which row is returned unless you order them
2. If tblA has no rows then what do you want to do? You could replace a NULL with a value or, as you say, return 0 rows. Things won't be unpredicatable, the tblA being empty is a possibilty then you just have to make allowances for it. Without more info on the job in hand I can't help further there.
3. Note sure what you mean here, if tblA is irrelavent then why are you getting data from it?
4. It's diffcult to comment on this without more info.

If you want me to look at this more closely can you
a. supply me with a copy of a sample database or script it so I can recreate it.
b. Let me know what backend you are using, my testing will be done on SQL server 2000 with Access2003 as the front end (as close as I can guess to what you are using)
c. More importantly, give me some detail on what the process is trying to acheive, it may be that a little redesigning could make life a whole lot simpler.

Regards,

Al.



0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
anthon007rAuthor Commented:
Very impressive comment ALL41_Eggy, I appreciate it =)

Actually, i'm using asp.net 1.1 as my front-end and ms access 2003 as my backend db.
The details about the database is not that really necessary, but i'll give the exact "schema" of the tables related to our topic.
I just hope that its not the engine at fault..

To make it simple, ill only mention tables that are related to our discussion.

Table1            Table2             Table3
--------           ---------            --------
prim1_ID         prim2_ID         Prim3_ID  
fieldA              field1              prim1_ID
                                            prim2_ID
                                            fieldX

This is it, to make it simple. Originally there are two tables with many-to-many relationship.
So I converted it to (one-to-many) and (many-to-one) that's why we have three tables now.

fieldA and field1 are required and are also unique (pls don't ask why LOL) just for an example, because I really want to use the statement below: (pls refer to the original question)

this one is not working!
------------------------
INSERT INTO tblC ( Field1_ID, Field2_ID, NewDate, Notes )
VALUES (
     (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     [@NewDate],
     [@Notes]
);

Now you see, in MS Access 2003 you will not be able to save a query statement or any SQL statement unless it is free of syntax error. And this statement has no syntax error at all. But, when you try to open it (just starting it to execute) it pops up the error EVEN WITHOUT asking for the parameters first (It only means it does not popped the error "during" the actual execution of the append SQL statement.

Now, if you are going to revise the above statement to this:

this one IS working! (revised, having only one subquery
------------------------
INSERT INTO tblC ( Field1_ID )
VALUES (
     (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
);

Well, this statement works fine very fine, if only your going to ignore other fields on tblC to be left as blank (for simplicity).
It LOOKS like the sub query: "(SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field])"
"wants" to be "alone" on the VALUES clause.

I really have the feeling that my append SQL statement with more than 1 sub queries on the values clause is correct. Please help.

BTW ALL41_Eggy, im sorry if can't give you the script for you to recreate it, because I created the database not through scripts but by using the "Design View" of the MS Access 2k3 where there's a form and all you need is to input all the types of the fields of a table you are going to create. I even haven't tried it yet to SQL Server.

What im trying to achieve here is just to add a record to the tblC or the table3 on the statements above.
And I don't want to seperate the sub query to a new whole query and pass it to the append statement as a variable because I want to minimize my reconnection to the database. If you could put it to a single query, then why not do it?

ALL41_Eggy, actually you're very right on your statements:
    1. If tblA has seven rows that match the criteria, but ... *cut*
    2. If tblA has no rows then what do you want to do?... *cut*

but i have a comment on the item 4:
yes, I think tblA is irelevant there, its because all items on the SELECT clause:

      (SELECT tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
      (SELECT tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
      [@NewDate],
      [@Notes]

does NOT come FROM tblA.

Another point is, the FROM clause CAN actualy use just ANY available tables, becuase all of items there simply does NOT belong to any table at all.

And if im going to specify a table, the select statement will return ALL rows from specified table simple because there's no WHERE clause unless you append TOP 1 to the SELECT clause.

 To conclue:
     - using a FROM clause will validate the statement even it has 2 sub queries in it
     - using a FROM clause is unpredictable in a way you can't always assume that your select clause will return value
              because look, if each of all the 4 items in the select clause returns a value, it doesnt mean the select clause
              will also return a row BECAUSE the number of rows that select statement will return will be BASED ON
              the number of ROWS on the table specified on the FROM clause, that is why I hate to add the FROM CLAUSE.
              It is happenning because the 4 items on the select clause has nothing to do with the FROM clause's table,
              and I want to eleminate that unless you could specify a table that always has at least one row in it but this solution
              is a mess, you will be adding a dummy table with at least one row.

Any ideas there?
Thanx ALL41_Eggy for the reply ^^
0
 
Gustav BrockCIOCommented:
But did this work for you:

     insert into tblSample (field1, field2) select (select field1, 'literal2' from tblSample2);

or:

     insert into tblSample (field1, field2) select (select top 1 field1, 'literal2' from tblSample2);

If nothing else it should answer your question 2.

/gustav
0
 
anthon007rAuthor Commented:
cactus_data,
yes both statement works.. but you know.. what I really want to do is to insert two sub queries... not just a single one..
0
 
Gustav BrockCIOCommented:
I see.
Something like this?:

  INSERT INTO tblC
     ( Field1_ID, Field2_ID, NewDate, Notes )
  SELECT
     (SELECT Top 1 tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT Top 1 tblB.Field2_ID, 'literal1', 'literal2' FROM tblB WHERE tblB.Name2_Field = [@Name2_Field])
  FROM tblA;

/gustav
0
 
anthon007rAuthor Commented:
Exactly cactus_data, but without the LAST and farthest "FROM tblA" clause.

Because as you can see, the two sub queries are treated as a seperate columns and therefore, it is NOT relevant to tblA, that is the main reason why I want to eliminate the FROM clause. But removing the FROM clause will generate an error EVEN though the FROM clause IS JUST AN OPTIONAL if your selected field does NOT belong to any other table just like the statement below:

 insert into tblSample(field1, field2) select 'literal1', 'literal2';

and obviously, the two sub queries above are totally independent fields.
0
 
CanadeanCommented:
Anthon,

I'm still not entirely sure I understand what you are trying to acheive, not in terms of SQL statements but just logically. Could you give a small sample of data for table1 and table2 then the expected results in table3. Maybe more than one example would be helpful.

I'll check back in tomorrow or later today and get back to you.
0
 
Gustav BrockCIOCommented:
OK. Like Anthon I feel not quite sure what the real problem is. What damage does "FROM tblA" do? It could be any table.
Meanwhile, how about this with TOP 1:

 INSERT INTO tblC
     ( Field1_ID, Field2_ID, NewDate, Notes )
  SELECT TOP 1
     (SELECT Top 1 tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT Top 1 tblB.Field2_ID, 'literal1', 'literal2' FROM tblB WHERE tblB.Name2_Field = [@Name2_Field])
  FROM tblA;

or:

 INSERT INTO tblC
     ( Field1_ID, Field2_ID, NewDate, Notes )
  SELECT TOP 1
     (SELECT Top 1 tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT Top 1 tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     'literal1',
     'literal2'
  FROM tblA;

/gustav
0
 
CanadeanCommented:
BTW: Canadean = ALL41_Eggy !
0
 
anthon007rAuthor Commented:
Im very sorry..
Now that I come to think of that (and actually I had done it that way and is very similar to my SQL statements mentioned from the beginning).. maybe I should reduce my original question

FROM:

My Questions:
----------------
1. How can I insert a sub query using VALUES clause instead of SELECT clause?
2. How can I insert a sub query using SELECT clause without the FROM clause?
    (hey its possible.. read again)
3. Why does this kind of scenario is happenning? Is it a bug or something?
4. What is the best workaround that fits my programming practice?
    (look for "Take Note:" section)

TO:

1. How can I insert sub query using the VALUES clause and not the SELECT clause in MS Access 2k3.

0
 
Gustav BrockCIOCommented:
I don't think you can. Others have been fighting similar challenges to no avail:

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40931

Still, I don't understand why you can't use the Insert Into .. Select .. syntax.

/gustav
0
 
CanadeanCommented:
You simly can't use potentially multiple row results in a VALUES clause, it's not a bug, it's by design.

I still don't see why you think you need to do this, if the SELECT based query works then why not use it?
0
 
Gustav BrockCIOCommented:
OK, but that should the TOP 1 take care of - as long as you do have a unique index to order by.

/gustav
0
 
anthon007rAuthor Commented:
ok guys, im already satisfied by your comments. But one last question, could you give me links to official sites (e.g. microsoft.com) that states what I really want to do is impossible? Just to make sure that what im trying to do is really impossible and the workaround given here is the BEST solution. It will really help me to have a clear thought of why things can't be done that way, because you know, my SQL insert statements are syntactically correct or else i will not be able to save it as a stored procedure in MS Access 2k3. Thanx.
0
 
anthon007rAuthor Commented:
another thing, about my most recent comment, "..my SQL insert statements are syntactically correct.." what im talking about there is the statement below:

 insert into tblSample(field1, field2) select (select field1 from tblSample2), 'literal2';

that's without the FROM clause.

Please take a look just above of this comment. This is just an additional info. Thanx
0
 
CanadeanCommented:
The following is a quote from the MS Access 2003 Help File, under the topic "INSERT INTO Statement"

Quote:
Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.
End Quuote:

Note it states " a single new record" and the re-itterates it later saying "Use an additional stastemtn for each record you want to create"
0
 
anthon007rAuthor Commented:
Canadean, does this statement below violates the rules you found on the MS Access 2k3 Help File?

INSERT INTO tblC ( Field1_ID, Field2_ID, NewDate, Notes )
VALUES (
     (SELECT TOP 1 tblA.Field1_ID FROM tblA WHERE tblA.Name1_Field = [@Name1_Field]),
     (SELECT TOP 1 tblB.Field2_ID FROM tblB WHERE tblB.Name2_Field = [@Name2_Field]),
     [@NewDate],
     [@Notes]
);

I dont know how this statement violates the rules. The statement above only adds a new single record. As I said from the beginning on my assumptions that all sub query should be appended with TOP 1 clause if it returns more than one record or row.

Thanks.
0
 
CanadeanCommented:
Yes, Realise it's not clear from the help file but basically you cannot use subqueries like this.
Running the same code on SQL Server also fails giving a rather more direct error.

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

0
 
anthon007rAuthor Commented:
Thanx canadean. But if scalar expressions are allowed, then a scalar sub query should also be allowed then?
0
 
anthon007rAuthor Commented:
hey guys, any reference?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now