• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 100
  • Last Modified:

queries ?

vb.net, vs2012
why won't this query work?

SELECT        CustomerID, Address1, Address2, CellPhone, City, County, CountyCode, DateOfBirth, DriversLicense, EmailAddress, Fax, FirstName, HomePhone, IsEmployee, Notes, PayrollDeduction, SocialSecurity,
                         WorkPhone, Zip, Do_Not_Call, IsBusiness, IsCurrent, BaseAddressID, ActiveDate, LastName, MiddleInitial, State, DriversLicenseState, LastWriteCount, DriversLicenseExpiration,
                             (SELECT        stock_num
                               FROM            stock
                               WHERE        customers.customerID = stock.PurchasedFrom) AS stkNo
FROM            Customers

I get an error in the Select command???
0
MBHEY131
Asked:
MBHEY131
  • 4
  • 3
1 Solution
 
arnoldCommented:
Try

SELECT       A. CustomerID, a.Address1, a.Address2, a.CellPhone, a.City, a.County, a.CountyCode, a.DateOfBirth, a.DriversLicense, a.EmailAddress, a.Fax, a.FirstName, a.HomePhone, a.IsEmployee, a.Notes, a.PayrollDeduction, a.SocialSecurity, 
                         A.WorkPhone, a.Zip, a.Do_Not_Call, a.IsBusiness, a.IsCurrent, a.BaseAddressID, a.ActiveDate, a.LastName, a.MiddleInitial, a.State, a.DriversLicenseState, a.LastWriteCount, a.DriversLicenseExpiration, b.stock_num as stkNO
                          
FROM            Customers a join stock b on customers.customerID = stock.PurchasedFrom

Open in new window

0
 
MBHEY131Author Commented:
I know about JOIN - However I am trying to avoid using it because the UPDATE, DELETE, INSERT etc. won't be automatically generated in the TABLE ADAPTER WIZARD of vb.net in visual studio 2012 as I understand it, although I don't understand why vb.net of vs2012 won't accept the SQL as I have written, It seems to adhere to all of the SQL rules as I understand them.
0
 
arnoldCommented:
I am not sure what you are saying.  If you know what you need to run, why be limited by what a tool built to assist one in a less complicated situation can or can not do?

what is the concern?

I think the result set of your query versus the join query will include entries where stkNO is null While the other will return only those entries that had a stkNO.

Missed the reference to vb,...
You might want to reorder the where clause. customers.customerID is unknown in the context of the table stock, try stock.PurchasedFrom=customers.customerID.

Though I am uncertain how customers.customerID is bound/limited what is the basis of the evaluation of select stock_num from stocks where stocks.PurchasedFrom=Customers.customer.ID
 
In the join it is limited on a per row basis by the customer.customerID such that it has a singular value.

run explain on your query and see what SQL reports on it.

What is the exact error that you get?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
MBHEY131Author Commented:
run explain on your query and see what SQL reports on it.

How would I do that???

=====================================================
What is the exact error that you get?

Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
======================================================

I am not sure what you are saying.  If you know what you need to run, why be limited by what a tool built to assist one in a less complicated situation can or can not do?

 what is the concern?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
What to you mean "Be Limited to"? No Believe me I do not want to be limited anymore than I already am.
My thought-be it right or wrong was that it was less complicated to let Vb.net and vs2012 do a lot of the work regarding, UPDATE, DELETE etc. as opposed to writing these SQL's manually. And to do that we could not use the JOIN clause on the SQL, as is my limited understanding. And the above query should suffice as I understand
==========================================================================================
I think the result set of your query versus the join query will include entries where stkNO is null While the other will return only those entries that had a stkNO.
+++++++++++++++++++++++++++++++++++++++++++++
If that is true, that's all the more reason to use My query as I do not need the "stkNo" null values at this point in my Project, but it could be a benefit in the long run and I did not know that.
====================================================================================
You might want to reorder the where clause. customers.customerID is unknown in the context of the table stock, try stock.PurchasedFrom=customers.customerID.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Get the same error.
===========================================================================
0
 
arnoldCommented:
You said that the reason you want this query versus using the join option is because of table updater wizard.

99.99% of your columns are from the customers table while one is from the stock table.

Depending on your database design what would an update/delete be if not only to the stock table or to the customers table.

I think you misinterpreted, your query if it worked would return stkno as null while the join will only return customer's who had purchased at least one stock.


Using SSMS (SQL Server management suite) can you run your query without an error?
Can you run the query within your code without the (select ) as stkNO?

The other option is prepend all your customers column with customers. and and stock columns with stock and see whether that resolves your issue.
0
 
MBHEY131Author Commented:
You said that the reason you want this query versus using the join option is because of table updater wizard.

 99.99% of your columns are from the customers table while one is from the stock table.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
That's because it states that you can use only return only 1 column in a sub-query - as I understand.
I need plenty more, but I can't get even 1 to return on the query. I was planning to add some more sub-queries in the future if I could get this to work.
==========================================================================================
I think you misinterpreted, your query if it worked would return stkno as null while the join will only return customer's who had purchased at least one stock.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
No - that's what I understood - my query has all the records and while I don't need them now, it would be beneficial in the future.
========================================================================================
Using SSMS (SQL Server management suite) can you run your query without an error?
 Can you run the query within your code without the (select ) as stkNO?
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Well Ok - more software to buy? vs2012 should take care of this in my humble opinion. it's SQL code is it not - I was unaware, again - that certain software does not accept all SQL code.
========================================================================
The other option is prepend all your customers column with customers. and and stock columns with stock and see whether that resolves your issue.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
tried that - same
==========================================================
could you please share about and possibly give an example of the EXPLAIN command you eluded to earlier.
==========================================================
Depending on your database design what would an update/delete be if not only to the stock table or to the customers table.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
not sure what your asking here - but there's plenty of reasons to update customer info and stock info simultaneously.
open RO's on sold units that are returned, etc.
==========================================
is this not allowed in vb.net?
0
 
arnoldCommented:
You have a local sql server installed, SSMS is a component of that you can get SSMSE (e stands for express)  from MS

for SQL 2012 http://www.microsoft.com/en-us/download/details.aspx?id=29062

for SQL 2005 http://www.microsoft.com/en-us/download/details.aspx?id=8961

Often the higher versioned ones can access the older versions. i.e. get 2012 and you should be able to access sql 2005, 2008 and 2012.

Lets try this, for the purposes of getting just this data out, will the join work? If so, use it.

deal with the other query as needed to address it.

It could be as simple as picking the wrong tool to use.

once you have ssms/e installed.  you would connect to the database in question, open a new query window. make sure you are using the correct database (use databasename)
then enter the
paste your query, and looking on the ssmse there is a tab/control that "include actual execution plan"
This when the query is run will display what resources were used for the query with the data,
there is a similar looking button that is labeled as "display estimated execution plan"

You could run two queries. select to get the customers. Then run the select for the stock and combine the data in your application.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now