|
Michael Potter wrote: You should be able to put your MID functions in the JOIN clause:
SELECT
[DATE] as tblS_Date,
MID([2ND REFERENCE_],1,9) as tblS_CellNr,
MID([Cell Number],2,9) as tblB_CellNr,
[Total___] as tblB_Total
FROM
Statement as tblS
INNER JOIN
Billing as tblB
ON MID(tblS.[2ND REFERENCE_],1,9) = MID(tblB.[Cell Number],2,9)
Hi Michael.
Thank you very much, the query worked perfect.
Regards
Playout
|
|
|
|
|
Hi,
I have two Access databases each with different tables in. There are two tables in each of them I need to join, using ado.net and vb.net...
costToCompany.mdb (tblEmployees)
Accounts.mdb (tblLoanAmount)
The relationship is EmployeeID in both tables.
How would the select/join statement look like?
Appreciate the help.
|
|
|
|
|
There are two other solutions to this...
1. You could just create another Access DB that has all the tables from both DB's as Linked tables, then just query that DB.
2. You could load data from both DB's into an ADO.Net DataSet, relate your tables and work on the data from there.
Steve
|
|
|
|
|
playout wrote: How would the select/join statement look like
Something like this should work, this is from memory so any errors you will have to work out. One work of caution, I did this a very long time ago, and the performance was terible. It was a temporary hack job in the first place and didnt last to long (thank goodness).
SELECT tblEmployees.*, tblLoanAmount.*
FROM [C:\costToCompany.mdb].tblEmployees
LEFT JOIN [C:\Accounts.mdb].tblLoanAmount ON tblEmployees.EmployeeID = tblLoanAmount.EmployeeID
Your better off as the other poster recommeneded selecting the tables and inserting them into one database. Or even better just combining the tables.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
I've created a Stored Procedure called GetFlashExportValues. See Below:
CREATE PROCEDURE [dbo].[GetFlashExportValues]
@WhereClause varchar(1000) = NULL
AS
DECLARE @sql varchar(5000)
SET @sql=
'SELECT DISTINCT
mr.GroupCode , md.PC, mi.RootControlName, md.YearNumber, fc.FISCAL_PERIOD_NUMBER , mm.ServiceType, md.StringMetricValue, md.NumericMetricValue
FROM
MetricIdents mi INNER JOIN MetricData md ON mi.RowKey=md.MetricIdentKey
INNER JOIN MetricMappings mm ON mi.RowKey=mm.MetricIdentKey
INNER JOIN MetricReportTypes mr ON mr.ReportTypeID=mm.ReportTypeID
INNER JOIN (SELECT DISTINCT FISCAL_PERIOD_MONTH, FISCAL_PERIOD_NUMBER from fiscalcalendar
WHERE SUBSTRING(FISCAL_PERIOD_NAME,1,3) = SUBSTRING(FISCAL_PERIOD_MONTH,1,3)) fc
ON md.MonthText=fc.FISCAL_PERIOD_MONTH
WHERE
mm.ExportToWareHouse=1 AND mm.RollupExport=0'
IF @WhereClause != NULL
BEGIN
SET @sql = @sql + ' AND ' + @WhereClause
END
EXEC (@sql)
GO
I then created a package to call this stored procedure and wanted to pass a value for the parameter @WhereClause from the command line. I was thinking of using the /A of dtsrun..
e.g. 'DTSRun /S "(local)" /U "user" /P "pass" /N "FlashExportValues" /A "@WhereClause":"8"="""MonthText=''August'' AND YearNumber=2006"""'
but I wasn't sure how to map it to the @WhereClause I declared in the code of the stored procedure.. can somebody show me how please? I'd really appreciate it. Thanks a bunch! 
|
|
|
|
|
Hello,
Is there ant in built query where in I can get all the field names from all the tables of my DB in SQL 2005
Regards,
Vipul Mehta
Regards,
Vipul Mehta
Chenoa Information & Software Services Pvt Ltd
|
|
|
|
|
Vipul Mehta wrote: Is there ant in built query where in I can get all the field names from all the tables of my DB in SQL 2005
If you want the column names you can use the syscolumns table or the INFORMATION_SCHEMA.COLUMNS view
|
|
|
|
|
I have a question about SqlDependency.
When using the SqlDependency class to get a notification of a change to a table, what is the best way to actually find out what data has changed? Is this even possible?
We are working on a stock trading system in which stocks are valued based upon notifications in the changes of the price, quantity, etc. Any light that you could shed would be helpful! Thanks a ton!
Ryan Heaney
rheaney@sgfallc.com
|
|
|
|
|
Not having much luck here either Ryan?
Look at the bottom of this page and you will see links to other forums...
You may have to create a TRIGGER to inform you of changed rows.
Steve
|
|
|
|
|
I am trying to move some records from table A to table B.
This is what I have thus far:
declare<br />
@PartID varchar(50),<br />
@SectionNumber varchar(50),<br />
@TopWidth float,<br />
@BottomWidth float,<br />
@boltDiameter float,<br />
@pickOrder int<br />
<br />
Select * from SSVLStandardBracingDesign<br />
set @PartID = SSVLSTandardBracingDesign.PartID<br />
set @SectionNumber = SSVLStandardBracingDesign.SectionNumber<br />
set @TopWidth = SSVLStandardBracingDesign.TopWidth<br />
set @BottomWidth = SSVLStandardBracingDesign.BottomWidth<br />
set @boltDiameter = SSVLStandardBracingDesign.BracingBoltDiameter<br />
set @pickOrder = SSVLStandardBracingDesign.PickOrder<br />
<br />
<br />
insert into BracingKits (PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder, <br />
S3TL29, S3TLVL, S3TLUL, S3TLMD, S3TLHD, S3TLVH, S3TLUH,S3R, S4TL,S4A, [1200TLWD],[1800TLWD], [1800SRWD],<br />
[2400SRWD],[3600SRWD] , [4400SRWD])<br />
VALUES (@PartID, @SectionNumber, @TopWidth, @BottomWidth, @boltDiameter, @PickOrder,<br />
0,1,0,0,0,0,0,0,0,0, 0, 0,0,0,0,0)
I need to set my variables to values from the table. I tried using commas but that didn't work, and my multiple set statements do not work.
What is the correct syntax?
Thanks!
|
|
|
|
|
|
Thank you for the link, that helped. I have another question though that it does not answer. I need to insert some 1's and 0's that are bit fields that don't come from the original table so they are not part of the select statement.
This is what I have now:
insert into BracingKits (PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder, <br />
S3TL29, S3TLVL, S3TLUL, S3TLMD, S3TLHD, S3TLVH, S3TLUH,S3R, S4TL,S4A, [1200TLWD],[1800TLWD], [1800SRWD],<br />
[2400SRWD],[3600SRWD] , [4400SRWD])<br />
SELECT PartID, SectionNumber, TopSread, BottomSpread, BracingBoltDiameter, PickOrder from SSVLSStandardBracingDesign
But all those that are bold need to be the 1/0's. How can I inculde this as part of the statement?
|
|
|
|
|
look up CASE statements in SQL that should work.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Just code in literals:
SELECT PartID, SectionNumber, TopSread, BottomSpread, BracingBoltDiameter, PickOrder, 0, 0, 0, 0, 1, 0, 0, 1, ... from SSVLSStandardBracingDesign
-- modified at 16:07 Thursday 17th August, 2006
|
|
|
|
|
INSERT INTO BracingKits SELECT PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0 from SSVLStandardBracingDesign
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Select @PartID = SSVLSTandardBracingDesign.PartID,
@SectionNumber = SSVLStandardBracingDesign.SectionNumber,
@TopWidth = SSVLStandardBracingDesign.TopWidth,
@BottomWidth = SSVLStandardBracingDesign.BottomWidth,
@boltDiameter = SSVLStandardBracingDesign.BracingBoltDiameter,
@pickOrder = SSVLStandardBracingDesign.PickOrder
from SSVLStandardBracingDesign
|
|
|
|
|
Hi,
I found that the Log file of my database has grown too fat. as i want to save some of the resources, i am thinking of deleting it.
but i think such a direct deletion will hamper my database. can you please tell me how to DELETE / REDUCE the size of that log file.
also, throw some light on how to read database log files.
Thanking You.
BSRK
|
|
|
|
|
What DB are you using, SQL Server?
ADDED: Apologies, I should have read the title of the question!!
You should look at your Recovery model. Maybe you could get away with a lower level of recovery.
You cannot delete the Transaction Log.
If you backup your log, it is automatically truncated. Then, you can shrink it. You can run backup with a TRUNCATE_ONLY option. Then shrink it.
Steve
-- modified at 14:37 Thursday 17th August, 2006
|
|
|
|
|
|
Simple one, I hope...
I want to query, say, a customer number and name and have the query return both as a single value for use in drop down list.
Is this possible? and if so what is the best way of doing this?
Thanks.
-- modified at 8:05 Thursday 17th August, 2006
|
|
|
|
|
Generally, SELECT field1 + field2 FROM table works. However, if the customer number is numeric, you will need to use SELECT CAST(field1 AS varchar(20)) + field2 FROM table
If you want gaps or additional chars such as [1] Customer name you do the following
SELECT '[' + CAST(field1 AS varchar(20)) + '] ' + field2 FROM table
Hope this helps,
Ian
|
|
|
|
|
1. Using ADO.NET Datareader a user extracts data from a database table
having 1000 rows.He closed his browser in between.
that is after fetching only 50 records.
What happens to the Datareader?will it remain connected?
and will fetch 1000 records and what after?
will garbage collector collect and dispose it soon?
2. A user fetched dtata from a database table
using Dataset(Disconnected records) for updation.
Another user deleted the table just after.
what happens when the first user try to update the table after changes? Error or Something else
3.where is session id stored in ASP? in IIS aerver or ASP Engine
Thanks & Regards
Vishal Sharma
vishalsharma556@yahoo.co.in
|
|
|
|
|
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
1. What do you think happens? Think through the process, the sequence of events and try and work out what is likely to happen and why. This is one of the most important skills in software development and will help you in many area such as designing, debugging and implementation.
2. This is easy to test yourself by making a couple of quick test applications. Another important skill is to be able to quickly create a small example showing how something works so that you have a small reference project from which you can learn.
3. Off topic for this forum. One of the best skills is to know where something is in the grand scheme of things.
|
|
|
|
|
Hi, how do i index a table in 2005?
When i right click on the table, full-text indexing is grey'd out(disabled).
How do i enable it?
Regards,
Tintin
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
