|
The approach loyal ginger referred to would be to use something like:
SELECT Price, Quantity, Price * Quantity AS Total
FROM table
WHERE condition
This gives you a record set that has a total field, without saving one on the disk. For an entry form you would use something like David Mujica's suggestion for the UI. This does violate your stated requirement of saving the field to disk. You might want to explore getting that requirement changed. All database fanatics will tell you so. 
According to the rules of relational databases, the total field does not belong stored in the table. It would be a violation of "normal form" (3rd normal form if I am not mistaken). Practically, it adds no information not already present and is subject to the creation of data anomalies from updates. If someone else wrote a program that uses the same database and in one place changes quantity, but forgets to update total, well, you get the picture. Triggers could be a way to deal with this, but the Access (Jet) backend doesn't have them. If you use an Access front end to a different database backend, that backend might have this capability.Please do not read this signature.
|
|
|
|
|
Good Day All
I have an Sp and UDF. the UDF cant take #tables. so i want to incoporate the functionality that is being provided by the udf and make it part of the code. the first part of my statement creates a solid table that is being used in the UDF
truncate table temp
INSERT INTO temp
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
END
ELSE
BEGIN
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
END
and later in my Sp i have this line of statement
SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV
which has no problem and later i want to update the Filed in the #TempSummary table like this
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,#TempSummary.actv)
now the problem is here, the Concat is the UDF. defined like this
create FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM temp (NOLOCK)
WHERE temp.Descr = @Name And temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END
now i want to incoporate the functionality of the UDF and get rid of it. The UDF use the Temp created in the First example code based on the two fields. and generate the string and update the table #TempSummary. How can i incoporate this functionlity without using UDF.
ThanksVuyiswa 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@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I'm trying to use both OleDbClient and OracleClient to get Tables/Views Schema but some data is incorrect.
only OracleDataReader.GetSchemaTable(), retrieves some correct data
but still I have the following problems:
1- For views, BaseTableName is the view name not really the name of base table of the view
2- Also BaseColumnName is the column name in the view not in the base table
3- I cannot find IsAutoIncrement or IsReadOnly columns in the schema table
4- Some data are incorrect with views only, such as IsKey:
IsKey is false for Primary Key columns, although it's retrieved correctly if I'm getting schema of table directly not schema of the view
can anybody helps me??
|
|
|
|
|
What do I need to download/install to be able to create mysql db's and to access the db's? I work in a windows Visual Studio C++ environment and want to be able to create and run both the db and client on the same development machine. Later for production the db and the client may or may not run on the same box.
Any help would be appreciated.
Thanks
Al Kurlansky
|
|
|
|
|
Alan Kurlansky wrote: What do I need to download/install to be able to create mysql db's and to access the db's?
The Community Server, and optionally a connector. You can use the Workbench to execute queries against the database, a bit similar to SQL Management Studio.I are Troll 
|
|
|
|
|
Hi all,
I want to write a query in such a way to find the list of records which are between two dates i.e., consider the two dates are StartDate and EndDate.
Thanks in advance.
|
|
|
|
|
This is an extremely simple query that is well documented. Have you even attempted to look?
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Look at the
BETWEEN keyword.
As Mark said, this is very basic T-SQL. What have you researched?I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
| Just one more think that i would like to add is the datatype/format of your datetime variables, you may need to convert. Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Hi All,
I'm looking to be able to predefine a particular user input as String, Int, or Dec (maybe others), as the particular case may be.
The available datatypes are stored in a table, associated with the property (see below) that they apply to.
When the user submits the filled in textbox, that entry will be put in a table column, probably as a varchar, and converted as needed, to or from the appropriate datatypes as predefined.
For example, the textbox may be asking for the property of "Length," for which "Dec" is the predefined datatype. Or, the textbox may be asking for the property "Count," for which INT would be the datatype, or "Color," for which varchar would be the datatype. A single column will store all of the results, no matter which kind of datatype is actually involved.
The Question: Is there a standard way of doing this kind of thing? Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?
|
|
|
|
|
Member 4125480 wrote: Is it simply a bad idea to do what I'm talking about doing?
Yes.
You could store the values as string (VARCHAR) and convert as necessary, but this is frought with dangers and misuses. Another option may be use a UDT, user defined type. Or possible use an xml column and store value and data type information.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
I looked up UDTs today, and one thing that has come up is that they aren't supported in Azure, which is somewhat part of the long term plan.
|
|
|
|
|
Member 4125480 wrote: Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?
I can throw some mud at you. It is a horrible idea.
Come to think of it, are you by any chance the Lotus Notes architect?
 Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
