Help doing procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help doing procedure

Author

Topic

nandoliveira
Starting Member

2 Posts

Posted - 2008-06-18 : 19:43:15

Hi i need to present in one gridview some information of one table but i need to filter it before and in ASP.NET/VB.NET i was trying but can't do it...

So i was thinking that with one procedure can be possible too... but my skills in SQL are not so good and i'm just now starting whit it...

So i need one procedure that:

makes one select: select id_word, txt_alt, n_errors from errorwords where n_errors=1 and retrive to out of the procedure all the results of this query but:

for each line returned in the select should analyse the field txt_alt [txt_alt is varchar] and:
if in the string the only upper letter is the first one, this line don't are returned out of the procedure and i won't see it in my gridview

else if there are upper letters, one or more, that are not the first one is returned out of the procedure for i can see it in my gridview

Example:
id_word txt_alt
109 "This line is with only one upper letter"
119 "This haVe two upper letters"
159 "This haVe SeVeral"

So in the procedure it only will be return out the 119 and 159... for i use in my gridview

Thanks for the attention :)

awheeler_01
Starting Member

4 Posts

Posted - 2008-06-19 : 12:43:37

quote:
Originally posted by nandoliveira

Hi i need to present in one gridview some information of one table but i need to filter it before and in ASP.NET/VB.NET i was trying but can't do it...

So i was thinking that with one procedure can be possible too... but my skills in SQL are not so good and i'm just now starting whit it...

So i need one procedure that:

makes one select: select id_word, txt_alt, n_errors from errorwords where n_errors=1 and retrive to out of the procedure all the results of this query but:

for each line returned in the select should analyse the field txt_alt [txt_alt is varchar] and:
if in the string the only upper letter is the first one, this line don't are returned out of the procedure and i won't see it in my gridview

else if there are upper letters, one or more, that are not the first one is returned out of the procedure for i can see it in my gridview

Example:
id_word txt_alt
109 "This line is with only one upper letter"
119 "This haVe two upper letters"
159 "This haVe SeVeral"

So in the procedure it only will be return out the 119 and 159... for i use in my gridview

Thanks for the attention :)

awheeler_01
Starting Member

4 Posts

Posted - 2008-06-19 : 12:47:33

quote:
Originally posted by awheeler_01

quote:
Originally posted by nandoliveira

Hi i need to present in one gridview some information of one table but i need to filter it before and in ASP.NET/VB.NET i was trying but can't do it...

So i was thinking that with one procedure can be possible too... but my skills in SQL are not so good and i'm just now starting whit it...

So i need one procedure that:

makes one select: select id_word, txt_alt, n_errors from errorwords where n_errors=1 and retrive to out of the procedure all the results of this query but:

for each line returned in the select should analyse the field txt_alt [txt_alt is varchar] and:
if in the string the only upper letter is the first one, this line don't are returned out of the procedure and i won't see it in my gridview

else if there are upper letters, one or more, that are not the first one is returned out of the procedure for i can see it in my gridview

Example:
id_word txt_alt
109 "This line is with only one upper letter"
119 "This haVe two upper letters"
159 "This haVe SeVeral"

So in the procedure it only will be return out the 119 and 159... for i use in my gridview

Thanks for the attention :)

I don't have any idea why the procedure doesn't return the line 109? However if you rewrite the procedure as:
select id_word, UPPER(txt_alt), n_errors

All the characters in txt_alt will be uppercase and if your supposition that rows are not returned when txt_alt has only one Uppercase letter is correct the procedure will return all rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-19 : 12:52:03

quote:
Originally posted by awheeler_01

quote:
Originally posted by awheeler_01

quote:
Originally posted by nandoliveira

Hi i need to present in one gridview some information of one table but i need to filter it before and in ASP.NET/VB.NET i was trying but can't do it...

So i was thinking that with one procedure can be possible too... but my skills in SQL are not so good and i'm just now starting whit it...

So i need one procedure that:

makes one select: select id_word, txt_alt, n_errors from errorwords where n_errors=1 and retrive to out of the procedure all the results of this query but:

for each line returned in the select should analyse the field txt_alt [txt_alt is varchar] and:
if in the string the only upper letter is the first one, this line don't are returned out of the procedure and i won't see it in my gridview

else if there are upper letters, one or more, that are not the first one is returned out of the procedure for i can see it in my gridview

Example:
id_word txt_alt
109 "This line is with only one upper letter"
119 "This haVe two upper letters"
159 "This haVe SeVeral"

So in the procedure it only will be return out the 119 and 159... for i use in my gridview

Thanks for the attention :)

I don't have any idea why the procedure doesn't return the line 109? However if you rewrite the procedure as:
select id_word, UPPER(txt_alt), n_errors

All the characters in txt_alt will be uppercase and if your supposition that rows are not returned when txt_alt has only one Uppercase letter is correct the procedure will return all rows.

probably you misunderstood the original post. I think OP was asking a way to filter out only those strings without any upper case letters except first character from the table and display it in grid

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-19 : 13:20:11

DECLARE @TABLE TABLE (COL varchar(10))

INSERT INTO @table
SELECT 'asd' union all
SELECT 'Asd' union all
SELECT 'asD' union all
SELECT 'aSd' union all
SELECT 'ASD'

SELECT col
,CASE WHEN CONVERT(varbinary,right(col,len(col)-1)) = CONVERT(varbinary,lower(right(col,len(col)-1)))
THEN 'same' ELSE 'mixed' END
FROM @table

Jim

bharambe9
Starting Member

1 Post

Posted - 2008-06-20 : 03:11:57

