Re : combinations function
-
I've created a function to check a number of combinations to chack if they exist in the database and if they do then set a indicator to 1,2, etc i've written the first combination but am just wondering if there is a easier way to do this ? as my way seems very long
The combinations are "field,Solve,Part,Info"
CREATE FUNCTION dbo.udf__Test_Act_ind(@field_1 char(50),@field_2 char(50))
RETURNS int
AS
BEGIN
DECLARE @Act_ind int
If @field_1 = 'field' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'solve' or
@field_1 = 'solve' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'part' or
@field_1 = 'part' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'info' or
@field_1 = 'info' and @field_2 = 'field'
Begin
Set @Act_ind = 1
End
Else
Set @Act_ind = 0
RETURN @Act_ind
END
-
You're the really confusing type of SQL-Cowboy
What are you trying to find out in fact? Whether any combination of specified four terms exists?
_/_/_/ paramind _/_/_/ -
Am trying to find out if the combinction exist then set a value to it 1,2,3 etc
For example if i have the word "field" and "part" in 2 rows where the unique id is the same then which once should be the more important hence the order is 1=Field, 2=solve,3=part etc...
-
-- simple version:
SELECT
UniqueID,
field1,
field2,
CASE field1 WHEN 'field' THEN 1
WHEN 'solve' THEN 2
WHEN 'part' THEN 3
ELSE 100 END
AS OrderCriteria1,
CASE field2 WHEN 'field' THEN 1
WHEN 'solve' THEN 2
WHEN 'part' THEN 3
ELSE 100 END
AS OrderCriteria2
FROM tableSort
ORDER BY
UniqueID,
OrderCriteria1,
OrderCriteria2
-- the better version:
CREATE TABLE SortValue (StringValue VARCHAR(255), RankValue INT)
INSERT SortValue VALUES ('field', 1)
INSERT SortValue VALUES ('solve', 2)
INSERT SortValue VALUES ('part', 3)
SELECT
UniqueID,
field1,
field2
FROM tableSort AS S
INNER JOIN SortValue AS SV1
ON S.field1 = SV1.StringValue
INNER JOIN SortValue AS SV2
ON S.field2 = SV2.StringValue
ORDER BY
UniqueID,
SV1.RankValue,
SV2.RankValue
-- and last but not least: I really hope that this is not some kind of a bill of materials you're trying to manage this way ...
_/_/_/ paramind _/_/_/ -
I omitted existence and null tests on purpose. This should of course be considered.
Taking the ranks out to a small persistent table will give you more control on what really happens and will enable you to change/update the rank, integrate new values any time without any hassle.
_/_/_/ paramind _/_/_/ -
Sorry i think you must have pick me up wrong on the first out set, am not looking to extract data from the table i am writing the function for the combinations
In your examples you explain how to select the combinations, which is fine. But i was after a function like the one i did with the if else.... Is it bossible to put this into a case statement... ?? I had to write out every combination for this is there a easier way...
My example ...
If @field_1 = 'field' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'solve' or
@field_1 = 'solve' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'part' or
@field_1 = 'part' and @field_2 = 'field' or
@field_1 = 'field' and @field_2 = 'info' or
@field_1 = 'info' and @field_2 = 'field'
Begin
Set @Act_ind = 1
End
Else
Set @Act_ind = 0
-
>For example if i have the word "field" and "part" in 2 rows where the unique id is the same then which once should be the more important hence the order is 1=Field, 2=solve,3=part etc...<
This does look as if you were trying to order the items? Wrong?
I thought you're trying to order items by their field contents, which in turn have some defined non-alphabetical rank.
What I first assumed though is, that you're trying to sort on TWO fields per record. Otherwise you should not use field1 and field2 but value1 and value2 respectively. If so, this makes things even easier - one join less. And there's of course no need to extract data -> the above procedure can be used to calculate all the ranks at once! without executing a function repeatedly. Whether you use it dynamically or in an update-statement to store that rank does not matter.
_/_/_/ paramind _/_/_/ -
It looks to me like some search with evaluation which of the found items is most corresponding to criteria, or something like that. Not sure though... and I still don't know what should be the result and how to get there.
Francis,
when writing complicated SQL with AND and OR, you should always use parentheses to make sure the condition will be evaluated in correct way.
-
I am not trying to order the items in the table
Am only looking at the Value1, Value2 and setting the indactor.
Field = 1 ,part = 2, solve = 3
Example if value1 = 'field' and value2 = 'part' set the Act_ind = 1
The reason i set it to 1 is that Field is more important that part in the business
Example if value1 = 'part ' and value2 = 'solve ' set the Act_ind = 2
The reason i set it to 2 is that part is more important that solve in the business
-
Fine, you're setting the indicator. But WHY are you doing that? What is the purpose of the indicator and how is it used later? That's what we can not understand from your posts. How do you know that "solve" is more important, is that stored in some table? Or is it hardcoded in the function? There are no other words to be considered than these 4 - field,Solve,Part,Info?
BTW, what does it mean "whether they exist in the database"? As far as I can see from the UDF, you just pass 2 values to the function and process them independently on what is in the database... Also, you are not checking ALL combinations, but only some of them, namely those that contain 'field' in at least one of the parameters. I can't find anywhere under what conditions you assign other vlaue than 0 or 1 to the indicator, but you mention also 2, 3 ... /*EDIT*/ : Now looking again at your last post I think I begin to see the pattern, but please explain anyway, just to be sure. 🙂
Clearly, we can not answer your questions, unless you are more specific about the purposes and give us some more explanations about the principles that should be used. I begin to think that this could be some kind of homework from a course, where you yourself haven't been supplied enough information to answer these questions. If that's the case, please say so.
-
CREATE FUNCTION dbo.udf__Test_Act_ind (@field_1 char(50),@field_2 char(50))
RETURNS int
AS
BEGIN
DECLARE @id int
SET @id = 0
DECLARE @combinations TABLE ([ID] int, field char(50))
INSERT INTO @combinations VALUES (1,'field')
INSERT INTO @combinations VALUES (2,'Solve')
INSERT INTO @combinations VALUES (3,'Part')
INSERT INTO @combinations VALUES (4,'Info')
SELECT @id = c1.[ID]
FROM @combinations c1
CROSS JOIN @combinations c2
WHERE c1.field = @field_1
AND c2.field = @field_2
RETURN @id
END
This uses an inbuilt table with the four names you supplied but can be replaced using actual table
CREATE FUNCTION dbo.udf__Test_Act_ind (@field_1 char(50),@field_2 char(50))
RETURNS int
AS
BEGIN
DECLARE @id int
SET @id = 0
SELECT @id = c1.[ID]
FROM
c1
CROSS JOIN
c2
WHERE c1.field = @field_1
AND c2.field = @field_2
RETURN @id
END
Far away is close at hand in the images of elsewhere.
Anon. -
After re-reading your last post again, I begin to see the light... but what should happen if @field_1 = 'field' and @field_2 = 'forest' ? Is that rated 0 because of the forest or 1 because of the field?
-
There will only ever be these Values
'Feild', 'Part','Solve'' ,info' if otherwise the Act_ind = 0
There is my code...
Test it with this
Update [DB_NAME].dbo.Test_1
Set Act_ind = dbo.udf__Test_Act_ind(field_1,field_2)
select * from dbo.Test_1
/* My Question was is there are simpler way of doing this ? The return value is set in the Act_ind which in turn is used again for another function*/
P.S this is not a case study its something am doing in work to build up a report.
CREATE FUNCTION dbo.udf__Test_Act_ind(@field_1 char(50),@field_2 char(50))
RETURNS int
AS
BEGIN
DECLARE @Act_ind int
If (@field_1 = 'field' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'field')
Begin
Set @Act_ind = 1
End
Else
If (@field_1 = 'solve' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'solve')
Begin
Set @Act_ind = 2
End
Else
If (@field_1 = 'part' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'part')
Begin
Set @Act_ind = 3
End
Else
If (@field_1 = 'info' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'field') or
(@field_1 = 'field' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'solve') or
(@field_1 = 'solve' and @field_2 = 'info') or
(@field_1 = 'info' and @field_2 = 'part') or
(@field_1 = 'part' and @field_2 = 'info')
Begin
Set @Act_ind = 4
End
Else
Set @Act_ind = 0
RETURN @Act_ind
END
-
>/* My Question was is there are simpler way of doing this ? The return value is set in the Act_ind which in turn is used again for another function*/<
I hope you're not trying to tell us, that we shouldn't ask questions but shut up and solve your curious code? Remember where you are ...
You're talking >of doing THIS<. What's THIS? and what is "simple". Short syntax, more flexible syntax? less processing or I/O-needs? WHAT? Sure. There are many ways, as long as you don't care of the destination.
People here try to help. If you insist on growing grain in the desert, while all your problem is hunger, you might be out of reach ...
_/_/_/ paramind _/_/_/ -
yesprob with my solution
revised
better to put options in permanent table
CREATE TABLE [Priorities] ([ID] int, field char(50))
INSERT INTO [Priorities] VALUES (1,'field')
INSERT INTO [Priorities] VALUES (2,'Solve')
INSERT INTO [Priorities] VALUES (3,'Part')
INSERT INTO [Priorities] VALUES (4,'Info')
CREATE FUNCTION dbo.udf__Test_Act_ind (@field_1 char(50),@field_2 char(50))
RETURNS int
AS
BEGIN
DECLARE @id int
SET @id = 0
SELECT @id = CASE WHEN p1.[ID] < p2.[ID] THEN p1.[ID] ELSE p2.[ID] END
FROM [Priorities] p1
CROSS JOIN [Priorities] p2
WHERE p1.field = @field_1
AND p2.field = @field_2
RETURN @id
END
but if you want to use it to update another table this would be better
UPDATE a
SET a.ind = p.[ID]
FROM
a
INNER JOIN (SELECT CASE WHEN p1.[ID] < p2.[ID] THEN p1.[ID] ELSE p2.[ID] END AS [ID],
p1.field AS [field_1],
p2.field AS [field_2],
FROM [Priorities] p1
CROSS JOIN [Priorities] p2) p
ON p.field_1 = @field_1
AND p.field_2 = @field_2
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply