SQL - How To - I Need A Query Solution, No Coding....
Nov 1, 2007
Hi. I'm looking at a problem and I can't find any solution short of
coding.
I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any of
the 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's say
that
-bucket A has 100 PINS
-bucket B has 300 PINS
-bucket C has 600 PINS
-bucket D has 200 PINS
-bucket E has 500 PINS
-bucket F has 350 PINS
-bucket G has 700 PINS
I need to know, for each PIN, the number of buckets (tables) it
belongs to, and which ones, i.e:
- PIN 1 belongs to A, C, D, so it belongs to 3 buckets
- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets
- PIN 3 belongs to A, so it belongs to 1 bucket
- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets
- PIN 5 belongs to ..., so it belongs to 0 buckets
etc, etc
What would be the simplest way to achieve that, please ?
I have a table where I am needing to concatenate several columns. Some of the columns might have NULL values, so I need to concatenate only those fields where there is data and not concatenate the NULLs.
For Example:
My columns are ID, Effdt, keyword01, keyword02, keyword03, keyword04, keyword05, keyword06, keyword07, etc.
I need to concatenate all of the keyword columns into one field (keyword01 + '/' + keyword02 + '/' +keyword03 + '/' +...). However, keyword06 and on might be NULL (there is never an instance where, say, keyword02 has data, keyword03 is NULL, and keyword04 has data. If a keyword field, greater than keyword01 (it always has data in it), has data in it... then every keyword field before it does also.)
I have a big presentation tomorrow and I have a query I'm trying to make a little more presentable. query text itself formatted (filtering data)
Tables: --tpeople --tsynclog
The query:
select Firstname+' '+Lastname as [FirstName LastName],synctime as [Nestle synctime],sum(DurationInMinutes) from tpeople join tsynclog on people_id = id where synctime > '2006-05-05' group by tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime having sum(DurationInMinutes) > 100 order by synctime desc
As you can see I need some alias added and maybe the whole thing formatted a little different. Can anyone help? Thanks.
I'm new in SQL Server 2000. Anyone has a good materials or sites to recommend which talk about Multidimensional Expression? Especially MDX work in "Analysis Manager".
You information are highly appreaciate. Thanking you in advance.
CREATE PROCEDURE SP_ProcessAdminResults(@UserID nvarchar(100)) As
Declare @ReqID as integer; Declare @Assaignee as integer; Declare @TechName as nvarchar(200); Declare @OpenDate as datetime; Declare @GroupID as integer; Declare @DateActiontaken as datetime; Declare @AssignedDate as datetime; Declare @ActionDays as integer; Declare @OpenDays as integer;
declare GroupCursor cursor local scroll static for select [ReqID],[Assignee],FullName,[OpenDate],[GroupID],[DateActionTaken] from V_ProcessRequestMaster where UserID=@UserID and [Status]<>'CL' order by ReqID open GroupCursor fetch first from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken while (@@fetch_status <>-1) begin select @AssignedDate= min(DateLogged) from LOGMASTER where analyst=@Assaignee and reqMasterID=@ReqID and UserID=@UserID and Type='INIT' If @AssignedDate is null Begin select @AssignedDate=dbo.GetActionDate(LTRIM(Rtrim(@TechN ame)),@ReqID,@UserID) End
If @AssignedDate is null BEGIN set @ActionDays=0 END Else BEGIN If @GroupID=438030 BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,5 ,6) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),5,6) END Else BEGIN set @ActionDays=[dbo].GetBussinessdays(@AssignedDate,@DateActiontaken,1 ,7) set @OpenDays=[dbo].GetBussinessdays(@OpenDate,getdate(),1,7) END
END If @ActionDays<>0 set @ActionDays=@ActionDays-1 If @OpenDays<>0 set @OpenDays=@OpenDays-1
update requestmaster set DateAssigned=@AssignedDate, ActionDays=@ActionDays, OpenDays=@OpenDays where ReqID=@ReqID and UserID=@UserID fetch next from GroupCursor into @ReqID,@Assaignee,@TechName,@OpenDate,@GroupID,@Da teActiontaken end GO
The master table contains like 600,000 odd records and when i put a cursor and process the results where i need to get the business days between assigned date and action taken date the query take lot of time and eventually the front end crashes saying Time OUT expired.
Looking for an expert solution to simplify that query.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim Value As String = EventCmb.SelectedItem.ToString() objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True" Try objConn.Open() objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter objCmd.Parameters.AddWithValue("@Value", Value) ' Add the @value withthe actual value that should be put. This makes it securer Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned. If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then ContactLbl.Text = RetVal.ToString() Else ' noting was returned End If Catch ex As Exception Throw ex Finally objConn.Close() End Try There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() " Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int." It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!
I'm attempting to copy data from an Epicore server with multiple company databases to another server. All this data will be placed in a single table on the receiving server with the addition of an database identifier, so that the receiving department will know, to which company the data belongs. I have code that will dynamically create the SQL and then execute it using EXEC (@SQL), and this works just fine acrosss multiple databases. I've read in my Transact-SQL Programming book that using EXEC() is not limited to 255 characters, but it still appears that my dynamically created SQL is being shortened. The insert statement and the select statement list each column, and here is that code:
I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.
Thanks in advance, Anne
begin Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date) select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date from TEAM3B_PULL_TOTAL_TST A where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST) and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST) and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST) and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE) end
epost char(60) default NULL, i must be a @ value in it! any ides?
kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!
losenord char(60) default NULL, verlosenord char(60) default NULL, How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!
i've two column... One for msg another for msgLastposted
in label control view msg(last visitor)....... that msg stored in my db.
Example Table Data --------------------------------------------- MSg|MsgLastPosed
How edit forum |2007-03-12 10:50:25.747 How to Value Change|2007-03-12 10:56:36.373 Sql Command|2007-03-12 11:00:25.047 User Control|2007-03-12 11:02:10.793 How I can uninstall|2007-03-12 13:07:51.233 -----------------------------------------------
In table have many record..
label control display msg based on last visitor time.. after that again msg changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..
:This is a segement of VBscript for checking lastest Backup in SQLServer 2000: 'temporary variables to useDim sTemp1Dim i, sTemp2 'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2 end If If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp group by sex havingsex='M')
selectlastname fromemp wheresex = 'F' and salary>(selectavg(salary) fromemp wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve
I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.
I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:
select * from Table where ... and Official in (... problem area ...)
What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.
Any ideas? I don't want to use a dynamic query. Thanks,
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman (pkey smallint primary key, fkey char(1), updateddatetime)
insert into #blindman (pkey, fkey, updated) select1, 'A', '1/1/2006' UNION select2, 'B', '1/1/2006' UNION select3, 'A', '1/2/2006' UNION select4, 'B', '1/2/2006' UNION select5, 'A', '1/4/2006' UNION select6, 'B', '1/2/2006' UNION select7, 'A', '1/3/2006' UNION select8, 'B', '1/3/2006' UNION select9, 'A', '1/5/2006' UNION select10, 'B', '1/5/2006'
drop table #blindman Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key. The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value. Here is the desired output for the sample data:pkey fkey updated nextpkey ------ ---- ---------- -------- 1 A 2006-01-01 3 3 A 2006-01-02 7 7 A 2006-01-03 5 5 A 2006-01-04 9 2 B 2006-01-01 4 4 B 2006-01-02 6 6 B 2006-01-02 8 8 B 2006-01-03 10 Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value. Ideally, I want this as a VIEW.
ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:
What is the total weight of different colored parts?
ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:
Select sum pweight from parts where distinct pcolor
looks horribly wrong so any help is appreciated. here is another one:
What colored part has total weight greater than 8 units?
select pcolor from parts where sum pweight > 8
??? i dunno lol.
there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:
Hi!If you like to spend some moments on my code examples,please look at it - usage is free (-:http://www.codeproject.com/useritems/TSQL_coding.aspFeedback welcome.GreetingsBjorn
Hi List,I am searching franticly for a solution (or the procedure) to settingthe coding of a new DB to UTF-8. I can find no setting in the ServerManager, during creation of the DB, to influence this. Can someoneplease show me the way? Thanks--Shawn
This question probably overlaps a few different topic areas.
As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).
Therefore I am having to look at alternatives.
With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.
I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.
Is there any scope in using data tables or have the read on one thread and write on another.
Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).
In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.
Any ideas on this area will be gratefully accepted.
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
GLOBAL STRING Line1_BatchID GLOBAL STRING Line1_CompletedDate GLOBAL INT Line1_CompletedQty GLOBAL STRING Line1_CompletedTime GLOBAL STRING Line1_ExpiryDate GLOBAL INT Line1_Qty GLOBAL STRING Line1_OrderID
GLOBAL INT Line1_BatchID_ID GLOBAL INT Line1_CompletedDate_ID GLOBAL INT Line1_CompletedQty_ID GLOBAL INT Line1_CompletedTime_ID GLOBAL INT Line1_ExpiryDate_ID GLOBAL INT Line1_OrderStatus_ID
GLOBAL STRING Line2_BatchID GLOBAL STRING Line2_CompletedDate GLOBAL INT Line2_CompletedQty GLOBAL STRING Line2_CompletedTime GLOBAL STRING Line2_ExpiryDate GLOBAL STRING Line2_OrderStatus GLOBAL INT Line2_Qty GLOBAL STRING Line2_OrderID
GLOBAL INT Line2_BatchID_ID GLOBAL INT Line2_CompletedDate_ID GLOBAL INT Line2_CompletedQty_ID GLOBAL INT Line2_CompletedTime_ID GLOBAL INT Line2_ExpiryDate_ID GLOBAL INT Line2_OrderStatus_ID
GLOBAL STRING Line3_BatchID GLOBAL STRING Line3_CompletedDate GLOBAL INT Line3_CompletedQty GLOBAL STRING Line3_CompletedTime GLOBAL STRING Line3_ExpiryDate GLOBAL STRING Line3_OrderStatus GLOBAL INT Line3_Qty GLOBAL STRING Line3_OrderID
GLOBAL INT Line3_BatchID_ID GLOBAL INT Line3_CompletedDate_ID GLOBAL INT Line3_CompletedQty_ID GLOBAL INT Line3_CompletedTime_ID GLOBAL INT Line3_ExpiryDate_ID GLOBAL INT Line3_OrderStatus_ID
GLOBAL STRING result GLOBAL STRING result2
FUNCTION getOrderDetail(STRING Product_ID) INT counter = 0 INT statusSQL, sqlResult; STRING Sql1 STRING Sql2 STRING Sql3
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN //Select first SetId result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter < 9 THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateBatchID(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedQty(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID) END IF Product_ID = "ChocolateBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID) END IF Product_ID = "PeanutButterBiscuit" THEN Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID) END
sqlResult = SQLExec(statusSQL, Sql1); END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
FUNCTION UpdateCompletedOrder(STRING Product_ID) INT statusSQL, sqlResult; STRING Sql1 statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success IF Product_ID = "Biscuit" THEN Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID) sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID) sqlResult = SQLExec(statusSQL, Sql1); END END
SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
INT FUNCTION getLine1_Qty() RETURN Line1_Qty END
INT FUNCTION getLine1_CompletedQty() RETURN Line1_CompletedQty END
STRING FUNCTION getLine1_OrderID() RETURN Line1_OrderID END
STRING FUNCTION getLine1_BatchID() RETURN Line1_BatchID END
INT FUNCTION getLine2_Qty() RETURN Line2_Qty END
INT FUNCTION getLine2_CompletedQty() RETURN Line2_CompletedQty END
STRING FUNCTION getLine2_OrderID() RETURN Line2_OrderID END
STRING FUNCTION getLine2_BatchID() RETURN Line2_BatchID END
INT FUNCTION getLine3_Qty() RETURN Line3_Qty END
INT FUNCTION getLine3_CompletedQty() RETURN Line3_CompletedQty END
STRING FUNCTION getLine3_OrderID() RETURN Line3_OrderID END
STRING FUNCTION getLine3_BatchID() RETURN Line3_BatchID END
FUNCTION dbTest()
INT statusSQL, sqlResult; INT counter = 0 STRING Sql1 STRING Sql2 STRING Sql3 STRING Product_ID = "Biscuit" result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')" sqlResult = SQLExec(statusSQL, Sql1); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF result <> "" THEN result = result + "," END result = result + SQLGetField(statusSQL, "SetId") END END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))" sqlResult = SQLExec(statusSQL, Sql2); IF sqlResult = 0 THEN //If SQL Success result = "" IF SQLNext(statusSQL) = 0 THEN result = SQLGetField(statusSQL, "SetId") END END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field" sqlResult = SQLExec(statusSQL, Sql3); IF sqlResult = 0 THEN //If SQL Success WHILE SQLNext(statusSQL) = 0 DO IF Product_ID = "Biscuit" THEN IF counter = 0 THEN Line1_BatchID_ID = SQLGetField(statusSQL , "ID") Line1_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line1_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line1_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line1_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "ChocolateBiscuit" THEN IF counter = 0 THEN Line2_BatchID_ID = SQLGetField(statusSQL , "ID") Line2_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line2_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line2_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line2_Qty = SQLGetField(statusSQL , "DataValue") END END
IF Product_ID = "PeanutButterBiscuit" THEN IF counter = 0 THEN Line3_BatchID_ID = SQLGetField(statusSQL , "ID") Line3_BatchID = SQLGetField(statusSQL , "DataValue") END IF counter = 1 THEN Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 2 THEN Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID") Line3_CompletedQty = SQLGetField(statusSQL , "DataValue") END IF counter = 3 THEN Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID") END IF counter = 4 THEN Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID") END IF counter = 5 THEN Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID") END IF counter = 6 THEN Line3_OrderID = SQLGetField(statusSQL , "DataValue") END IF counter = 7 THEN
END IF counter = 8 THEN Line3_Qty = SQLGetField(statusSQL , "DataValue") END END
counter = counter + 1 END END
END SQLEnd(statusSQL) SQLDisconnect("DSN=SQLSRV_TBLS") END
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE"; execute SQL statement "select * from table A where tableA.field B = "ABCDE"; For each record from the result of the above SQL statement, "select * from tableB where tableB.field C=tableA.fieldX" if the record can be found in tableB, "update tableB set tableB.fieldD=something"; if the record cannot be found in tableB, "insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
I'm currently working on an application that needs to store address information. I figured a database of some sort would be the perfect thing to use.
The thing is: The application will work locally, there will be no server running on a network
There shouldn't be any login and password protection for the db When I later on install the application on a computer, a fresh and clean db should be created within the applications folde
How can I create and access a db like that using c# and MS SQL Server?
Any hints, tips, example code, links...anything?
Many thanks in advance!!!
PS. I've been working some with visual c# express and recently installed SQL Server & Manager. I've previusly made some php web pages using MySQL but I´m a total noob when it comes to working with SQL and c# (= I'm stuck!).
It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @@FETCH_STATUS = 0 to @@FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.
DECLARE @FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For
select column_name from information_schema.columns where table_name = 'rpt_Scr_B0000_MiniFinancials'
open cursor_update_rpt_Scr_B0000_MiniFinancials
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials INTO @FieldName
update rpt_Scr_B0000_MiniFinancials set @FieldName = 0 where @FieldName is null
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials INTO @FieldName
END
CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials
