|
hi
I have got a table as follows
col1 col2
---- -------
1 1183.74
2 1188.05
3 1202.08
4 1211.92
5 1213.55
6 1213.45
7 1213.54
8 1204.92
9 1210.13
10 1209.57
11 1205.45
12 1194.65
13 1194.22
14 1203.21
15 1205.72
16 1203.38
17 1198.68
18 1188
19 1189.24
20 1182.81
21 1177.07
22 1190.25
23 1191.17
now i want a query which yields the following results
here col3 for record 1 is calculated as follows
(1188.08/1183.74)-1)*100=0.364100225
col1 col2 col3
---- ---- ----------------
1 1183.74 0.364100225
2 1188.05 1.180926729
3 1202.08 0.818581126
4 1211.92 0.134497327
5 1213.55 -0.008240287
6 1213.45 0.007416869
7 1213.54 -0.710318572
8 1204.92 0.432393852
9 1210.13 -0.04627602
10 1209.57 -0.340616913
11 1205.45 -0.89593098
12 1194.65 -0.035993806
13 1194.22 0.752792618
14 1203.21 0.208608639
15 1205.72 -0.19407491
16 1203.38 -0.390566571
17 1198.68 -0.890980078
18 1188 0.104377104
19 1189.24 -0.540681444
20 1182.81 -0.485285042
21 1177.07 1.119729498
22 1190.25 0.077294686
23 1191.17 
|
|
|
|
|
Looks like homework...
Wout Louwers
|
|
|
|
|
hi
I want to know how to communicate with database over the internet. What type of encrypting should I use.
shelly
|
|
|
|
|
Although it is possible (if your firewall allows it) I wouldn't really recommend it. You can, of course, connect via a VPN which would make it appear as if it is on a local network. VPN software will encrypt the data for you.
If the communication is at an application level I would suggest looking into creating a web service to communicate with the database. This uses HTTP (so you can use HTTPS) and also it means that you define exactly the type of queries that can be run by virtue of the web methods that you expose.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello,
Please advise on how I might be able to make this sproc work...
declare @TrainerID int
select TrainerID, LastName, FirstName, City, State, Country, r.code, l.description, p.Code from trainers as t
inner join Regions as r
on t.region = r.regionid
inner join Languages as l
on t.Language1 = l.LangID
--and t.Language2 = l.LangID
--and t.Language3 = l.LangID
inner join products as p
on t.product1 = p.ProductID
--on t.product2 = p.ProductID
--on t.product3 = p.ProductID
--on t.product4 = p.ProductID
where TrainerID = @TrainerID
The situation is that I have designed in the Trainers (t) table multiple columns of Languages and Products with properties as Int which stores values based on their respective LanguageID and ProductID from the Languages and Products (definition) table.
The dilemma is on the result, I would like to see the actual codes and not the ID key of each of the multiple columns coming from the definition tables.
Thanks
Jay
Feng Screwed - the art of moving to a new place because the old place is just too messy to clean.
|
|
|
|
|
|
Please disregard the previous message, I was able to figure it out.
I do have another question though. What if l3 is null from db, then how do evaluate it in sproc so that if it is len = 0, then it is left blank instead of not returning any result.
Thanks 
|
|
|
|
|
The Trainer table is somewhat denormalised (it partially meets the 3rd normal form criteria) and it would be better if there was a many-to-many join between the Trainer table and the Languages table, and also between the Trainer table and the Products table.
To implement a many-to-many join you add an intermediate table with a compound primary key and two foreign joins. This is actually a lot simpler than it sounds. Simply put the intermediate table contains the primary key from each side of the join, for example the primary key from Trainer (one of the foreign keys) and the primary key from Languages (the other foreign key). All those columns together make up the primary key of the intermediate table so that the only columns in the intermediate table make up the compound primary key.
So, you create two new tables like this
/----------------------\ /---------------------\
| TrainerLanguage | | TrainerProduct |
|----------------------| |---------------------|
| PK, FK1 | TrainerID | | PK, FK1 | TrainerID |
| PK, FK2 | LanguageID | | PK, FK2 | ProductID |
\----------------------/ \---------------------/ and populate them with the values that were in the Language and Product columns in the Trainer table.
Once you have that set up then your queries can be much simpler and you don't have to worry about nulls and the schema will be much more flexible than it is at the moment.
I don't know your level with databases so I hope that this is not too overwhelming.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello everyone I am working on a small project I'm sorry if I do not know all the right phrases and words that goes with the programming language I'm still learning with that behind us. I need help with my project had created a database with access and now I want to fill it with data through the form that I have created I have implemented the fill method if I go into the database and add and name address telephone number eccentrics and when I go back to my form and hit the load button to fill method works I conceded that what I can not get is my form to write the new data back to the database I am using VB.net and yes I went to server Explorer and made a connection to the database. adapter, data connection, dataset what I don't know is what is my code supposed to look like to get my data to write back to the data source
|
|
|
|
|
what programming language are you using?
|
|
|
|
|
I get this error:
Description Line 1: Incorrect syntax near '='.
Explantation Syntax error in reserved words!
SQL Statement
SELECT m.[Firstname], m.[Lastname], m.[Address], m.[PostalCode], p.[PostalOffice], m.[Email], m.[MemberImage], m.[categoryid]
FROM [Member] m, [Post] p WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=
I use this code
sqlStatement = ""<br />
sqlStatement = sqlStatement & "SELECT m.[Firstname], m.[Lastname], m.[Address], m.[PostalCode], p.[PostalOffice], m.[Email], m.[MemberImage], m.[categoryid] "<br />
sqlStatement = sqlStatement & "FROM [Member] m, [Post] p "<br />
sqlStatement = sqlStatement & "WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=" & intMemberId
Have someone poposal whats it's wrong 
Thanks for any help
Erik Gjertsen
|
|
|
|
|
|
intMemberId is not a proper SQL variable.
Try @intMemberID.
See Colin's comment above - I have to second that (LOUDLY).
|
|
|
|
|
Change the intMemberId with number,
Erik Gjertsen wrote:
sqlStatement = sqlStatement & "WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=2"
From there you will probably find out what went wrong.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
As part of my "newbee" problems I discovered I can't compile visual basic as cut and paste from examples:
The example:
Dim selectCMD As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
has be modified as such in order to compile:
Dim selectCMD As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
Am I missing some header file or something else?
Thanks,
Ilan
|
|
|
|
|
|
I have a number of SQL queries to execute on an Orcale database, all of which are based on a common subset of a large table. I have the SQL to create a temp table then use the temp table to drive the first of these queries, and this works through Oracle SQL worksheet.
I'm trying to get this to work through ADO.NET, by sending a series of SQL commands via OleDbCommand.
This is the SQL:
DROP TABLE ODSWIP;<br />
<br />
COMMIT WORK;<br />
<br />
CREATE GLOBAL TEMPORARY TABLE ODSWIP<br />
ON COMMIT PRESERVE ROWS<br />
AS SELECT <br />
<br />
RMAM.RMA_NBR AS RMAM_RMA_NBR, <br />
RMAD.LN_NBR AS RMAD_LN_NBR, <br />
SORD.ORD_NBR AS SORM_ORD_NBR, <br />
SORD.LN_NBR AS SORD_LN_NBR, <br />
RMAD.OUT_PART_NBR, <br />
SORD.PRODUCT_ID, <br />
RMAD.OUT_TRACE_ID, <br />
RMAD.DT_RECEIVED, <br />
SORD.DT_CREATED, <br />
ZORD.ORD_NBR AS ZORD_ORD_NBR, <br />
ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR<br />
FROM RMAM, RMAD, SORD, ZORD<br />
WHERE RMAM.RMA_NBR=RMAD.RMA_NBR<br />
AND RMAD.RMA_NBR=SORD.ORD_NBR<br />
AND SORD.PRODUCT_ID=ZORD.PART_NBR<br />
AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR<br />
AND SORD.REC_CD='DT'<br />
AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' <br />
AND TRIM(SORD.CNCL_ITM_RSN_CD) Is Null<br />
AND RMAD.DT_RECEIVED Is Not Null <br />
AND TRIM(SORD.INVOICE_ID) is null <br />
AND ZORD.PART_NBR Not Like '*-R *'<br />
AND ZORD.SERV_ORDER='Y';<br />
<br />
COMMIT WORK;<br />
<br />
SELECT RMAM.RMA_NBR, SORD.LN_NBR, TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR), <br />
SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE, ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, <br />
OORD.ACTUAL_OPEN_DATE<br />
FROM ODSWIP, RMAM, RMAD, SORD, ZORD, OORD<br />
WHERE ODSWIP.RMAM_RMA_NBR = RMAM.RMA_NBR<br />
AND ODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR AND ODSWIP.RMAD_LN_NBR = RMAD.LN_NBR<br />
AND ODSWIP.SORM_ORD_NBR = SORD.ORD_NBR AND ODSWIP.SORD_LN_NBR = SORD.LN_NBR<br />
AND ODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR<br />
AND ZORD.ORD_NBR=OORD.ORD_NBR AND ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR<br />
ORDER BY SORD.ORD_NBR DESC;
My problem is the COMMIT WORK underlined. I *know* this is necessary to actually populate the temp table, and I don't get an error from executing this command using ExecuteNonQuery. However, the results from the final SELECT as returned through OleDbDataAdapter gives me the column headers - i.e. an empty dataset, as if the COMMIT WORK didn't.
Apologies for the long post, but here's the .net code I'm using:
Private Sub btnStartDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartDate.Click<br />
' Query the database and cache the result<br />
Dim myConn As OleDbConnection<br />
Dim strconn As String = Session.Item("optSrcDBConnect")<br />
<br />
Dim dcSQL As OleDbCommand<br />
Dim strSQL As String<br />
Dim drSQL As OleDbDataReader<br />
<br />
WriteLog("Open connection")<br />
Try<br />
myConn = New OleDbConnection(strconn)<br />
myConn.Open()<br />
Catch ex As Exception<br />
WriteLog("Error opening connection " & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "DROP TABLE ODSWIP"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
' Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "CREATE GLOBAL TEMPORARY TABLE ODSWIP ON COMMIT PRESERVE ROWS AS SELECT /*+ ORDERED */ RMAM.RMA_NBR AS RMAM_RMA_NBR, RMAD.LN_NBR AS RMAD_LN_NBR, SORD.ORD_NBR AS SORM_ORD_NBR, SORD.LN_NBR AS SORD_LN_NBR, RMAD.OUT_PART_NBR, SORD.PRODUCT_ID, RMAD.OUT_TRACE_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, ZORD.ORD_NBR AS ZORD_ORD_NBR, ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR FROM RMAM, RMAD, SORD, ZORD WHERE RMAM.RMA_NBR = RMAD.RMA_NBR AND RMAD.RMA_NBR=SORD.ORD_NBR AND SORD.PRODUCT_ID=ZORD.PART_NBR AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR AND SORD.REC_CD='DT' AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' AND TRIM(SORD.CNCL_ITM_RSN_CD) Is Null AND RMAD.DT_RECEIVED Is Not Null AND TRIM(SORD.INVOICE_ID) is null AND ZORD.PART_NBR Not Like '*-R *' AND ZORD.SERV_ORDER='Y'"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "COMMIT WORK"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "SELECT /*+ ORDERED */ RMAM.RMA_NBR, SORD.LN_NBR, TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR), SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE, ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, OORD.ACTUAL_OPEN_DATE FROM ODSWIP, RMAM, RMAD, SORD, ZORD, OORD WHERE ODSWIP.RMAM_RMA_NBR = RMAM.RMA_NBR And ODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR And ODSWIP.RMAD_LN_NBR = RMAD.LN_NBR AND ODSWIP.SORM_ORD_NBR = SORD.ORD_NBR AND ODSWIP.SORD_LN_NBR = SORD.LN_NBR AND ODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR AND ZORD.ORD_NBR=OORD.ORD_NBR AND ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR"<br />
WriteLog(strSQL)<br />
Dim daOrders = New OleDbDataAdapter(strSQL, strconn)<br />
'WriteLog(txtSpecificQuery.Text)<br />
If myDS.Tables.Contains("SpecificQuery") Then myDS.Tables("SpecificQuery").Dispose()<br />
daOrders.fill(myDS, "SpecificQuery")<br />
WriteLog("Cincom specific extract complete" & vbCrLf & myDS.Tables("SpecificQuery").Rows.Count & " rows returned", EventLogEntryType.Information)<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL" & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
End Try<br />
<br />
Session.Item("myDS") = myDS<br />
Radiobutton8.Checked = True<br />
DataGrid2.CurrentPageIndex = 0<br />
DataGrid2.DataSource = myDS.Tables("SpecificQuery")<br />
DataBind()<br />
End Sub
Any suggestions gratefully accepted!
|
|
|
|
|
i have a table with 8 columns. the first seven columns form a key.
i need to select 7 of them, and join another table :
SELECT mt.c1, mt.c2, mt.c3, mt.v4, mt.c5, mt.c6, mt.c7, ot.ot5 MyTable mt
LEFT OUTER JOIN OtherTable ot ON
mt.c1 = ot.ot1
mt.c2 = ot.ot2
mt.c3 = ot.ot3
mt.c4 = ot.ot4
WHERE mt.c1=0
i'll get something like this:
c1 c2 c3 c4 c5 c6 c7 ot5
------------------------
1 1 1 1 1 1 1 91
1 1 1 1 1 1 2 92
1 1 1 1 1 2 1 93
1 1 1 1 1 3 1 94
simple so far.
since the the key to MyTable is composed of 7 columns, it's possible (and likely) that for any given row, the first 6 of those columns will match the first 6 from some other row (and that's why the key is composed of 7 columns, obviously). ex., see the first and second rows in my little result set.
but, i need to get rows such that the first 6 columns in any row will be unqiue in the result set. in other words, if two or more rows match in the first 6 columns, i need to discard all but the first (or all but the last, it doesn't matter, as long as there is only one):
c1 c2 c3 c4 c5 c6 c7 ot5
------------------------
1 1 1 1 1 1 1 91
1 1 1 1 1 1 2 92 <---- eliminate this row
1 1 1 1 1 2 1 93
1 1 1 1 1 3 1 94
so, is there a way to do this?
GROUP BY won't do it. DISTINCT doesn't help.
Image Toolkits | Image Processing | Cleek
|
|
|
|
|
SELECT a.c1, a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, ot5
FROM MyTable AS a
INNER JOIN (SELECT c1, c2, c3, c4, c5, c6, MAX(c7) AS c7 -- or MIN(c7) if you prefer
FROM MyTable
GROUP BY c1, c2, c3, c4, c5, c6) AS b ON
a.c1 = b.c1 AND a.c2=b.c2 AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6 AND a.c7=b.c7
DISCLAIMER: I have not tried this. Its all in my head (which has been known to be fallable sometimes)
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
This question has to be easy, but I can't seem to find the answer. I'm working with Visual Basic and I've defined a form with a button, an OleDbConnection, an OleDbDataAdapter and a Dataset.
The DataAdapter has a select command like SELECT * from patients where name LIKE 'm%'. (It too me much sweat to figure out to use m% and not m*, but that is another story.)
The next step is use an edit box where I can enter the patient name. Then I want to take that and either change the adapter command text, or use something like
SELECT * from patients where name LIKE ?
and fill in the ?.
There must be an easy way to do this, as this is bread and butter, but I have yet to find it.
Thanks,
Ilan
|
|
|
|
|
The problem I refer to above boils down to the fact that it doesn't know "As OleDbConnection", "As OleDbCommand" or "As OleDbDataAdapter" in mysub().
It knows the same functions as New commands, and it works find with Button1_Click which actually uses OleDbConnection1, sty1 (an OleDbDataAdapter) and stds (a Dataset).
I can't figure out why it gives me the compilation errors. If it were a C++, I would say some include file is missing.
Below is the code, and I'm stuck.....
Thanks for any help.
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OleDbConnection1.Open()
sty1.Fill(stds, "studies")
OleDbConnection1.Close()
End Sub
Private Sub mysub()
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim selectCMD As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter
End Sub
End Class
|
|
|
|
|
Hello,
I'm trying to read BLOB field that contains RTF-data (Microsoft Word file) using VC++ and ADO. Actually, i have the code:
CoInitialize(NULL);
_ConnectionPtr spConn(__uuidof(Connection));
_RecordsetPtr spRs;
CString cnctstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb; Persist Security Info=False;";
CString Qry = "select * from testtable";
spConn->Open((_bstr_t) cnctstr, "", "", adConnectUnspecified);
spRs = spConn->Execute((_bstr_t) Qry, NULL, adCmdText);
//
// BLOB data reading section
//
spConn->Close();
CoUninitialize();
Should I use a "_variant_t" data type or anything like that to access the data that I need? Please help to solve this problem.
All the comments will be appreciated, thanks in advance.
|
|
|
|
|
|
Hi, authoring a task report with Microsoft SQL Server's Reporting Service. It consists of Projects, Tasks and subtasks. So, naturally, I group tasks by:
1. Project
2. Task Owner.
The trouble is, "grouping" inevitably lead to a drilldown box for each group - Drilldown button is okay on "Preview", I clicked and things get expanded as expected. I then deployed the report and view from http://localhost/reports. The reports get loaded and I clicked on the drilldown button -- I can tell there's a postback everytime I click drilldown. But nothing gets expanded in response. Is there any work around for this? I have already applied Reporting Service's SP1.
Thanks in advance.
Norman Fung
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
