[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to write this query in MSSQL?

Posted on 2010-01-06
6
Medium Priority
?
222 Views
Last Modified: 2012-05-08
select [id]=t.id, [nickname]=c.nickname, [currency]=t.currency, [ucurrency]=t.currency, [rate],
[amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t
inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency
inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id
inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and
(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)

It does not return any result but I can see 153750 in amount field.
Since it is a search textbox let user input either string or decimal, it can return query result if I input string. If I input number, it does not return.

 
0
Comment
Question by:techques
6 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26196998
what are you  trying to do here, the case statement

where alltranid=0 and status = 0 and soldstockid = 0 and
(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)

???
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 200 total points
ID: 26197018
maybe this

where alltranid=0 and status = 0 and soldstockid = 0 and
(
'%153750%'='XX'
or
(t.amount = (case when (isnumeric(Substring('%153750%', 2, LEN('%153750%')-2))) then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0))
)

or maybe this

where alltranid=0 and status = 0 and soldstockid = 0 and
(
'%153750%'='XX'
or
(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2) and t.amount = convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
)
0
 

Author Comment

by:techques
ID: 26197085
where condition is to sort out amount

but the user can input nothing, case when '%153750%'='XX' then 1
or
input string, when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
or
input decimal / number , when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))



0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 41

Expert Comment

by:Sharath
ID: 26197378
Can you explain this WHERE filter in normal english.

(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26197747
try
select t.id as [id], c.nickname as [nickname], t.currency as [currency], t.currency as [ucurrency], [rate], 
	cast(t.amount as money) as [amount], [countervalue], [clientreference], [handlingincome], [handlingexpense], 
	[trandatetime], [status]
from alltransaction t 
inner join client c on t.clientid = c.id 
inner join currency u on t.currency = u.currency 
inner join transactiontype r on t.transactiontypeid = r.id 
inner join account a on t.accountid = a.id and t.lastaccountid = n.id 
where 
	t.[alltranid] = 0 
	and t.[status] = 0 
	and t.[soldstockid] = 0 
	and (case 
			  when '%153750%'='XX' then 1 
			  when t.amount = case(isnumeric(Substring('%153750%', 2, LEN('%153750%')-2)))
									when 1 then cast(Substring('%153750%', 2, LEN('%153750%')-2) as decimal(18,2))
									else 0 end THEN 1 else 0 END
		  ) = 1

Open in new window

0
 

Author Comment

by:techques
ID: 26199745
I think my question was not clear enough. There is a aspx page which has a textbox let user input a string, e.g. kat, 153750, 8823.67, then it will search the alltransaction table. For default, textbox is empty, it will list out all records.

Case 1: Empty textbox

select [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%%'='XX' or t.clientreference like N'%%') or ('%%'='XX' or r.type like N'%%') or
('%%'='XX' or c.nickname like N'%%') or ('%%'='XX' or t.currency like N'%%') or
(case when '%%'='XX' then 1 when t.amount = case(isnumeric (Substring('%%', 2, LEN('%%')-2))) when 1 then convert(decimal, Substring('%%', 2, LEN('%%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%%'='XX' or t.remarks like N'%%') or ('%%'='XX' or t.remarks1 like N'%%') or ('%%'='XX' or t.remarks2 like N'%%') or
('%%'='XX' or t.remarks3 like N'%%') or ('%%'='XX' or t.remarks4 like N'%%') or ('%%'='XX' or t.remarks5 like N'%%') or
('%%'='XX' or t.remarks6 like N'%%') or ('%%'='XX' or t.remarks7 like N'%%')
) order by [id] asc

Case 2: kat in textbox

select [rowselector]=0, [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%kat%'='XX' or t.clientreference like N'%kat%') or ('%kat%'='XX' or r.type like N'%kat%') or
('%kat%'='XX' or c.nickname like N'%kat%') or ('%kat%'='XX' or t.currency like N'%kat%') or
(case when '%kat%'='XX' then 1 when t.amount = case(isnumeric (Substring('%kat%', 2, LEN('%kat%')-2))) when 1 then convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%kat%'='XX' or t.remarks like N'%kat%') or ('%kat%'='XX' or t.remarks1 like N'%kat%') or
('%kat%'='XX' or t.remarks2 like N'%kat%') or ('%kat%'='XX' or t.remarks3 like N'%kat%') or
('%kat%'='XX' or t.remarks4 like N'%kat%') or ('%kat%'='XX' or t.remarks5 like N'%kat%') or
('%kat%'='XX' or t.remarks6 like N'%kat%') or ('%kat%'='XX' or t.remarks7 like N'%kat%')
) order by [id] asc

Case 3: 153750

select [id]=t.id, [alltranid], [transactiontypeid], [type]=r.type, [soldstockid], [clientid]=t.clientid, [nickname]=c.nickname,
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,CONVERT(MONEY,t.amount),1), [countervalue], [clientreference],
[handlingincome], [handlingexpense], [trandatetime], [status]
from alltransaction t inner join client c on t.clientid = c.id inner join currency u on t.currency = u.currency inner join transactiontype r on t.transactiontypeid = r.id inner join account a on t.accountid = a.id inner join account n on t.lastaccountid = n.id
where alltranid=0 and status = 0 and soldstockid = 0 and (
('%153750%'='XX' or t.clientreference like N'%153750%') or ('%153750%'='XX' or r.type like N'%153750%') or
('%153750%'='XX' or c.nickname like N'%153750%') or ('%153750%'='XX' or t.currency like N'%153750%') or
(case when '%153750%'='XX' then 1 when t.amount = case(isnumeric (Substring('%153750%', 2, LEN('%153750%')-2))) when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2)) else 0 end THEN 1 else 0 END = 1) or
('%153750%'='XX' or t.remarks like N'%153750%') or ('%153750%'='XX' or t.remarks1 like N'%153750%') or
('%153750%'='XX' or t.remarks2 like N'%153750%') or ('%153750%'='XX' or t.remarks3 like N'%153750%') or
('%153750%'='XX' or t.remarks4 like N'%153750%') or ('%153750%'='XX' or t.remarks5 like N'%153750%') or
('%153750%'='XX' or t.remarks6 like N'%153750%') or ('%153750%'='XX' or t.remarks7 like N'%153750%')
) order by [id] asc

Case 1 and 2 are ok, but Case 3 cannot list the record.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question