Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@mSQL + '''' + @mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')
Hi, I need to make a query that, according to the value of a field, returns an specified value.
ie:
TABLE:
Col1
1
0
I need to make a query like:
SELECT (????) FROM TABLE
and if Col1=1 returns 'Assigned' and if Col2=0 it returns 'Unassigned'
So the result from the query for the data above is:
Assigned
Unassigned
Thankx
Hello forum. I address you in order to help me for create a little sentence.
the problem is as:
I have a table "STAMPING" that save data as:
TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31 1 1 1
19/05/1982 15:31:29 1 1 1
19/05/1982 15:31:55 1 1 1
19/05/1982 16:25:46 1 1 1
19/05/1982 16:26:23 1 1 1
19/05/1982 16:26:34 1 1 1
19/05/1982 22:23:56 1 1 1
19/05/1982 22:24:23 1 1 1
and I would need to filter them as-->
TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31 1 1 1
19/05/1982 15:31:29 1 1 1
19/05/1982 15:31:55 1 1 1
19/05/1982 16:25:46 1 1 1
19/05/1982 16:26:23 1 1 1
19/05/1982 16:26:34 1 1 1
19/05/1982 22:23:56 1 1 1
19/05/1982 22:24:23 1 1 1
20/05/1982 06:10:29 1 1 1
20/05/1982 06:11:51 1 1 1
TIMESTAMP------------USER_ID-----TERMINAL_ID----CHIP_ID
19/05/1982 15:30:31 1 1 1 <<<----
less (difference) --> 00:56:03
19/05/1982 16:26:34 1 1 1 <<<----
19/05/1982 22:24:23 1 1 1 <<<----
less (difference) --> 07:45:00(+/-)
20/05/1982 06:11:51 1 1 1 <<<----
So, I need to delete the "timestamps" the an user has done, which the difference between one stamping and the following one is less than 2 minutes for the inputs for entrances and I need to remove it when the "stampings" which the difference between an one stamping and the previous one is less than 2 minutes for exits.
Can you help me please?
I will appreciate a lot your help.
CNUECOD is primary key in VTA020SAP2 table
TIA
Violation of PRIMARY KEY constraint 'PK_VTA020SAP2'. Cannot insert duplicate key in object 'VTA020SAP2'.
The statement has been terminated.
INSERT INTO VTA020SAP2 SELECT VTA020SAP1.*, NULL as xCLIPRO, NULL AS xPAIS, NULL AS xCONFREI, NULL AS xCONCPER,
NULL AS xMONEDA, NULL AS xFAX, NULL AS xTIPEXP, NULL AS xCASILLA, NULL AS xDIAENTR, NULL AS xPUNVEN,
NULL AS xCIUDAD, NULL AS xREGION, NULL AS xCONDIC, NULL AS xINSPAG, NULL AS xCUMPLE,
NULL AS xPERVIS, NULL AS ESTADO FROM dbo.VTA020SAP1
INNER JOIN VTA020
ON VTA020SAP1.CNRCLI = VTA020.CNRCLI
AND VTA020SAP1.CNRCLI IN (SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1
GROUP BY VTA020SAP1.CNUECOD)
AND (not EXISTS
(SELECT VTA020SAP2.* FROM dbo.VTA020SAP2
INNER JOIN VTA020SAP1 ON VTA020SAP1.CNUECOD = VTA020SAP2.CNUECOD
WHERE dbo.VTA020SAP2.CNUECOD = dbo.VTA020SAP1.CNUECOD))
If I want to change the value of a special bit in a column, how to wirte the SQL sentence in sqlserver.
I mean in a column, the value is 'test', then how to change the 's' to 'e' or any other value by just one SQL sentence.
Thanks.
Hello !!
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Is It possible ???? How to do It (step by step) ?
Thanks !!
Hi, all friends. I'm using SQL-SERVER 2000.I have a table like this:NIF Name----------------------------------------A-1234 Company1B-123-B1 Company2C-4568 Company4D-453-DF Company5I want delete the symbol "-" in the row "NIF". eg. A-1234 change toA1234, D-453-DF to D453DFSo how can I write the sentence . Should I use the Stored Procedure?Thank you very much!!Simon
View 2 Replies View Relatedi have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:
INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')
with empty values, but then it doent update in the dataBase-only if all the values appear-
what the solution of it?
Thanks
I have two tables. A table called users (content speaks by it self) and a table called groups.
Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt.
The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId.
Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table.
Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command.
Thanks in advance, Greetings from Esben
Hello,
I would like to know how can I change a "not in" clause with the same results in a SQL sentence in order to improve the performance of my SQL sentence.
I am working with SQL Server 2005
Thanks,
jmota
Hi,
I want to add a parameter to the following SQL-sequence. ActivityGroup is the table and I want to select the current activityGroupID in place of XXXXX. Do I need to put this in a stored procedure?
select * from ActivityGroup where cpgActivityGroupID = XXXXX and cgpRegistredFrom > getDate() and cgpRegistredTo < getDate()";
Thanks in advance!
Hi,
I have a project in which I have about 20,000 records in sql database table.
What I would like to do is generate a query that lists all the unique words in a particular field acros the entire table so as to generate a glossary of words.
if we had a table that looked like
ID Description
001 This is the first record
002 This is the second record
003 This is not the first record
and the query was run on the description field, then the result I would like to see is
This
is
the
first
second
not
I hope this makes sense. Any help is appreciated.
Hi All,
I want to give the users the ability to highlight words in sentences on the page in different colors.
Please see link below for an example(watch out for URL wrapping):
http://64.233.161.104/search?q=cache:fKHSRIxERbUJ:www.codeguru.com/columns/DotNet/article.php/c6603/+XML+serialization+in+.net&hl=en
My MASTER table that contains text is very simple:
TEXT_ID INT
TEXT varchar
I was thinking of something like creating another table to keep track of selection criteria:
USER_NAME varchar // keeps track of which user made the selection
TEXT_ID INT // points to the text id in the MASTER table
START_POS INT // start position of the selection
END_POS INT // end position of the selection
COLOR INT // color of the selection
The thing I don't like about this method, is if user(s) have many words higlighted in the sentences, this table may get very large.
I also thought of maybe combining START_POS, END_POS & COLOR into comma delimited entries:
EX: 1:5:240, 25:30:125 // START_POS:END_POS:COLOR
The thing I don't like about this method, the parsing calculations may take a while to execute.
My goal is to stream as much text data as possible and make it as fast as possible. How does Google or any one else do it?
P.S. Having a copy of a MASTER table with HTML tags in TEXT field is not an option.
Thanks,
Vlad
I have to parse a sentence into separate words. This has to be done in a Stored Proc.
If the sentence is: "The sun is rising". Then I want to store the words "The" "sun" "is" "rising" into 4 separate variables. (The separator is a space).
Has someone already written something like this before? I do not want to re-invent the wheel.
Thankx.
KP
CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions
Output
Site_no Instructions
20 Request PIN#510 then proceed
21 Request PIN#987 if wrong request name
22 Request PIN#688 allowed to use only numbers
All text instructions start with “Request PIN#XXX” also after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN#XXX” the text “and codeword” keeping the current rest of text
How can I set e REPLACE CAST sentence using something LIKE PIN%%%%
To get these type of results
site_no instructions
----------- ----------------------------------------------------
20 Request PIN#510 and codeword then proceed
21 Request PIN#987 and codeword if wrong request name
22 Request PIN#688 and codeword allowed to use only numbers
the talbe row like this:
ID Name Scoe 11 Tome 20 12 Jack 30 11 Tome 40 12 Jack 10 13 John 10
My query command like this:
Select T1.Id,T1.Name,T2.mathfrom st T1right join(Select Id as Id2,Sum(Math) as Math from St group by id) T2on T1.id=t2.id2where t1.id = t2.id2
While the reuslt is :
Id Name Score
11 Tom 60
11 Tom 60
12 Jake 40
12 Jack 40
13 John 10
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows, and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same.
but why ? please help me !
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder
@mywhere VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
select * from order @mywhere
END
Hi everyone!I am working with Delphi v7 and MS SQLServer.I am trying to insert data in a table with a SQL sentence. Some of thefields of my table are type char or varchar, and they can have nullvalues.What do i have to write in the SQL sentence to insert a null value inthose fields?I tried with '', an empty String, but it doesnt work, the tablesstores an empty String (logical :-)).In the SQLServer GUI you have to press CTRL + 0 to insert a NULLvalue, but how can i tell this to the SQLServer through a SQLSentence?Well, thank you very much.
View 2 Replies View RelatedI was recently told SSRS does not support the ability to underline a portion of text in a sentence. Does anyone know if this is true? Couldn't "text decoration" "underline" and "span" be used to underline the web address in the sample sentence below?
"Please visit our website at: www.abc.com"
Thanks
I have a illegal keyword table name as "Illegal_keyword_Master" where near about 2000 illegal keyword are stored... Now I have a big Sentence(bunch of words)...
If any illegal is keywords is found from "Illegal_keyword_Master" table in that sentence then the sentence is "banned" otherwise the sentence is "OK"....
I have an very long ntext field, made up of many sentences that I append a full stop to every one, I also strip out any line breaks within the text. However I get this error, when I look it up it comes up with "Failed to locate the ending boundary of a sentence."
View 0 Replies View Relatedi have this situations:
the aspx file with source code work good.
when i try run project which created from VS i take
"SQL Server does not exist or access denied."
connection strings same:
"Data Source=**.**.**.**,1433;Network Library=DBMSSOCN;Initial Catalog=slider;User ID=username;Password=password;"
i think may be it`s web.config in project wrong?
Edited by SomeNewKid
Removed username and password from connection string.
I am having a problem reinstall SQL Express 2005. I had it installed, but then I unistalled it. I have tried installing it again, but everytime I do it says that the product is already installed. There is no instance of it in my Add/Remove Programs. I have read the threads that relate to this topic, but they haven't helped. I have looked at my logs of the installs, but they don't make sense neither. I just want the program installed again, or a way to get it so that I can use it.
When I try to manually uninstall using msiexec.exe /x <processid> I get an error saying that the package could not be opened. This happens with all the process ids I use that I get from the log files (I have 11 logs).
am using Visual Basic 2005 and trying to create a database, and it will not let me. That is how I know that it is not installed, plus I can't find any instance of it one my computer. Please help. Thanks.
How di I understand what SQL Server Integration Services mean? Can some one help me?
View 3 Replies View RelatedI'm trying to put together a very simple page where you can click the up and down button to move the item up or down in the list and save the new list into the database. I've run the stored procedure... and it works so Its not a procedure problem. Here is the list in my databaseOrder_Id Item Position1 Sebring 12 GrandPrix 23 Accord 44 Miati 3 When I go to click the button I get a object reference not set to an instance of an object... This error haunts me many times. Heres the code to my project:BLL /// <summary>
/// Gets Items from the Item table.
/// </summary>
/// <returns>A list of items.</returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
public OrderItemDataSet.OrderItemDataTable GetItemDataByPosition()
{
return OrderItemAdapter.GetItemDataByPosition();
}
/// <summary>
/// Gets the order id based on the position specified.
/// </summary>
/// <returns>The order id.</returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
public int SelectOrderIdByPos(int pos)
{
return SelectOrderIdByPos(pos);
}
/// <summary>
/// Update Order list.
/// </summary>
/// <param name="newPos">Position to change to.</param>
/// <param name="originalPos">Original position.</param>
/// <param name="orderId">Original id.</param>
/// <param name="otherOrderId">Position to change id.</param>
/// <returns></returns>
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
public void ReorderItemByIdAndPosition(int newPos, int originalPos, int orderId, int otherOrderId)
{
OrderItemAdapter.ReorderItemByIdAndPosition(newPos, originalPos, orderId, otherOrderId);
} Codebehind 1 using ReorderListWebApplication.BLL;
2 using System;
3 using System.Collections;
4 using System.Configuration;
5 using System.Data;
6 using System.Linq;
7 using System.Web;
8 using System.Web.Security;
9 using System.Web.UI;
10 using System.Web.UI.HtmlControls;
11 using System.Web.UI.WebControls;
12 using System.Web.UI.WebControls.WebParts;
13 using System.Xml.Linq;
14
15 namespace ReorderListWebApplication
16 {
17 public partial class _Default : System.Web.UI.Page
18 {
19 protected void Page_Load(object sender, EventArgs e)
20 {
21
22 }
23
24 protected void UpButton_OnClick(object sender, EventArgs e)
25 {
26 Label posLabel = (Label)ReorderItemDataList.FindControl("PosLabel");
27 Label idLabel = (Label)ReorderItemDataList.FindControl("IdLabel");
28
29 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem())
30 {
31 int pos = 0;
32
33 pos = item.SelectOrderIdByPos(Convert.ToInt32(idLabel.Text) - 1);
34
35 // Grab other orderId!!
36 item.ReorderItemByIdAndPosition((Convert.ToInt32(posLabel.Text) - 1), Convert.ToInt32(posLabel.Text), 3, pos);
37 }
38 }
39
40 protected void DownButton_OnClick(object sender, EventArgs e)
41 {
42 using (ReorderListWebApplication.BLL.OrderItem item = new ReorderListWebApplication.BLL.OrderItem())
43 {
44
45 }
46 }
47 }
48 }
49
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: You have specified that your update command compares all values on SqlDataSource 'SqlDataSource5', but the dictionary passed in for oldValues is empty. Pass in a valid dictionary for update or change your mode to OverwriteChanges.
can somebody tell me what this is?
Angiemarie
Hello, I could use some help with this error message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
This if statement is the problem
IF (select bill_freq from #header1) = '1' update #header1 Set [Monthend] = aa.pername from sv00564 zz inner join sv00532 aa on zz.wennsoft_period_id = aa.wennsoft_period_id Where zz.rmdnumwk like #header1.Invoice_Number
This is failing:// Trying to update DynamicPageContent.Html where DynamicPageContent.PageID='121'//With// Select Html from DynamicPageHistory where HistoryID='831'//Update DynamicPageContentSET Html=(SELECT Html FROM DynamicPageHistory WHERE HistoryID='831')WHERE PageID='121'With the following error:Server: Msg 279, Level 16, State 3, Line 1The text, ntext, and image data types are invalid in this subquery or aggregate expression.DynamicPageContent.Html is Ntext size 16 and DynamicPageHistory.Html is Ntext size 16. PageID is and int and HistoryID is an int. It fails with single quotes around 831 and 121 and it fails without single quotes. The error message is the same both ways.Ideas?Thanks,Rex
View 4 Replies View RelatedI feel extremly happy today and let me share with this wonderful forum why.
SQL is awesome and I'm really learning that theres alot of resources out there and coding is not that hard once you understand what your doing. Just took awhile for things to sink in. I still have a long ways to go but I'm really enjoying using sql. I practically live in our SQL Server 2000 at work and I'm learning more and more about it everyday.
Thank YOu so much guys I really appreciate all the help you've given me
Now I'm gonna study for the sql cert
:)
I have a table Test_GUID, with fields id as int PK IDENTITY, GUID as varchar(50), and Version as tinyint. I put data in it such as
id GUID Version
------------------
1 abc 1
2 abc 2
3 def 1
Then I tried to get the following result:
id GUID Version
-------------------
1 abc 2
3 def 1
By using a query:
SELECT GUID, MAX(Version) AS MaxVersion, id
FROM dbo.Test_GUID
GROUP BY GUID
But then I get an error because id is not being aggregated.
This works
SELECT GUID, MAX(Version) AS MaxVersion
FROM dbo.Test_GUID
GROUP BY GUID
but then how do I get the id of each record?
I'm doing code with VB.Net.
The error was:
System.FormatException: Input string was not in a correct format.
The line this is happening on is:
Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
I don't understand, it is giving that error particularly when I want to get ClientRepID
Some more lines from the code:
Response.Write("<TD><p>" + rdData("Username") + "</p></TD>")
Response.Write("<TD><p>" + rdData("ClientRepID") + "</p></td>")
The first line the compiler had no problem but when it gets to the second line with ClientRepID it gives me this error. (ClientRepID exists in the database)
Can someone please help me? Thanks
Hello, i just bought sql server 2005 with 5 cals (previously i was using a shared sql server with my previous host)... I probably should have asked this question before i bought it... i don't understand how the cal licencing works... the sql server is for running our ecommerce site, i just got a dedicated server to put this on... but from reading the licencing does this mean that only 5 internet users can access my website(or the information in the database) at a time???
any help with this would be appreciated
~ Moe
OK, so I know that following works if I want update/insert the value "joe's bar" into the table.
set SET QUOTED_IDENTIFIER on
update #temp set id=4 where name ='joe''s bar'
The thing is, I do not conceptually understand what is happening and it is driving me nuts. I have read and re-read the description of quoted_identifier in books online and I still don't get it. How does the extra ' after joe make it work? Is there any hope for me? :)