February 17, 2015 at 7:18 am
Attempting to build a report were you can place a specific code in the parameter field and it will return all row values based on that particular code. I have a similar report that works great, but the specific code is just in 1 column, the one I'm trying to create has the potential to have that code in up to 20 different spots. I have the report built, but the issue I'm facing is linking the parameter. Is there a way to link 1 parameter to multiple column options?
Here's an example:
Docflo Distribution Group Queue Status Pend1 Pend 2 Pend 3 Pend 4 Pend 5
ABC ABC1 Catch All NEW 123 126 125 621 129
ABC ABC1 Various PENDED 621 123 872 542 630
Right now if I were to link the parameter to the Pend1 field, I would get every line I wanted that had Pend "123", but it would not include any of the lines where Pend "123" was in Pend 2, Pend 3, Pend 4, so on.
How would I link the parameter to more than 1 column so it would return all rows with a specific code no matter which Pend column it was in?
February 17, 2015 at 8:59 am
wouldn't that just require a bigger WHERE statement?
WHERE Pend1=@param
OR Pend2=@param
OR Pend3=@param
OR Pend4=@param
OR Pend5=@param
t think i would change it to a union instead, for a performance test and avoid a big table scan due to the OR statements
SELECT ... WHERE Pend1=@param UNION
SELECT ... WHERE Pend2=@param UNION
SELECT ... WHERE Pend3=@param UNION
SELECT ... WHERE Pend4=@param UNION
SELECT ... WHERE Pend5=@param
Lowell
February 17, 2015 at 9:32 am
I've tried using this and it just times out due to the large volume of data. Any other suggestions? I do appreciate the assistance!
February 17, 2015 at 9:41 am
the union logic, assuming you have indexes on each of the Pend columns would be the best solution.
the OR would work, but perform horribly on big tables.
do you have indexes on the Pend* columns that you would be searching on? the actual query /where statement being used is really important here as far as indexing and perforamnce, can you provide better details?
Lowell
February 17, 2015 at 1:42 pm
I apologize but not sure what you mean by having indexes on them. Could you elaborate more? Do you have more info on the UNION topic?
February 17, 2015 at 1:48 pm
since your result looks like a matrix, I just have to ask - what does your underlying dataset look like? (or the table in question).
If your data table isn't normalized properly, then this is going to be a huge pain. (that would account for the UNION query stuff.)
February 17, 2015 at 2:37 pm
rcharbonneau1 (2/17/2015)
I apologize but not sure what you mean by having indexes on them. Could you elaborate more? Do you have more info on the UNION topic?
any intelligent answer depends on the structure of your data.
if your table actually has twenty columns named Pend1,Pend2 etc vs a query that is pivoting rows to columns is a crucial peice of this question.
if you can show us the actual query you are using, it would help us add a bit of peer review and clarity, otherwise it's just wild guesses based on what i THINK your table looks like.
Lowell
February 17, 2015 at 5:17 pm
What if the value of 123 appears in more than one Pend column?
WITH SampleData
(
Docflo, Distribution, [Group], [Queue], [Status], Pend1, Pend2, Pend3, Pend4, Pend5
) AS
(
SELECT 'ABC','ABC1','Catch','All','NEW',123, 126, 125, 621, 129
UNION ALL SELECT 'ABC','ABC1','Catch', 'Various','PENDED', 621, 123, 872, 542, 630
)
SELECT *
FROM SampleData a
CROSS APPLY
(
VALUES(Pend1),(Pend2),(Pend3),(Pend4),(Pend5)
) b (Pend)
WHERE Pend = 123;
If 123 only appears in one of the columns, the above will work. If 123 could appear in more than one, you'll get dups (but those would be easy enough to eliminate).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2015 at 5:38 pm
Another option to consider is IN
WHERE '123' in (Pend1,Pend2,Pend3...)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 17, 2015 at 8:01 pm
CELKO (2/17/2015)
what does your underlying dataset look like? (or the table in question). If your data table isn't normalized properly, then this is going to be a huge pain. (that would account for the UNION query stuff.)
I am inclined to agree. This looks like a repeated group, so the table is not even in First Normal Form. But we have no DDL. :crying:
As the OP said Joe, he's building a report, so I would hope that this is merely an intermediate table. But as you said, we have no DDL to be sure. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 18, 2015 at 4:34 pm
CELKO (2/17/2015)
As the OP said Joe, he's building a report, so I would hope that this is merely an intermediate table. But as you said, we have no DDL to be sure. 🙂
This drives me nuts. SQL is for data, not for any kind of presentation layer, like a report, screen, tweet, email, graphics, etc. This is the "ghost of COBOL" in RDBMS; when all data was for PICTURE display in the files, etc.
Let me tell you what drives me nuts. Often I've had the opportunity to work with developers of lesser than average skill. I've found that many "general" developers tend to know a little about many different things, like SQL, C#, Crystal, etc. but don't have deep skills in any of them.
If you happen to have a highly skilled SQL specialist on hand, the incremental effort to make the query deliver results that are close to the formatting required in the report, tends to ensure that the effort to render those results on the report is so significantly reduced that it keeps schedules intact.
One might say that I am a pragmatist.
There is also the question of which server has the most resources or load, so you'd want to shunt load to the lesser used or bigger server wherever possible.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 19, 2015 at 6:51 am
Thanks everyone, I appreciate the help here. The solution of using the OR funtion worked after making some minor adjustments!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply