August 24, 2021 at 10:59 am
Could some one help me with setting up function in SQL to check
I have Category and value supplying as parameter. And Category exist in a JOB table. When fetch data from job table it will compaire data from parrmeter list and if category exists there then pick up value from there.
Category Value
ABC 1
DEF 2
Select Job Num, Category ,--Match category with category parameter and returns value if no match then returns 0
from Job
August 24, 2021 at 4:12 pm
First, I'd recommend posting some DDL so we can consume your data and do some tests. Nothing worse than untested code, right?
Looking at the image, a potential solution would be to use a UNION. Do a SELECT on the first table, joined to the second on Category and just grab the columns you care about. You now have the first 2 rows of your data set, you just need to UNION to get the last 2. The second SELECT joins on the static value of 'All' and your where clause is a NOT IN on the Category of the Category table. Something like:
SELECT *
FROM [Category]
JOIN [Job] ON [Job].[category] = [Category].[category]
UNION
SELECT *
FROM [Category]
JOIN [Job] ON [Category].[category] = 'ALL'
WHERE NOT EXISTS (SELECT category FROM Category CAT1 WHERE [Job].category = [CAT1].[category])
You will want to change the SELECT *'s to be the columns you care about. This doesn't match up with your description, but matches up with your code.png file. Your description says that if no match is found, then return 0, your image says that if no match is found, then use the value from "ALL". If you wanted to return 0 instead of the value from ALL, you would just change the second part query to change the FROM to be form the job table, remove the join, and the * becomes the Job Number and the static value 0. Where clause can remain the same.
I didn't do this in a function, but using the above, it shouldn't be challenging to convert to a function.
Now, to me this looks like it MAY be a homework type question. If this is a homework question, the above may give you the right answer, but may not be what they were looking for. For example, if they haven't taught you UNION yet, handing in an assignment with UNION in it may get you bad marks. Plus, most teachers and professors of SQL Server likely know about this site. So if you use the answer posted above, you MAY get no marks.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 25, 2021 at 3:32 am
Hello and welcome aboard. To get some really good help quickly, please read the article at the first link in my signature line below for how to create "readily consumable data". In most cases, you'll get quicker answers and they'll have been tested with code.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2021 at 4:31 am
SELECT
J.Job,
J.Category,
COALESCE(C.Value, C_All.Value) AS Value
FROM Job J
LEFT OUTER JOIN Category C ON C.Category = J.Category
/* if no match found in Category table, then use the 'ALL' Category row */
LEFT OUTER JOIN Category C_All ON C.Category IS NULL AND C_All.Category = 'ALL'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2021 at 8:02 am
If Category is static (ie ALL is always 3)
SELECT j.Job,j.Category,ISNULL(c.Value,3)
FROM Job j
LEFT JOIN Category c ON c.Category = j.Category
Otherwise Scott's solution
Far away is close at hand in the images of elsewhere.
Anon.
August 25, 2021 at 9:12 am
Thanks for your reply.
August 25, 2021 at 10:01 am
Function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. Function can return an only single value or a table.
August 25, 2021 at 10:09 am
Thanks Jakubowski, actually i am looking for one final result. Passing Category and value from SSRS report parameter and these parameter will compare category in the SQL query and provides result. So far I managed to create one function on SSRS report. But looking for some solution in SQL side. Trying to convert this in SQL.
Public Function MarginByJobCatFilter(JobCats() As Object, Margin() As Object, JobCat As String) As Decimal
Dim MarginOut As Decimal = 0
For i As Integer = 0 To JobCats.Length - 1
If Trim(LCase(JobCats(i))) = Trim(LCase(JobCat)) Then
If Margin.Length > i Then
MarginOut = Margin(i)
Else
MarginOut = Margin(Margin.Length - 1)
End If
End If
Next
if MarginOut = 0 Then
For i As Integer = 0 To JobCats.Length - 1
If Trim(LCase(JobCats(i))) = "*" Then
If Margin.Length > i Then
MarginOut = Margin(i)
Else
MarginOut = Margin(Margin.Length - 1)
End If
End If
Next
End If
Return MarginOut
End Function
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply