techques
asked on
How to write this SQL?
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
from transactions 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 = convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) 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.remarks6 like N'%kat%') or ('%kat%'='XX' or t.remarks7 like N'%kat%')) order by [id] asc
--------------------------
(case when '%kat%'='XX' then 1 when t.amount = convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) THEN 1 else 0 END = 1) or
has error:
Error converting data type varchar to numeric.
It is a query to search decimal and string of those fields in table.
It is ok if I search decimal, number, but it has error when search string
How should I modify the above query?
[currency]=t.currency, [ucurrency]=t.currency, [rate], [amount]=CONVERT(VARCHAR,C
from transactions 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 = convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) 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.remarks6 like N'%kat%') or ('%kat%'='XX' or t.remarks7 like N'%kat%')) order by [id] asc
--------------------------
(case when '%kat%'='XX' then 1 when t.amount = convert(decimal, Substring('%kat%', 2, LEN('%kat%')-2)) THEN 1 else 0 END = 1) or
has error:
Error converting data type varchar to numeric.
It is a query to search decimal and string of those fields in table.
It is ok if I search decimal, number, but it has error when search string
How should I modify the above query?
sorry .. some syntax errors:
(case when '%kat%'='XX' then '1' when cast(t.amount as nvarchar(50)) = Substring('%kat%', 2, LEN('%kat%')-2) THEN '1' else '0' END = 1) or
ASKER
t.amount cannot be treated as nvarchar as its datatype is decimal.
If I use your query, it is ok to search string. However, it did not return result when i search decimal or number.
If I use your query, it is ok to search string. However, it did not return result when i search decimal or number.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It does work on 2005, thanks for help
but you could try
Open in new window