March 16, 2009 at 7:37 pm
I'm writing a stored procedure for an SRS report (SQL 2005 and Visual Studio 2005). This is a vendor-supplied database and I don't have the luxury of modifying/adding fields.
One field on a particular table is used for 4 different fields on the report; what differentiates each one is the value in a different table. Two of the four fields in the report can be longer than the varchar length of the field, so I will need to concatenate (sp?) the same field more than once for each row of data.
I have removed several fields in my example to (hopefully) make it easier to see what I'm try to do...
In my example below, the field "G.NOTE as Remarks" might take 4 or 5 copies of the field.
There is an additional field on "G" called "SQL_NUM" with a value of 1 or 2 or 3, etc that dictates the order that they need to be processed.
Any guidance would be greatly appreciated!
Thanks,
Bob
DECLARE @IDQuoteODBC varchar(9)
SELECT distinct A.ID_CUST_ODBC
,A.ID_QUOTE_ODBC
,G.NOTE as Remarks
,B.CODE_USER_3_ES
,H.NOTE as 'Tool Description'
,I.NOTE as 'Est Sample Delivery'
,J.NOTE as 'Price'
FROM
pil.ES_QUOTE_HDR A JOIN
pil.ES_QUOTE_EST C ON A.ID_QUOTE_ODBC = C.ID_QUOTE_ODBC JOIN
pil.ESTMAS_HDR B ON B.ID_EST = C.ID_EST_ODBC LEFT OUTER JOIN
pil.ES_QUOTE_HDR_NOTE D ON A.ID_QUOTE_ODBC = D.ID_QUOTE_ODBC JOIN
pil.ES_QTY_BREAK E ON B.ID_EST = E.ID_EST LEFT OUTER JOIN
pil.ES_QUOTE_REV F ON A.ID_QUOTE_ODBC = F.ID_QUOTE_ODBC LEFT OUTER JOIN
pil.ESTMAS_NOTE_OPER G ON B.ID_EST = G.ID_EST LEFT OUTER JOIN
pil.ESTMAS_NOTE_OPER H ON B.ID_EST = H.ID_EST LEFT OUTER JOIN
pil.ESTMAS_NOTE_OPER I ON B.ID_EST = I.ID_EST LEFT OUTER JOIN
pil.ESTMAS_NOTE_OPER J ON B.ID_EST = J.ID_EST
WHERE RTRIM (LTRIM(A.ID_QUOTE_ODBC)) = @IDQuoteODBC
AND G.ID_OPER = 900
AND G.FLAG_PRNT_QUOTE = 1
AND H.ID_OPER = 901
AND H.FLAG_PRNT_QUOTE = 1
AND I.ID_OPER = 902
AND I.FLAG_PRNT_QUOTE = 1
AND J.ID_OPER = 903
AND J.FLAG_PRNT_QUOTE = 1
March 16, 2009 at 7:56 pm
[font="Verdana"]I guess I must be slow. What are you wanting your stored procedure to actually do?[/font]
March 16, 2009 at 7:59 pm
Bruce W Cassidy (3/16/2009)
[font="Verdana"]I guess I must be slow. What are you wanting your stored procedure to actually do?[/font]
It is going to feed an SRS report...
March 16, 2009 at 8:12 pm
No, we aren't sure what you want your output to look like.
One field on a particular table is used for 4 different fields on the report; what differentiates each one is the value in a different table.
Does the above quote mean you want to populate one of four different fields from the "one field" based on the value in the column of another table? We can make that happen with case statements.
Two of the four fields in the report can be longer than the varchar length of the field, so I will need to concatenate (sp?) the same field more than once for each row of data.
Concatenation means tying multiple values together into a single string. That seems to contradict the first quote. When you say they can be longer, that's not a problem.
If you could possibly show us a sample of data for the relevant columns and what you want the output to look like in the resultset, I'm sure someone can help you.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2009 at 8:25 pm
Bob Hovious (3/16/2009)
Sorry for the confusion...
Here is a shortened version of the stored proc...
DECLARE @IDQuoteODBC varchar(9)
set @IDQuoteODBC = '1018'
SELECT distinct --A.ID_CUST_ODBC
A.ID_QUOTE_ODBC
--,A.NAME_CUST
,E.QTY_BREAK_ES
,G.SEQ_NOTE
,G.NOTE as Remarks
FROM
pil.ES_QUOTE_HDR A JOIN
pil.ES_QUOTE_EST C ON A.ID_QUOTE_ODBC = C.ID_QUOTE_ODBC JOIN
pil.ESTMAS_HDR B ON B.ID_EST = C.ID_EST_ODBC LEFT OUTER JOIN
pil.ES_QUOTE_HDR_NOTE D ON A.ID_QUOTE_ODBC = D.ID_QUOTE_ODBC JOIN
pil.ES_QTY_BREAK E ON B.ID_EST = E.ID_EST LEFT OUTER JOIN
pil.ES_QUOTE_REV F ON A.ID_QUOTE_ODBC = F.ID_QUOTE_ODBC LEFT OUTER JOIN
pil.ESTMAS_NOTE_OPER G ON B.ID_EST = G.ID_EST
WHERE RTRIM (LTRIM(A.ID_QUOTE_ODBC)) = @IDQuoteODBC
AND G.ID_OPER = 900
AND G.FLAG_PRNT_QUOTE = 1
Here is the current output...
[font="Courier New"]
ID_QUOTE_ODBC QTY_BREAK_ES SEQ_NOTE Remarks
------------- --------------------------------------- --------------------------------------- ------------------------------------------------------------
1018 1 1 this is a test how long is the line seq #1 12345678901234567
1018 1 2 this is seq #2 of oper 900 this should print
1018 1 4 seq 3 should not have printed but this seq 4 should (900)
1018 250 1 this is a test how long is the line seq #1 12345678901234567
1018 250 2 this is seq #2 of oper 900 this should print
1018 250 4 seq 3 should not have printed but this seq 4 should (900)[/font]
The output should be...
In this scenario, there should be two rows of data. In the first row, the "Remarks" field should be "Remarks where SEQ_NOTE = 1" concatonated with "Remarks where SEQ_NOTE = 2" concatonated with "Remarks where SEQ_NOTE = 4"
March 16, 2009 at 9:14 pm
No cursor needed. I don't have your original data, so I'm using a table variable in my code below to mimic your output. The code below assumes that ONLY seq_Notes 1,2, and 4 will need to be concatenated. If this is an incorrect assumption, let me know.
To implement this solution, wrap your existing query in a CTE called yourOutput, just as I've done for the (select * from @sample) in the code below. Then cut and paste the summary query code at the very bottom.
This should run pretty quick, but I may have a faster running solution based on FOR XML. Without some source data, I didn't trust myself to rewrite all your code using it. Let me know if you would like to see an example, and please let me know if the solution below is satisfactory.
Good hunting.
Bob
declare @sample table (id_quote_odbc int, qty_break_es int, seq_note int, remarks varchar(max))
insert into @sample
select 1018,1,1,'this is a test how long is the line seq #1 12345678901234567' union all
select 1018,1,2,'this is seq #2 of oper 900 this should print' union all
select 1018,1,4,'seq 3 should not have printed but this seq 4 should (900)' union all
select 1018,250,1,'this is a test how long is the line seq #1 12345678901234567' union all
select 1018,250,2,'this is seq #2 of oper 900 this should print' union all
select 1018,250,4,'seq 3 should not have printed but this seq 4 should (900)'
;with yourOutput as
(select * from @sample) -- replace this with your existing query inside parentheses
-------------------------------------------------------------------------------------------------
-- this is the code to concatenate the results from your output
-- NOTE: it assumes that there will only seq_notes 1,2, and 4 will be presented by the CTE
-------------------------------------------------------------------------------------------------
select id_quote_odbc,qty_break_es,
max(case when seq_note = 1 then remarks+' ' else '' end)
+max(case when seq_note = 2 then remarks+' ' else '' end)
+max(case when seq_note = 4 then remarks else '' end)
as remarks
from yourOutput
group by id_quote_odbc,qty_break_es
order by id_quote_odbc,qty_break_es
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 17, 2009 at 8:15 am
Bob Hovious (3/16/2009)
No cursor needed. I don't have your original data, so I'm using a table variable in my code below to mimic your output. The code below assumes that ONLY seq_Notes 1,2, and 4 will need to be concatenated. If this is an incorrect assumption, let me know.To implement this solution, wrap your existing query in a CTE called yourOutput, just as I've done for the (select * from @sample) in the code below. Then cut and paste the summary query code at the very bottom.
This should run pretty quick, but I may have a faster running solution based on FOR XML. Without some source data, I didn't trust myself to rewrite all your code using it. Let me know if you would like to see an example, and please let me know if the solution below is satisfactory.
Good hunting.
Bob
declare @sample table (id_quote_odbc int, qty_break_es int, seq_note int, remarks varchar(max))
insert into @sample
select 1018,1,1,'this is a test how long is the line seq #1 12345678901234567' union all
select 1018,1,2,'this is seq #2 of oper 900 this should print' union all
select 1018,1,4,'seq 3 should not have printed but this seq 4 should (900)' union all
select 1018,250,1,'this is a test how long is the line seq #1 12345678901234567' union all
select 1018,250,2,'this is seq #2 of oper 900 this should print' union all
select 1018,250,4,'seq 3 should not have printed but this seq 4 should (900)'
;with yourOutput as
(select * from @sample) -- replace this with your existing query inside parentheses
-------------------------------------------------------------------------------------------------
-- this is the code to concatenate the results from your output
-- NOTE: it assumes that there will only seq_notes 1,2, and 4 will be presented by the CTE
-------------------------------------------------------------------------------------------------
select id_quote_odbc,qty_break_es,
max(case when seq_note = 1 then remarks+' ' else '' end)
+max(case when seq_note = 2 then remarks+' ' else '' end)
+max(case when seq_note = 4 then remarks else '' end)
as remarks
from yourOutput
group by id_quote_odbc,qty_break_es
order by id_quote_odbc,qty_break_es
Bob, I copied this into my query window and the output is what I need to happen. Most everything I have done is in SQL 2000 so CTE's are new to me. I did some reading and they seem like a pretty neat tool. I'm confused about part of this though in how to convert this to my stored procedure.
The "declare @sample table" is self-explanatory... I assume that I need to define all fields (21 at this point) that will be used in the stored procedure.
The part I bolded in your code (the "select ... union all" lines) is what confuses me. It looks like I would be hard coding values from my select statement into the selects (does my question make any sense?)
Thanks very much for your help with this!
Bob
March 17, 2009 at 8:46 am
Bob H.
I reread your comments and realized I was reading too much into it (my wife call me "Mr. Literal"). Using your code as a starting point and adding my other 20+ columns in, it now works as I need it to.
Thanks again very much!
Bob B.
March 17, 2009 at 8:58 am
Glad I could help. CTEs are a good thing. I prefer them to subqueries because they read from top to bottom, which is more intuitive to me. Get used to them and you will find lots of uses for them.
You were wise to ask first before using a CURSOR. There is almost always a solution to avoid cursors or while loops, and the alternative will usually run faster.
Have a good one.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply