|
Hi
I need to run a query on a stock table. Each product may exist in either one or many warehouses. I am trying to find all items where the cost of the product is not the same in warehouse 01 and 02.
Example
Wh Prod Cost
01 A 10.00
02 A 10.00
01 B 64.00
01 C 98.00
02 C 10.00
In the example data above, I would like to get the last two rows returned.
This is what I have tried, but get lots of the same product code
SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY stockm.product, stockm.warehouse
|
|
|
|
|
I think you are close ... but try this:
SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse
I believe you included the scheme_stockm too many times in your original query.
|
|
|
|
|
Thanks David
I had to add warehouse in the two select statements for warehouse '01' and warehouse '02' since they were referred to in the outer SELECT as below and now it works.
SELECT A.warehouse, A.product, A.standard_material,B.warehouse, B.product, B.standard_material FROM
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT warehouse, product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B
WHERE (A.product = B.product AND A.standard_material <> B.standard_material)
ORDER BY A.product, A.warehouse
I also got mine to work by adding an additional 'join' as below on stockm.product = A.product, but I prefer yours, since the result is one record per error, whereas mine is two records per error
SELECT stockm.warehouse, stockm.product, stockm.standard_material FROM
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '01') AS A,
(SELECT product, standard_material FROM scheme_stockm WHERE warehouse = '02') AS B,
scheme_stockm AS stockm
WHERE (A.product = B.product AND A.standard_material <> B.standard_material AND stockm.product = A.product)
ORDER BY stockm.product, stockm.warehouse
|
|
|
|
|
Glad it worked ouy for you. 
|
|
|
|
|
Your query will only work if you have 2 warehouses. What happens if there are 3 or more? Try:
SELECT
a.warehouse,
a.product,
a.standard_material
FROM
scheme_stockm AS a
WHERE
EXISTS (SELECT *
FROM scheme_stockm
WHERE product = a.product AND
standard_material <> a.standard_material)
ORDER BY
a.product,
a.warehouse
|
|
|
|
|
Hi Michael
I had wondered about multiple warehouses 
In this particular case the product can exist in other warehouses, however the cost in the others does not need to be the same - only warehouse 01 and 02 must be the same.
I did test your query, and it works perfectly!
Thanks as I'm sure I'll need something like this in the future
|
|
|
|
|
Hi all,
If I were an employer searching for resumes which contain skill sets that match my search criteria, how is that done from a programmer's perspective. Can we actually go inside of text documents in a database and search their contents? If not, I will have to add a Skills field to the database so that it can be searched. Any suggestions will be greatly appreciated, thanks in advance.
|
|
|
|
|
ASPnoob wrote: Can we actually go inside of text documents in a database and search their contents?
That wouldn't be very efficient.
What type of documents do you expect, besides PDF, Word and the OpenOffice format? Whenever your end-user uploads a document, save it twice; once the original document, once a text-only version. Each of those formats can be "read" with the help of some libraries, and the text can be extracted. The text-version of the document can easily be searched, and once you find a "match", you display the original document.
For searching the text-versions of the uploaded documents, take a look at the Full Text Search[^] article on MSDN. If you're using another version than Sql2008, you can change it to match your specific version with a drop-down list at the top of the page.
Including a "Skills" field might still be beneficial, besides the FTS. Keep in mind that users will have different labels for the same role/function/job, and that managing those is the hardest part. For example, one of the major Dutch websites has different entries for "Software Developer", "Software Engineer" and the dutch word for "Software Developer".
Hope this helps a bit
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
When I wrote one of these in the 90s we required word docs only, then we compared each word in the document with a list of skills, if there was a kit it was added to the skills set for that document.
I imagine the tech has moved on in the last 20 years 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How much did the tech move forward for time-sheets?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
If it's actual files (blob) like word, .txt, excel etc you can use iFilter in sql. http://support.microsoft.com/kb/945934[^]
This will give you a fast search of content in your files.
--------------------
When Chuck Norris' dreams come true, your worst nightmares begin.
|
|
|
|
|
ASPnoob wrote: Any suggestions will be greatly appreciated
How many resumes? Hundreds, hundreds of thousands, Millions?
How responsive does it need to be? How many searches at one time?
Will this database and database server be used for anything else?
The answers to those questions impact how it can be designed.
|
|
|
|
|
Google 'Resume Parser'. No need to re-invent the wheel.
|
|
|
|
|
Astonishing, it would never have occurred to me that there seems to be a whole industry around parsing the crap we see in CVs. I wonder if they should be promoted as bullshit detectors!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Implement in the language of your choice:
boolean is_bullsh-t(string cv)
{
return true;
}
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005.
But in my code i need rows effected 0 . How to do that ?
|
|
|
|
|
You told the server not to count, so it returns -1.
Why do you "need" a count of 0?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
That's an old concept for returning a value when actually no value can be returned (e.g. due to an error).
Here, you told the server that it must not count the rows affected. When you now ask "how many rows were affected" it cannot return the "correct" value, you ought to expect an exception, but for compatibility with legacy applications, it returns a number.
Any whole number from 0 to inifinite would pretend to be the correct result. Hence, the server must not return any value in that range. And therefore, it returns -1: it is not possible that a negative number of rows was affected.
Get used to that old concept, you may encounter it more often.
|
|
|
|
|
Dim Ssql
Dim dbstatus
Dim rs
'*************
Ssql = " Select * from User_Access A Where A.User_pwd ='1234' "
set rs = Server.CreateObject("ADODB.RecordSet")
rs = DB.execute(Ssql,dbstatus)
Response.Write(dbstatus)
If dbstatus <> 0 Then
'Error code here
Else
'code here
END IF
'*************
Above is the code working ok . Actually this is written in dll. Now code will give error if "dbstatus" is other than 0 . Here I'm getting -1 always in my environment.
|
|
|
|
|
..and what's the problem with changing that code to reflect reality? There aren't 0 records affected, but an "uncounted" number;
If dbstatus <> -1 Then
'Error code here
Else
'code here
END IF
Then again, your If-Else wouldn't be very valuable, since it will always return -1 (unless an exception occurs or you raiserror)
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
..add a SELECT 1 WHERE 1 = 0 to your Sql-statement. IIRC and you execute a batch, only the affected rows of the last statement are returned.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
Because you are telling to server, "don't count the numbers of rows affected".
If you need the numbers of rows affected then try setting SET NOCOUNT OFF.
All the bset.
|
|
|
|
|
SET NOCOUNT OFF is working for insert delete update but for select statement it is giving -1 always .
|
|
|
|
|
Member 3487632 wrote: for select statement it is giving -1 always
What a shocker. It is what the doc[^] says: For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command... For all other types of statements, the return value is -1
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
