SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 error in query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 01/31/2007 :  03:01:10  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/31/2007 :  03:04:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17642 Posts

Posted - 01/31/2007 :  03:13:10  Show Profile  Reply with Quote

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



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/31/2007 :  03:13:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)
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

Sweden
30265 Posts

Posted - 01/31/2007 :  03:13:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

oracle_corrgi
Yak Posting Veteran

India
98 Posts

Posted - 01/31/2007 :  03:20:25  Show Profile  Reply with Quote
I NEED A dynamic query for this

krmm
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/31/2007 :  03:23:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
BOTH SUGGESTION ARE DYNAMIC!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 01/31/2007 :  03:24:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

Singapore
17642 Posts

Posted - 01/31/2007 :  03:25:55  Show Profile  Reply with Quote
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

India
98 Posts

Posted - 01/31/2007 :  03:35:07  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/31/2007 :  03:41:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 01/31/2007 03:44:31
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 01/31/2007 :  04:12:20  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 01/31/2007 :  09:21:26  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Sweden
30265 Posts

Posted - 01/31/2007 :  10:24:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or too much ego?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000