|
Unless I mistake what you're asking, that should be doable using a correlated subquery. Something along the lines of
SELECT *,
(SELECT BayLocation FROM NexGen.dbo.Bays AS bl WHERE bl.SerialNumber = ar.BaySerialNumber) AS [MyNewColumn]
FROM NexGen.dbo.AttemptedRun AS ar
WHERE BaySerialNumber = '1545300531' That is assuming that each bay is uniquely numbered on said field.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
If you look at the data in his screenshot you'll see that there are different values for BayLocation per run so this approach would give an error about multiple records in a subquery.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I didn't look; he said he wanted the entire record, and add a single column. Means it is also easy to debug the subquery by using a value from the database as example.
There will probably be multiple records with the same ar.BaySerialNumber, and I'm hoping that b.SerialNumber is the primary key (or at least uniquely indexed) on the Bays-table. Even if there's a one to one relation, that should work.
So, what the schema of the tables? Would be nice to have a CREATE script for both tables 
--edit
Asking the wrong person, and was already solved.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
modified 19-Jan-17 16:49pm.
|
|
|
|
|
Colleagues:
I have developed a product for Microsoft's SQL Server database. It is a source control tool very similar to what both Red-Gate and ApexSQL are offering but priced very affordably for individual developers. Unlike these two competing products, my software is designed with localized storage so that the files can be easily transferable to another machine that has my software installed.
The reason I developed this product was directly related to the many experiences I had in both my consulting assignments and full-time employment where I found that with the exception of those companies that had complete database groups, source control for applications was often poor and erratic while for database version control of SQL's object scripts it was non-existent.
On a number of occasions, while developing database applications and alongside that, the SQL procedures and modules to support these applications, I found my efforts hindered by others going in and changing those modules I was working on or even completely wiping them out without my ability to save them on a regular basis so that I could recover them if necessary.
Though my product has had several hundred downloads over the time I have been offering it I have received no sales. However, what has dismayed me even more is the lack of comments about my software that would give me an inkling as to why people who had downloaded the product chose not to pursue a purchase.
To date I have received one review by a download site that appeared to go through my software on a legitimate basis and liked it very much. Their only complaint was the "stodgy" interface, which I have since modernized.
I would like additional input to this effort to better understand if there is something in my work that is deficient or it is merely a marketing issue. As a result, I am offering 5 to 10 license keys to those developers that would take the time to review my product for me and give me some of the insight I am looking for.
My software is called, "SQL Server Source Control for Developers" and can be downloaded as a 30-day trial package from my business site... Black Falcon Software
These freely available keys will unlock the trial version making it a full version, which can be installed on up to 5 separate machines.
To obtain one of these licenses, simply contact me at my email address below and I will forward it to you.
If any of you would be willing to take the time out to work with my software and provide your comments to it as a result, it would be greatly appreciated...
Thank you...
Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com
|
|
|
|
|
|
Thanks for the heads-up.
I'll move my thread over as soon as I finish my current set of tasks.
Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com
|
|
|
|
|
I don't seem to be getting this right in VB. I get an error in casting the result as an Integer, in which the result may not exist.
ProductInfo_Inventory should be a join to get a count
Dim cStatus As Integer = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()
This is the whole thing. Not every item has a record in inventory for it's optional. So I'm not really sure how to properly handle this in VB
My new program is much better and in c#, so I seem to be doing better in that now.
pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
Dim cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).DefaultIfEmpty()
If (cStatus = 4) Then
dV = False
Else
dV = If(pValue > 0, True, False)
End If
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
modified 5-Jan-17 14:27pm.
|
|
|
|
|
The first question is, what is the type of currentStatus?. If it's non-numeric or beyond the limits of integer, the cast will fail.
Another thing is that if the current status is nullable (or reference type) then the default would be null which cannot be cast to integer.
The most accurate information is in the error message, but you didn't post that.
|
|
|
|
|
I had a customer walk in while posting and I hit post without much details, sorry about that; my bad.
This just determines if an item really exist, in case someone messed with the query string and put bad chars in there, or if an item was marked for deletion.
I went with this; cStatus is an Integer.
pValue = context.ProductInfo.Where(Function(m) m.PartNumber = p_PartNumber).Count()
If (context.ProductInfo_Inventory.Any(Function(m) m.partNumber = p_PartNumber)) Then
cStatus = context.ProductInfo_Inventory.Where(Function(m) m.partNumber = p_PartNumber).Select(Function(m) m.currentStatus).FirstOrDefault()
End If
If (cStatus = 4) Then
dV = False
Else
dV = If(pValue < 4, True, False)
End If
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
No problem! So everything works now? Just notice that FirstOrDefault may also return null which wuold cause an exception since cStatus is integer so depending on the situation you may want to use integer? and check if a value is actually returned.
|
|
|
|
|
I didn't think of that, I'll add it now
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Hi All,
I have a File System Watcher, which is monitoring file drops and renaming etc, which keeps the file information into a table, I have another application which executes the file related SSIS packages, and these two applications are Asynchronous. The problem is the Excel Connection Manager in the SSIS Package, when its reading the files, its creating another temporary file which is unnecessarily creating a record in the table. Can anybody please help me in how to overcome this scenario, I did a little bit by keeping a file exists check in the SSIS package that if the file exists or file already ran I am not going to run the package again on the File but still a record entry would be there on the monitors table which comes because of the Excel connection manager opening and reading the File.
Any help would be very helpful, a code snippet, a link or suggestion anything helps, thanks in advance
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
I did but the problem is the temp files are going to be created with $ prefix but the extension is same and another thing is that the temp files is going to be renamed back to the original file I think that's the reason why, it was hard for me to catch it and fix it. I tried it in hard way but only got 90% success, the way is to not insert any record into the monitoring table if same file name and location file comes within 15 minutes of previous drop then don't insert it into the monitoring table, but still for some SSIS package may take more that 15 minutes to read, then still those files are showing up in the monitoring table.
If there is any easy and proven approach that would be very helpful, thanks for the help buddy
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
hi,
I am having doubt in logical schema of database.
A logical schema
a)is the entire database
b)is a standard way of organizing information into a accessible part
c)describe how data is actually stored on disk
d)is design-centric database structure built to meet business requirements
My answer is "a" that is the entire database.Is it correct?
Please help............
|
|
|
|
|
|
Answer: none of the above, although d is maybe closest.
|
|
|
|
|
Thanks for confirming that 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Actually looks more like the output of one of the online buzzword generators. 
|
|
|
|
|
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution?
MySql 5.7.17 MySql database on remote server. (and local)
Windows C# Form VS 2010 .NET 3.5
private void txtSearchName_TextChanged(object sender, EventArgs e)
{
string srch = "Strain"; //txtSearchName.Text + "%";
this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch);
}
VS 2010 DataSet Query
SELECT ID, Company, Last Name, First Name, 'etc' FROM contacts
WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1
ORDER BY 'Last Name'
|
|
|
|
|
Errors = field names with spaces - this will drive you nuts as you now need to bracket the field names with []
try
WHERE [Last Name] like @param1 etc..
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Brackets is only on SQL Server, on MySQL you need to use the backtick `
Both supports the use of double quote " as that is the ANSI standard. You may need to SET sql_mode = 'ANSI_QUOTES' though.
|
|
|
|
|
Ah thanks, have not got used to the variations in MySQL yet and I would never find that wrinkle - spaces in field names are NEVER acceptable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses [name with blank] while MySQL `name with blank`.
Next, a comparison with LIKE expects a wild card. Strain does not contain a wild card, use Strain% instead - and make sure that you have data starting with that in one of the columns...
|
|
|
|
|
Bernhard,
Thank you. I don't know how I missed the backtick. I kept looking for something serious. I discovered it about an hour after posting. It became obvious when I executed queries with Workbench. That is a great tool! Well, I corrected that. As to the wildcard, I had that done properly in the query I was interested in, appending the % to the string before passing to the query. The web is filled with advice on wildcards, most is incorrect. I always get answers quickly here.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
