|
Dear All,
I am implementing a shopping where the prices of the products are structured according to the quantity taken
for examle:
1 = 1 Euro
3 = 0.80Euro
5 = 0.70 Euro
etc etc
The database is structured as follows:
quantity1 pricequantity1 quantity2 pricequantity2 quantity3 pricequantity3 etc
A quantity and pricequantity can be set to 0 if no further quantity groups exist.
I need a query where I pass a quantity and it gets the subtotal.
Can someone help please?
Thanks
Chris
|
|
|
|
|
I guess you can put a case statement in your select.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hello
Last week i have upgrade my OS xp home edition to xp professional becuase i want to start learning ASP.NET and ADO.NET but before i have backup my all projects which i have created with vb.net 2003, vb.net 2005 and few of them was sql server 2005
so after upgrading OS i have saved all those projects to it's default loaction and i have tried to run few of them in vb.net 2003 and vb.net 2005 so it was ok but when i checked projects with sql server then the problem starts and i am still fighting with it , when ever i try to run application it shows me error 26 and even if i try to configure the data adapter then it's same error so now i dont know what to do with this
error is "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by th fact the undre the default settings SQL Server does not allow remote connections.(Provider: Sql Network Interfaces, error : 26 - Error Locating Server / Instance Specified) " 
and i have also try to create new project from start but when i try to add sql data adapter it shows me same error
please help me with this
i have also check this web site
http://support.microsoft.com/kb/914277[^]
any help with this
|
|
|
|
|
Hi
I have created a Trigger Statement and assigned to CommandText property of
sqlCommand.While Try to Execute using
command.ExecuteNonQuery() it throws an exception like
{"Incorrect syntax near the keyword 'TRIGGER'."}
Created Trigger Statement:
CREATE TRIGGER SampleTrigger
ON dbo.sample
AFTER INSERT
AS begin
SET NOCOUNT ON
DECLARE @Name AS nvarchar(50)
SELECT @Name= i.Name from inserted i
DECLARE @Address AS nvarchar(50)
SELECT @Address= i.Address from inserted i
insert into triggertest(statusID,FieldName,FieldValue) values( 25,'Name',@Name)
insert into triggertest(statusID,FieldName,FieldValue) values( 25,'Address',@Address)
end
Can anyone help me?
Thanks in advance
Sri
|
|
|
|
|
Since you don't show the way you build up the string, my guess is that the string sent to the SqlCommand is not exactly the one shown.
For example:
string sql = "CREATE TRIGGER SampleTrigger" +
"ON dbo.sample";
will actually result in a string that reads:
CREATE TRIGGER SampleTriggerON dbo.sample
|
|
|
|
|
i want to write a query to adding the numeric values in one row
how to do that ?
regards
|
|
|
|
|
So whats the prob??
select column1+column2+column3+.... from table.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Hi all,
i m new in database,i m using a SDI type appliaction in VC++ 2005, and MS-Access Datase through ODBC.
i m add class using MFC ODBC Consumer,and add desired database and its table for code.
By default it is derived from CRecordset. here GetDefaultConnect() for default connection and GetDefaultSQL() is present.
i want to know that in GetDefaultConnect() the connection string or path of database present, if i run my application on other computer then it gives an error, can i change the path in GetDefaultConnect(),
i dont knoe how can use it.
please tell me what string i pass in GetDefaultConnect() for execute my application properly for each computer.
Thanks in advance.
I m waiting for ur Valuable suggetions.
IN A DAY, WHEN YOU DON'T COME ACROSS ANY PROBLEMS - YOU CAN BE SURE THAT YOU ARE TRAVELLING IN A WRONG PATH
|
|
|
|
|
|
Hi All,
I have the following defined in a class that I'm using to access and export the contents of an SQL Compact database. After having read around some I was under the impression that the following code would work, but its failing to actually LOAD any data from the database. Any help would be great.
to create my typed dataset:
SSdbDataSet ds = new SSdbDataSet();
The method to load the entire database from the .sdf file is:
public void ExportDatabaseXML()
{
ds.Clear();
ds.localSamples.Load(GetLocalSamplesReader());
ds.WriteXml("SStest.xml", XmlWriteMode.WriteSchema);
}
And there is this 'utility' method to create the reader:
private IDataReader GetLocalSamplesReader()
{
return ds.localSamples.CreateDataReader();
}
I'm a bit stuck now as I was under the impression that the Load() method pulled all the data from the underlying datatable.
Any pointers would be great.
Jammer
Going where everyone here has gone before! 
My Blog
|
|
|
|
|
Hey Guys,
Lets say we have a table T1 that have a numeric field F:
Table T1 (int ID, int F)
I need to update this table and set F to the MAX +1 to insure uniqueness:
UPDATE T1
SET F = (SELECT ISNULL(MAX(F),0) + 1)
WHERE ID IN(1,2)
The thing is I end up with both IDs 1 and 2 having the same value for F. I remember doing but what do I have to change so that Max is calucated for row in T1 so that if my initial table is like:
ID F
1 1
2 2
Running the update query will get the following result
ID F
1 3
2 4
Thanks.
|
|
|
|
|
UPDATE T1<br />
SET F = (SELECT top 1 ISNULL(MAX(t2.F),0) + 2 from T1 as t2 where t2.id=T1.ID)<br />
WHERE ID IN(1,2)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Any easy way to do that is:
DECLARE @I INT
SELECT @I = MAX(F) FROM T1
UPDATE T1
SET @I = F = @I + 1
Change T1 and F for your actual names.
|
|
|
|
|
I have a table similar to the following :-
Id, BigInt, IsIdentity
Name, VarChar(50)
ParentId, BigInt, Allows Nulls
Therefore I could have data as follows
Id Name ParentId
-- ---- --------
1 Fred Null
2 Fred 1
3 Fred 1
4 Fred 2
5 Fred 4
Therefore data is structured as follows :-
1, Fred
..2, Fred
....4, Fred
......5, Fred
..3, Fred
What I need to do, (and this is where my weak T-SQL lets me down) is when the Name is change for row 1, an update cascades through the table and updates and related row i.e. where ParentId = Id of row updated.
My thoughts are to use an trigger on Update, but I need some help with the code.
Also, if I understand the update tigger correctly, and the option is enabled at Database level, the rows being updated by the trigger will cause the trigger to fire again, thus the update with cascade down the tree.
Any assistance is much appreicated.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Hi, try something like this:
CREATE TRIGGER [Table1_Update] ON [dbo].[Table1]
FOR UPDATE
AS
IF EXISTS(SELECT * FROM inserted)
UPDATE Table1 SET [Name] = I.[Name] FROM Table1 T INNER JOIN inserted I ON T.ParentID = I.ID
You're right in that the trigger recurse option needs to be enabled (disabled by default), but be aware that you're limited to 32 levels in the recursion stack (sql server 2000 - don't know limit in SQL2005), so you're tree must be limited to max 32 levels
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for the example code and the additional info.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Steve
I have a nasty feeling you are setting yourself up for a disaster. I don't understand your requirement but some of your statements are suspect.
the field name should never be used in a relationship. It should be stored once and the ID used to define your heirachy. You need 2 tables for this operation.
Name Table
ID int, Name varchar(?)
Link Table
ID int, ParentID int
Id ParentId
-- --------
1 Null
2 1
3 1
4 2
5 4
and join out to the name table (twice for ID and ParentID).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I understand your concerns and thanks for raising them. I did not go too deeply into what I need to achieve because I did not think it relevent to the problem (i.e. my lack of knowledge) at the time.
Just to make things clear, the problem I have is will a database created and maintained by a third-party product, namely Sage SalesLogix CRM package. I have a requirement where the principle contact at a parent account is also the principle contact at each associated child account, but SalesLogix does not cater for this requirement as standard. Therefore some coding has been done on SalexLogix that when a child account is associated with the parent account, then principle contact of the parent is copied to child. So far so good.
Now the problem is, that when the details of the contact is changed, it is only for the that one record. This is where the trigger comes into play. What I want to do is when the details of a contact is changed, all duplicate(ish) records are updated to reflect the change, hence my example showing a column called 'Name' with the same value.
Thanks again for you concern.
Best regards
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Steven, this should be held us as a classic DB design error 101 - typical of Sage.
Are you attempting this in SageLogix (not), if so then my sympathy.
I would naturally [avoid] triggers because I hate them . If you can inject an Exec into the save process I would use a stored proc to synch the data based on the ParentID and new contact name. I presume the user can update the contact from either the child or the parent.
Good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using following query ..
select siid as Sid,biid as Bid,(select iname from Ol_items where iid=siid) as SIName,(select iname from Ol_items where iid=biid) as BIName from Ol_Wishlist
ouput is
Sid Bid SIName BIName
10 27 NULL Spice
here SINAME = null value
but I want if not null display the data ..if null,Instead of NULL Some text like NO and
Sid Bid SIName BIName
10 27 NO Spice
anybody know plz reply me..
thanks
Rajendran.AL
|
|
|
|
|
This should do it (around each of the subqueries):
COALESCE((select iname ...), 'NO') as ...
Look up the COALESCE function in the documentation for details.
Peter the small turnip
(1) It Has To Work. --RFC 1925[^]
|
|
|
|
|
Its working fine.....Thanks alot...
Rajendran.AL
|
|
|
|
|
Personally I would use a CASE for that, although as the other poster suggested a COALESCE would work.
|
|
|
|
|
|
Giorgi Dalakishvili wrote: You can use ISNULL
yes, but coalesce is standard, so I prefer it to isnull which is not.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
