?
Solved

queries ?

Posted on 2015-02-16
7
Medium Priority
?
91 Views
Last Modified: 2015-02-17
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
Comment
Question by:MBHEY131
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 79

Expert Comment

by:arnold
ID: 40613403
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
 

Author Comment

by:MBHEY131
ID: 40614797
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
 
LVL 79

Expert Comment

by:arnold
ID: 40614826
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
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.

 

Author Comment

by:MBHEY131
ID: 40614889
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
 
LVL 79

Expert Comment

by:arnold
ID: 40614927
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
 

Author Comment

by:MBHEY131
ID: 40615133
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
 
LVL 79

Accepted Solution

by:
arnold earned 2000 total points
ID: 40615191
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question