|
CREATE TABLE EmpDtls(Code VARCHAR(10), Name VARCHAR(100), DeptCode TINYINT)
CREATE TABLE DeptDtls(DeptCode TINYINT, DeptName VARCHAR(50), EmpCount INT)
INSERT INTO EmpDtls(Code, Name, DeptCode)
VALUES('Emp1','Venkat', 1), ('Emp2','Prabu', 2), ('Emp3','Kumar', 1), ('Emp4','Karthick', 3),
('Emp5','Amith', 2),('Emp6','HariKrishna', 2)
INSERT INTO DeptDtls(DeptCode, DeptName)
VALUES(1, 'IT'),(2, 'Sales'), (3,'HR'), (4, 'Accounts')
SELECT Code, Name, DeptCode FROM EmpDtls
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls
UPDATE D SET D.EmpCount= E.EmpCount
FROM DeptDtls D
INNER JOIN (SELECT DeptCode, COUNT(Code) 'EmpCount' FROM EmpDtls GROUP BY DeptCode) E ON E.DeptCode=D.DeptCode
SELECT DeptCode, DeptName,EmpCount FROM DeptDtls
IF OBJECT_ID('EmpDtls') IS NOT NULL DROP TABLE EmpDtls
IF OBJECT_ID('DeptDtls') IS NOT NULL DROP TABLE DeptDtls
|
|
|
|
|
Hi Naina,
Check the Script, U can use CASE Statement in UPDATE.
CREATE TABLE #EmpDtls(ID INT, EmpGender CHAR(1))
INSERT INTO #EmpDtls (ID, EmpGender) VALUES (1,'M'),(2,'F'),(3,'M')
SELECT ID, EmpGender FROM #EmpDtls
UPDATE #EmpDtls SET EmpGender = (CASE WHEN EmpGender='M' THEN 'F' WHEN EmpGender='F' THEN 'M' END)
SELECT ID, EmpGender FROM #EmpDtls
Regards,
GVPrabu
|
|
|
|
|
I am adding my code below
create procedure dist_profit_pairs1(IN user_id
varchar(20),IN plan varchar(20))
begin
DECLARE left_bvs,right_bvs Float DEFAULT 0;
SELECT sum(businessvalues)as left_bvs from bv_history where userid=user_id and type='Distribution Profit' and position='Left';
SELECT sum(businessvalues)as right_bvs from bv_history where userid=user_id and type='Distribution Profit' and position='Right';
IF left_bvs < right_bvs THEN
SELECT left_bvs;
ELSE
SELECT right_bvs;
|
|
|
|
|
Hi
I assume you want to print the variable value when executing in sqlserver studio management. If so, you can print the variable value by
print variablename
regards
Naina
Naina
|
|
|
|
|
The printed value has to be a varchar or char. So, please cast the value to a varchar before printing.
|
|
|
|
|
Can Visual Studio 2005 connect with an Access 2010 Database?
|
|
|
|
|
Yes
G to connectionstrings.com as a research beginning.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
We're currently implementing a warehouse management system with some customizations. One task is to plan the work in the warehouse by releasing waves of picking activities. The release is handled by a stored procedure. This stored procedure generates data on the amount of work that needs to be done, number of containers that are required, etc.. The planner needs to know this information in order to release the appropriate waves.
So, in order to capture the required data, we have another stored procedure which releases all waves that are not yet released and rolls back the transaction once it's done. This roll-back is a guarantee. The problem is that during this 'simulation' other queries are blocked, which creates a performance problem.
Is there a way to run the 'simulation' without blocking all other queries?
Thanks in advance!
Kind regards,
Bob Stanneveld
|
|
|
|
|
Sounds to me like you are doing something dramatically wrong, running a simulation on production data, therefore requiring the roll back, or building a business process on cancelling a transaction.
A more normal method of simulating would be to restore production to another instance, run the process to completion and then restore again to repeat.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Earlier this week I queried the possibility of Propagating a MySQL transaction with MSMQ on a concept level.
Thanks to Pete I crossed that bridge. But there is a stumbling block.
MySQL transactions themselves do not appear compatible with TransactionScope!
Short of migration to SQLServer what are my choices?
Ger
|
|
|
|
|
Good Day All
I imported data into a Ms Access 2013 from SQL and all the data is there. I created a Query and also created a report that will use the Query. So the Query returns all the data. There are around 590 000 records that needs to be displayed by the record and records that are returned by the Query. when i do a Print Preview it gives me
Page 32767 of 12224
So i am afraid to start printing because from this look it seems like it does not show all data, but my assumption is that Print Preview cant show all the data , even if i try to go to the last page, can anyone clear my assumption.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Vuyiswa Maseko wrote: There are around 590 000 records...So i am afraid to start printing
You should be.
Why exactly do you think that you should print something that is probably going to be about 6000 pages (at 100 records per page).
Who has told you that they are going to read this? Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.
Other than that I certainly wouldn't attempt to print that at one go. If it messes up in the middle you have to start the entire job over. So select 400 pages at a time and print.
Might tell them you need your own printer too as it is going to tie it up for quite a while.
|
|
|
|
|
Good Day Jschell
Thank you for your reply. i will like to reserve my harsh comment for the other reply that i received after you , but i will politely reply to your question.
Who has told you that they are going to read this?
This is a book called the valuation roll , everyone comes and read this book because it tells a person how much they are going to pay in taxes ,for sure more than a million people are going to read the book.
Have you suggested to them what 6000 pages represents? Perhaps if you put a box (10 x 500 packages) on their desk and ask them what they planned to do with that then you might start a conversation that would lead to reports that are actually useful.
I have now decided to split it to 30000 to be on the safe side and created temp tables with the data sequential and its fine now thanks , what i learned is that Access Report will never output more than "32767", so basically in a Page you would have 10 records and if you take 590 000 and Divide by 10 it gives more 30000 Pages, so that is when i realized where and how to fix the problem.
Your reply was still going to point me to the direction of Splitting the data.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Vuyiswa Maseko wrote: for sure more than a million people are going to read the book.
Nope.
There is no way that you are going to get one million people to read 6000 pages (plural) much les 30,000.
What is going to happen instead is that they are going to search for what they already know they are looking for. And then read 1 or perhaps as many as 10 pages to find what they are looking for.
And that is where the solution lies. Provide a way for them to search for it.
Vuyiswa Maseko wrote: so that is when i realized where and how to fix the problem.
Presumably your design will have 30,000 static pages and then provide a way to figure out where something is in those pages and then go to the specific page.
|
|
|
|
|
I agree with jschell, you are asking access to do something inherently dumb, print 1000s of pages and you wonder why it is giving you wrong results!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Requirements in Software Development or in any industry might sometimes overcome the basic understanding of what makes sense to you.
Sometimes before reply you must try to understand what is the poster's point of view and after that you can reply or decide to be rude.
For your information, you can read my reply on jschell's reply.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Your "harsh reply" may be justified. I should have been somewhat more diplomatic. However you say more than a million people are going to read this tome, astonishing, no really I will be absolutely astounded if a million people read that book.
Some may reference the book and here is where I should have been more sensitive, I automatically assure that referencing such a vast amount of data would only be done electronically, this may not be an option for some!
Still the viability of printing 1000s of pages via Access stikes me as using the wrong tool for the job. Anything lees than a professional printing solution would be the wrong tool!
I am mightily impressed that you did not resort to down voting the response 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Pardon me for the word "Read" it should be substituted with the word "Reference",
This book is also available electronically, but there are people who comes in some parts of the Offices in the the city who don't have access to the electronic version
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Vuyiswa Maseko wrote: Requirements in Software Development or in any industry might sometimes overcome
the basic understanding of what makes sense to you.
That it true to a limited extent.
HOWEVER
1. That doesn't mean that the requirements are always right.
2. That doesn't mean that the requirements are always complete.
3 That doesn't mean that the requirement are always understood.
4 That doesn't mean that the requirements are always possible nor logical.
A professional developer must be willing and capable of recognizing when there is a flaw in the requirements and tell the business people what those are so together they can work towards a solution that meets the business needs.
And as a guess in terms of this posting, for what you posted, I would suppose that #2 in the above was the problem. Since presumably you already knew you needed to produce indivdiual pages in an electronic form and not just one massive single paper report.
At least I am hoping that is what your other post meant.
|
|
|
|
|
Hello Dears,
I have a problem with my application within Win server 2008
, when it is work well within Win server 2003
when I try to use my app with win_ser2008 get the following error message:
/////////////////////////////////////////////////
Server Error in '/yyy' Application.
--------------------------------------------------------------------------------
ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )
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.Data.OracleClient.OracleException: ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OracleException (0x80131938): ORA-00161: طول فرع الحركة 90 غير مشروع (العدد الأقصى المسموح64 )]
System.Data.OracleClient.OciEnlistContext.Join(OracleInternalConnection internalConnection, Transaction indigoTransaction) +264679
System.Data.OracleClient.OracleInternalConnection.Enlist(String userName, String password, String serverName, Transaction transaction, Boolean manualEnlistment) +202
System.Data.OracleClient.OracleInternalConnection.Activate(Transaction transaction) +68
System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction) +33
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +1318
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +100
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +116
System.Data.OracleClient.OracleConnection.Open() +40
cOracleDBManipulation.tableHasData(OracleConnection dbConn, String tableName) +78
cOracleDBManipulation.fillDataTable(String TableName, String SelectStatment) +75
cOracleDBManipulation.SequenceNextValGen(String SequenceName) +40
INSERTGENIMP_New.exportItemsNo(DataTable toDT) +643
INSERTGENIMP_New.btm_isert_Click(Object sender, EventArgs e) +551
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053
/////////////////////////////////////////////////////
Please I need to resolve this problem in near time as can.
Thanks for anybody help,
Eslam Fares
|
|
|
|
|
Oracle provides the error code - just google for ORA-00161. Looks like a problem with the different data providers you installed on the machines.
|
|
|
|
|
I guess, the question is, why would I use
char(255)
instead of
varchar(255)
When I specify varchar(255), does SQL sever allocate [255] per cell? or does it allocate dynamically? If it statically allocate [255] bytes, then I don't see if there's much practical difference between char(255) and varchar(255) -- well, except varchar always store a one (or two) byte termination character internally.
If SQL server allocates 255 bytes when i specify varchar(255), then what about varchar(MAX)?
Thanks
dev
|
|
|
|
|
|
I did but not sure if I interpreted MSDN correctly.
<br />
<br />
char [ ( n ) ]<br />
<br />
Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.<br />
varchar [ ( n | max ) ]<br />
<br />
Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes. The ISO synonyms for varchar are char varying or character varying.
varchar(255) --- this means SQL server will always allocate 255bytes? Or depending on actual values being inserted for a particular row?
If say row 1, "Name" = 25 bytes and row 2, "Name" = 225 bytes (say no other row longer than 225), then...
POSSIBILITY 1: SQL server always allocate 225 bytes (actual length of data, taken from row 2)
POSSIBILITY 2: SQL server always allocate 255 bytes for all rows as defined in column definition varchar(255)
POSSIBILITY 3: SQL Server allocate 25 bytes for row 1, and 225 bytes for row 2 (actual length of data vary from one row to the next)
My guess is - POSSIBILITY 3 is how SQL server actually behalves. This said, then why we bother specify varchar(n)? We should always specify varchar(MAX). Two reasons I can think of after a bit of digging...[^]
a. You cannot index anything varchar longer than 900 bytes
b. by MAX, you remove column max length validation provided for you at database level.
Am I missing something?
dev
modified 2-Feb-13 21:55pm.
|
|
|
|
|
Allocation of storage is the primary difference, but it won't affect how you work with the data.
The main difference you'll have to deal with is in trailing SPACEs -- CHAR stores them, VARCHAR doesn't. Which can occasionally cause confusion, in that with VARCHAR 'BOB' and 'BOB ' will test equal and LEN report the same length; you'll want to use DATALENGTH for VARCHAR -- this sort of thing bit me again this week. 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
