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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 error in query

Author  Topic 

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 03:01:10
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')
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] CN

krmm

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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

Go to Top of Page

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_Amt
FROM dbo.vw_PDB_FLEX_TXN AS fx
INNER 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 Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 03:13:58



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 03:20:25
I NEED A dynamic query for this

krmm
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 03:23:47
BOTH SUGGESTION ARE DYNAMIC!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 03:25:55
Any why do you need to resort to Dynamic SQL ?

Seen this before ? http://www.sommarskog.se/dynamic_sql.html


KH

Go to Top of Page

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-31 : 03:35:07
this is a part of proc
this is the dynamic query which i use in my other proc

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'+''''+' 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_Status

FROM dbo.[TPA] T1 INNER JOIN


krmm
Go to Top of Page

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_Status
FROM dbo.[TPA] as T1
INNER JOIN

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 04:12:20
is the query here related to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78349


KH

Go to Top of Page

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 ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 10:24:23
Or too much ego?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -