Solved

queries ?

Posted on 2015-02-16
7
78 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
  • 4
  • 3
7 Comments
 
LVL 76

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 76

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 76

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 76

Accepted Solution

by:
arnold earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now