techques
asked on
How to write this query in MSSQL?
select [id]=t.id, [nickname]=c.nickname, [currency]=t.currency, [ucurrency]=t.currency, [rate],
[amount]=CONVERT(VARCHAR,C
[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('
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.
[amount]=CONVERT(VARCHAR,C
[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('
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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('
or
input decimal / number , when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
but the user can input nothing, case when '%153750%'='XX' then 1
or
input string, when t.amount = case(isnumeric(Substring('
or
input decimal / number , when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
Can you explain this WHERE filter in normal english.
(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)
(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)
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
) = 1ASKER
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,C
[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,C
[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,C
[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.
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,C
[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,C
[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,C
[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.
where alltranid=0 and status = 0 and soldstockid = 0 and
(
(case when '%153750%'='XX' then 1
when t.amount = case(isnumeric(Substring('
when 1 then convert(decimal, Substring('%153750%', 2, LEN('%153750%')-2))
else 0 end THEN 1 else 0 END = 1)
)
???