SELECT salyymm,sum(basicsalary) [basicsalary], sum(personalpay) [personalpay], sum(fda) [fda],sum(ida) [ida], sum(ca)[ca], sum(hra) [hra],sum(oall)[oall],(sum(basicsalary)+sum(personalpay)+sum(fda)+sum(ida)+sum(ca)+sum(hra)+sum(oall)+sum(contractpay)+ sum(X.amount)) [totpayable], sum(pf) [pf], sum(vpf) [vpf],sum(insurance) [insurance], sum(pension) [pension],sum(gslipemp) [gslipemp], sum(ptax) [ptax], sum(itax) [itax], sum(loanamt) [loanamt], sum(oded) [oded], (sum(pf)+sum(vpf)+sum(insurance)+sum(gslipemp)+sum(ptax)+sum(itax)+sum(oded)+sum(loanamt)) [totded], sum(contractpay) [contractpay], ((sum(basicsalary)+sum(personalpay)+sum(fda)+sum(ida)+sum(ca)+sum(hra)+sum(oall)+sum(contractpay)) - (sum(pf)+sum(vpf)+sum(insurance)+sum(gslipemp)+sum(ptax)+sum(itax)+sum(oded)+sum(loanamt))) [netpay], hstsalaryhdr.branchcd, branchmst.name [branchname], sum(hstsalaryhdr.employee)[totemployees]
FROM (SELECT hstsalaryhdr.employeecd,hstsalaryhdr.salyymm, isnull((basicsalary),0) basicsalary, isnull((personalpay),0) personalpay, isnull((fda),0) fda, isnull((ida),0) ida, isnull((ca),0) ca, isnull((hra),0) hra, isnull((oall),0) oall, isnull((pf),0) pf, isnull((vpf),0) vpf, isnull((insurance),0) insurance, isnull((pension),0) pension, isnull((gslipemp),0) gslipemp, isnull((ptax),0) ptax, isnull((itax),0) itax, isnull((loanamt),0) loanamt, isnull((oded),0) oded, isnull((contractpay),0) contractpay, hstsalaryhdr.branchcd, 1 [employee]
FROM hstsalaryhdr
LEFT OUTER JOIN employeemst
ON hstsalaryhdr.employeecd = employeemst.employeecd
WHERE hstsalaryhdr.salyymm = '200804'
UNION ALL SELECT salaryadjustment.employeecd, salaryadjustment.salyymm,
CASE WHEN allowancecd = salarydefaults.basiccd THEN amount else 0 END [basicsalary],
CASE WHEN allowancecd = salarydefaults.personalpaycd THEN amount ELSE 0 END [personalpay],
CASE WHEN allowancecd = salarydefaults.fdacd THEN amount ELSE 0 END [fda],
CASE WHEN allowancecd = salarydefaults.idacd THEN amount ELSE 0 END [ida],
CASE WHEN allowancecd = salarydefaults.cacd THEN amount ELSE 0 END [ca],
CASE WHEN allowancecd = salarydefaults.hracd THEN amount ELSE 0 END [hra],0 [oall],
CASE WHEN allowancecd = salarydefaults.pfcd THEN amount ELSE 0 END [pf],
CASE WHEN allowancecd = salarydefaults.vpfcd THEN amount ELSE 0 END [vpf],
CASE WHEN allowancecd = salarydefaults.insurancecd THEN amount ELSE 0 END [insurance],
CASE WHEN allowancecd = salarydefaults.pensioncd THEN amount ELSE 0 END [pension],
CASE WHEN allowancecd = salarydefaults.gslipcd THEN amount ELSE 0 END [gslipemp],
CASE WHEN allowancecd = salarydefaults.ptcd THEN amount ELSE 0 END [ptax],
CASE WHEN allowancecd = salarydefaults.itcd THEN amount ELSE 0 END [itax],
CASE WHEN allowancecd = salarydefaults.loancd THEN amount ELSE 0 END [loanamt],
CASE WHEN allowancecd = salarydefaults.houserentcd THEN amount ELSE 0 END [oded],
CASE WHEN allowancecd = salarydefaults.contractpaycd THEN amount ELSE 0 END [contractpay], hstsalaryhdr.branchcd, 0 [employee]

FROM salaryadjustment CROSS JOIN salarydefaults
INNER JOIN hstsalaryhdr
ON salaryadjustment.employeecd=hstsalaryhdr.employeecd AND salaryadjustment.salyymm = hstsalaryhdr.salyymm
WHERE hstsalaryhdr.salyymm = '200804')hstsalaryhdr
left outer join branchmst
on hstsalaryhdr.branchcd = branchmst.branchcd
left outer join (select salaryadjustment.employeecd, (sum(case when allid = 'E' then (salaryadjustment.amount) else -(salaryadjustment.amount) end)) amount from salaryadjustment inner join hstsalarydtl on salaryadjustment.salyymm = hstsalarydtl.salyymm and salaryadjustment.employeecd = hstsalarydtl.employeecd and salaryadjustment.allowancecd = hstsalarydtl.allowcd inner join earningndeduction on salaryadjustment.allowancecd = earningndeduction.sysallcd where salaryadjustment.salyymm='200804' group by salaryadjustment.employeecd )X
on hstsalaryhdr.employeecd = X.employeecd

WHERE hstsalaryhdr.salyymm = '200804'
GROUP BY hstsalaryhdr.salyymm, hstsalaryhdr.branchcd, branchmst.name
ORDER BY hstsalaryhdr.branchcd
how to hnadle this query

p v bahrambe

nandoliveira
Starting Member

2 Posts

Posted - 2008-06-25 : 03:29:19

hello, i think i found a fast solution... and is working:
i just make one procedure with this query:

SELECT * FROM mytable
WHERE
( SUBSTRING(my_field, 2, LEN(my_field)-1) LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZACEEIŠUUŽ]%' COLLATE SQL_Lithuanian_Cp1257_CS_AS )

Thanks for all the help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources