|
Try this,
SELECT SUBSTRING('C1H1D01',1,2)
Niladri Biswas
|
|
|
|
|
hello
CURSOR bad (Performance) in Oracle and MySQL as well? Or just M$SQL?
Thanks
dev
|
|
|
|
|
Cursors generally don't perform well in any database. Although I read that Oracle have done a lot of work to optimise cursors so it may not be as bad in Oracle. However, it is generally best to do as much as you can using set based queries (i.e. queries that operate on a lot of rows at once) as that is what databases are good at.
|
|
|
|
|
|
Colin Angus Mackay wrote: I read that Oracle have done a lot of work to optimise cursors
Yes that's right. In fact, I think it is the only way to return a set of results from a oracle stored procedure.
|
|
|
|
|
Hey, Guys Let me just lay out the problem:
I have 2 tables
table Function
FID
FName
Table Materials
MID
MName
FunctionID1 (foriegn key for FID)
FunctionID2 (foriegn key for FID)
Now I want to select all columns from Materials table, instead of FunctionID1 and FunctionID2, I want to have their related text (FName).
SELECT M.MID, M.MName, F.FName, F.Fname FROM Materials AS M INNER JOIN Function AS F
ON FunctionID1=F.FID OR FunctionID2=F.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)
I want this query to search for records in which FunctionID 1 is The input or FunctionID2 is equal to related input!!!! buy when I do this, It fetches two rows for a row and in each one FunctionID1 and FunctionID2 are the same !
How should I fix this???
|
|
|
|
|
If the content of the rows is the same DISTINCT might be useful. Using OR in a join statement is always going to cause you problems.
You may also want to use sub selects, do your initial filtering in the from (select)
Select *
from (Select * from Function where Id1 = @ID1) X
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How about using two joins like this:
SELECT M.MID, M.MName, F1.FName, F2.Fname
FROM Materials AS M
INNER JOIN Function AS F1 ON M.FunctionID1=F1.FID
INNER JOIN Function AS F2 ON M.FunctionID2=F2.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Actually, I think you'd want to use two LEFT OUTER JOINS, as you'll want to return a match on FunctionID1 or FunctionID2 (unless I've read this all incorrectly):
select M.MID, M.MName, F1.FName [F1Name], F2.FName [F2Name]
from Materials M
left outer join Functions F1 on F1.FID = M.Function1ID
left outer join Functions F2 on F2.FID = M.Function2ID
where F1.FID is not null or F2.FID is not null
The WHERE clause will elimitate records where neither the Function1ID nor Function2ID match the input value. And the double LEFT OUTER JOINs will cause records where either the Function1ID or Function2ID match the supplied input value to be pulled.
Hope in one hand and poop in the other; see which fills up first. Hope and change were good slogans, now show us more than words.
|
|
|
|
|
Hello,
I am new to Writing Triggers in SQL Server 2000.
I have not written any triggers uptil now.
So it would be great if someone helps me in this problem.
I have a table with a field Serial No and SrCount
I want to write a trigger such that when a record is added in the table, it will take the serial no of the new record and get the count of that serial no present in the table and update the SrCount field in the new record with the Count Results.
Please anyone could help, I am totally new to triggers.
Any help could be of great help.
Thanks in Advance. 
|
|
|
|
|
Do you know how to update the SrCount without using a trigger?
I are troll
|
|
|
|
|
Feels like a design error to me, I would ask why you need to count stored in another table.
Triggers are EVIL, debugging triggers can become a support nightmare. If it was essential I would do the update in the insert procedure rather than use a trigger (achieves the same thing, almost, but is easier to support).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Stay away from triggers. You should do that within the stored procedure that does the insertion.
Also, be aware of concurrency issues (like race conditions).
|
|
|
|
|
Hi
Can i convert an SQL Server 2005 bank file to SQL Server 2000?
|
|
|
|
|
In theory, and depending entirely on the structure of the 2005 database, possibly.
But given the way you have worded the question, I doubt it.
Why would you want to?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Helloo all..I need to identify the entities and relationship for a company's database and then draw a suitable set of E-R diagrams. Draw a DFD and perform a structure Analysis to produce and appropiate Structure Chart. I am stuck with this and would like to ask if anyone could help me out with this.
Need a database to keep track of the stock in each outlet as well as customer information. It has supplies of electrical goods which include such items as:
cables
plugs and sockets
light bulbs
fire alarms
security alarms
CCTV equipment
these goods are produced by different manufacturers, a particular outlet will have different manufacturer's versions of the same item. In the new database must be stored the following:
the address and telephone numbers of all the outlets
a stock inventory for each outlet, stating the quantity of each item currently in stock and the date of the next delivery. for each item must be listed:
a full specification of the item
the manufacturer's contact details
the item's current price
any discounts currently available
|
|
|
|
|
This smells like homework so I refer you to all the responses that such questions elicit on this forum.
Regards
David R
|
|
|
|
|
I Have 3 Tables
1.
Productmaster:
Columns:ProductId,PName,PDescription
2.
Attributes:
Columns:AttributeID,attName
this Table has Three Entries
1 Brand
2 Category
3 Artist
3.ProductAttributeValues
Columns:paId,ProductId,AttributeID,AttributeValues
Conditions:1 Product can have Multiple Attributes
Needed Output
--------------------
ProductId | ProductDesc | Brand | Category | Artist..
1 sadasd Brand1 Category1 Artist1
2 sadasds Brand2 Category3 Artist4
I NOt Getting What should I Do To Get This O/p
Pls Help Me
Please
Thanks in Advance
|
|
|
|
|
Try This
select * from Productmaster a,ProductAttributeValues b,Attributes c
where a.ProductId=b.ProductId and b.AttributeID=c.AttributeID
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
u r not getting this is not so easy
there r 3 entries for each product in productattributevalues table
thats why i am adding 3 columns to the output with values displaying attValue
|
|
|
|
|
|
Leave It ,
Thanks Yar, I Got The Answer
|
|
|
|
|
can u post the answer..,
am also working with complex quries on database.,
Your Answer may be helpful for me.
Thanks
Rajesh B --> A Poor Workman Blames His Tools <--
|
|
|
|
|
USE [rhythmhouseDynamic]
GO
/****** Object: View [dbo].[vw_SearchStationery] Script Date: 04/17/2009 16:34:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_SearchStationery]
AS
SELECT dbo.ProductLine.prodlineID as 'ProductLineId', dbo.ProductLine.prodlineName as 'ProductLineName', dbo.ProductMaster.ProductId, dbo.ProductMaster.ProductTitle,
dbo.ProductMaster.ProductDescription, dbo.ProductMaster.ProductShortdescription, dbo.ProductMaster.ProductSynopsis
,ProductAttributeValues_Brand.ComputedAttributeValue AS [Brand]
----Add New Attributes Here
FROM dbo.ProductLine INNER JOIN dbo.ProductMaster
ON dbo.ProductLine.prodlineID = dbo.ProductMaster.ProductlineID
LEFT OUTER JOIN dbo.ProductAttributeValues AS ProductAttributeValues_Brand
ON dbo.ProductMaster.ProductId = ProductAttributeValues_Brand.ProductId
----Add New Tables And Join Conditions Here
WHERE (dbo.ProductLine.prodlineID = 7)
AND (ProductAttributeValues_Brand.attId = 265 or ProductAttributeValues_Brand.attId is null)
----Add AttributeId Where Condition Here
.............................................
Bye Best Of Luck Keep In Touch
|
|
|
|
|
I would try a natural join first since the fields joining the tables have the same names.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