modified on Monday, February 8, 2010 7:57 AM
|
|
|
|
|
Member 4125480 wrote: The Question: Is there a standard way of doing this kind of thing? Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?
In effect, you'd be storing everything in a list, without type-safety. Using a database would be kinda overkill where a textfile would suffice. A bit of a mean remark, but a database is best suited for relational data
Member 4125480 wrote: or "Color," for which varchar would be the datatype.
I'd expect a color to be a number. You'd best prepare for a lot of conversion-problems, mostly centered around datetime and decimals.I are Troll 
|
|
|
|
|
Hi.
i am currently building a project that contains data on a SQL server (2008),
i need to move some data from the server to a portable media (like SDF file or access file), the structure in both are the equal.
at certain queries i will want to query both databases , the server and portable one (so only one who has the portable database on the USB can get true results).
can anyone tel me which format would be the easiest to deal with this? for example is there a format that i can use to attach to the database and create a UNION query that should query the server and the file at once?
is it "healthy" to use a MDF file as the portable database and attach and deattach every time i want to use the database and then create a UNION query to the servers database?
|
|
|
|
|
|
Hello guys, i am using a module.
I want to run a query whenever user updates an article.
Reason is: During article update Fileid becomes FileID under ImageURL column,
and this causes broken image paths for my case.
I want to keep this Fileid thing same whenever an update occurs at database table.
Following trigger works for new entries:
CREATE TRIGGER FileID_replace ON Articles
AFTER INSERT
AS
BEGIN
update Articles set ImageUrl=REPLACE(ImageUrl,'FileID','Fileid') where ArticleID = (select max(ArticleID) as ArticleID from Articles)
END
However i need a one to keep Fileid whenever an update occurs.
Would you please help?
Thank you.
|
|
|
|
|
So create an UPDATE trigger. What difficulties are you having?
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Can you tell me which query to use?
I tried one, but it works for only latest entry. If editing a previous entry, it does not work. Because my query was relying on increase on articleId.
|
|
|
|
|
Maybe you'd like to show what you have tried.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Sure: (I simply changed AFTER CREATE with AFTER UPDATE) But i guess i am missing something.
I tried following:
CREATE TRIGGER FileID_replace ON Articles
AFTER UPDATE
AS
BEGIN
update Articles set ImageUrl=REPLACE(ImageUrl,'FileID','Fileid') where ArticleID = (select max(ArticleID) as ArticleID from Articles)
END
GO
|
|
|
|
|
Of course these queries are only going to work for the last record, that's what you're asking for.
idumlupinar wrote: select max(ArticleID)
If you read the documentation for triggers you will understand. Query the inserted or updated to get the record currently being affected.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
Well, i am not a SQL Developer yet, i am trying to learn anyway
Can you share an url that would explain my case clearly?
I reviewed a few resources but i couldn't understand most of them 
|
|
|
|
|
Try like this:
DECLARE @MyId VARCHAR(50)
SET @MyId = SELECT ID FROM INSERTED
UPDATE ........ WHERE ID = @MyId Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Dictionary Table; Records in only 2 lines (for example, a table of sex: male, female) or as much as 40-50 lines and changing of future data in the table Insert / Update / Delete operations possibility of very low.
Combining these tables in a single table, Does it make sense?
For Example;
TBL_Sex
1 Male
2 Female
TBL_WorkType
1 Full-Time
2 Part-Time
TBL_BloodType
1 A Rh(+)
2 B Rh(+)
3 AB Rh(+)
4 0 Rh(-)
5 A Rh(-)
6 B Rh(-)
7 AB Rh(-)
8 0 Rh(-)
Instead of following table;
TBL_DICTIONARY
ID GROUP VALUE
-- ----- -----
1 Sex Erkek
2 Sex Kadın
3 WorkType Yarım Gün
4 WorkType Tam Gün
5 BloodType A Rh(+)
6 BloodType B Rh(+)
7 BloodType AB Rh(+)
8 BloodType 0 Rh(-)
9 BloodType A Rh(-)
10 BloodType B Rh(-)
11 BloodType AB Rh(-)
12 BloodType 0 Rh(-)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
