Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
oracle_corrgi
Yak Posting Veteran
98 Posts |
Posted - 2007-01-31 : 03:01:10
|
declare@rpt varchar(8000)select @rpt =' SELECTCN.[step_id],'+ 'CASE CN.[msg_delivery_options]WHEN 3 THEN '+ ''''+'ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed') '+ ''''+' WHEN 2 THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.mail_dte,101),'Not Mailed') '+ ''''+' WHEN 1 THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed') '+ ''''+' WHEN 0 THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'None') END as notification_dte,' 'CASE CN.[msg_delivery_options] WHEN 3 THEN '+ ''''+''Letter & Email' WHEN 2 THEN '+ ''''+''Letter' WHEN 1 THEN '+ ''''+''E-Mail' WHEN 0 THEN '+ ''''+''None' END ' ss notification_method,FX.[txn_amt]FROM dbo.[vw_PDB_FLEX_TXN] FX ,dbo.[CardHolder_Notification] CNkrmm |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:04:47
|
The closing ' after END should be after dbo.[CardHolder_Notification] CN.But why are you CROSS JOIN the two tables?Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-01-31 : 03:13:10
|
[code]declare @rpt varchar(8000)select @rpt = ''select @rpt = @rpt + 'SELECT CN.[step_id],' + char(13)select @rpt = @rpt + ' CASE CN.[msg_delivery_options]' + char(13)select @rpt = @rpt + ' WHEN 3 THEN ISNULL(CONVERT(VARCHAR,CN.email_dte,101), ''Not Mailed'')' + char(13)select @rpt = @rpt + ' WHEN 2 THEN ISNULL(CONVERT(VARCHAR,CN.mail_dte,101), ''Not Mailed'')' + char(13)select @rpt = @rpt + ' WHEN 1 THEN ISNULL(CONVERT(VARCHAR,CN.email_dte,101), ''Not Mailed'')' + char(13)select @rpt = @rpt + ' WHEN 0 THEN ISNULL(CONVERT(VARCHAR,CN.email_dte,101), ''None'')' + char(13)select @rpt = @rpt + ' END as notification_dte,' + char(13)select @rpt = @rpt + ' CASE CN.[msg_delivery_options]' + char(13)select @rpt = @rpt + ' WHEN 3 THEN ''Letter & Email''' + char(13)select @rpt = @rpt + ' WHEN 2 THEN ''Letter''' + char(13)select @rpt = @rpt + ' WHEN 1 THEN ''E-Mail''' + char(13)select @rpt = @rpt + ' WHEN 0 THEN ''None''' + char(13)select @rpt = @rpt + ' END as notification_method,' + char(13)select @rpt = @rpt + ' FX.[txn_amt]' + char(13)select @rpt = @rpt + 'FROM dbo.[vw_PDB_FLEX_TXN] FX INNER JOIN dbo.[CardHolder_Notification] CN' + char(13)select @rpt = @rpt + ' ON <your join condition here>'print @rpt[/code] KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:13:24
|
[code]declare @rpt varchar(8000)select @rpt = 'SELECT cn.Step_ID, CASE cn.Msg_Delivery_Options WHEN 3 THEN ISNULL(CONVERT(VARCHAR, cn.Email_Dte, 101), ''Not Mailed'') WHEN 2 THEN ISNULL(CONVERT(VARCHAR, cn.Mail_Dte, 101), ''Not Mailed'') WHEN 1 THEN ISNULL(CONVERT(VARCHAR, cn.Email_Dte, 101), ''Not Mailed'') WHEN 0 THEN ISNULL(CONVERT(VARCHAR, cn.Email_Dte, 101), ''None'') ELSE ''I have forgotten to handle this case...'' END AS Notification_Dte, CASE cn.Msg_Delivery_Options WHEN 3 THEN ''Letter + Email'' WHEN 2 THEN ''Letter'' WHEN 1 THEN ''E-Mail'' WHEN 0 THEN ''None'' ELSE ''I have forgotten to handle this case...'' END AS Notification_Method, fx.Txn_AmtFROM dbo.vw_PDB_FLEX_TXN AS fxINNER JOIN dbo.CardHolder_Notification AS cn ON cn.<somecolnamehere> = fx.<somecolnamehere>'print @rpt-- exec(@rpt)[/code]DON'T FORGET TO CHANGE THE <somecolnamehere> TO BIND TOGETHER THE VIEW WITH THE TABLE!!!Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:13:58
|
Peter LarssonHelsingborg, Sweden |
|
|
oracle_corrgi
Yak Posting Veteran
98 Posts |
Posted - 2007-01-31 : 03:20:25
|
I NEED A dynamic query for thiskrmm |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:23:47
|
BOTH SUGGESTION ARE DYNAMIC!Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:24:38
|
It would be a GREAT HELP if you could at least explain what YOU mean with DYNAMIC...Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
oracle_corrgi
Yak Posting Veteran
98 Posts |
Posted - 2007-01-31 : 03:35:07
|
this is a part of procthis is the dynamic query which i use in my other procT4.[last_name], '+ ' CASE WHEN T2.[card_status_cde] = 1 THEN '+ ''''+'New'+''''+' WHEN T2.[card_status_cde] = 2 THEN '+''''+'Active'+''''+' WHEN T2.[card_status_cde] = 3 THEN '+''''+'TempInactive'+''''+' WHEN T2.[card_status_cde] = 4 THEN '+''''+'PermInactive'+''''+' WHEN T2.[card_status_cde] = 5 THEN '+''''+'Lost/Stolen'+''''+' END as Status_Code,'+ 'NULL as empe_status_code, '+ 'ISNULL(T2.[status_change_dte],T2.[insert_dte]) AS Status_Date, '+ ' CASE WHEN (T5.[tpa_id] is null AND T5.[reactivate_dte] is null OR T5.[tpa_id] is not null AND T5.[reactivate_dte] is not null) THEN '+''''+'0'+''''+ ' WHEN (T5.[tpa_id] is not null AND T5.[reactivate_dte] is null) THEN '+''''+'1'+''''+' END AS Card_StatusFROM dbo.[TPA] T1 INNER JOINkrmm |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 03:41:52
|
WHAT!?!?Are you aware you are a twit? How can you expect us to help you if you only post/send us half the code used? T4.[last_name], CASE WHEN T2.[card_status_cde] = 1 THEN ''New'' WHEN T2.[card_status_cde] = 2 THEN ''Active'' WHEN T2.[card_status_cde] = 3 THEN ''TempInactive'' WHEN T2.[card_status_cde] = 4 THEN ''PermInactive'' WHEN T2.[card_status_cde] = 5 THEN ''Lost/Stolen'' ELSE ''Yet again I have forgotten to handle this case...'' END as Status_Code, NULL as empe_status_code, ISNULL(T2.[status_change_dte], T2.[insert_dte]) AS Status_Date, CASE WHEN T5.[tpa_id] is null AND T5.[reactivate_dte] is null OR T5.[tpa_id] is not null AND T5.[reactivate_dte] is not null THEN ''0'' WHEN T5.[tpa_id] is not null AND T5.[reactivate_dte] is null THEN ''1'' END AS Card_StatusFROM dbo.[TPA] as T1INNER JOIN Peter LarssonHelsingborg, Sweden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-31 : 09:21:26
|
These different text descriptions for the different values should all be stored in tables, and then you should be doing a simple join to get those descriptions. You should never, ever use a CASE like this since you are forcing data into your code, making your code longer, harder to read and write, less efficient, and less flexible. You are using a database -- store data in it! see: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx for more on this.In addition, as we have asked over and over, why the heck are you using dynamic SQL and building a long string with escaping and all that??? all you need to do is write a simple SQL statement normally and be done with it. You are making things so much harder on yourself.Once again, we have the classic situation: a person is struggling with the basic concepts, working much, much harder than they need to and overcomplicating very simple things, and then they come to the experts for help. The experts explain how there are very easy, simple ways to do things, and the person says "no thanks! i will ignore your advice and keep struggling and doing things the hard way!!"Amazing ... if I spend a week on a project that I just can't get, and someone else explains to me "hey, you are going about it wrong. . all you need to do is xxxx and it makes things simpler, easier, shorter, quicker and so on" I would be excited to hear that and immediately take their advice. Why doesn't everyone think this way? because it makes too much sense?Either ask for help and accept it and learn from it, or don't bother asking at all ...- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-31 : 10:24:23
|
Or too much ego?Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|