0 works, col contains no 0

where filter col <> 0 returns error, col > 0 works, col contains no 0
Invalid column name?
Read a table within a table
Help needed in writing a stored procedure
Looking for a clever solution
Error inserting data with T-sql
Can I join on an alias?
How to Parse two string Columns using a Function

where filter col <> 0 returns error, col > 0 works, col contains no 0

Posted: 18 Jul 2013 03:13 AM PDT

Hi, I can't explain the following behaviour:I join 2 tables and depending on the where filter I get an error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."table A is a heap, table B has a clustered indexB.Date is CHAR(8)this does not work:SELECT CONVERT(DATETIME,B.Date)FROM AINNER JOIN B ON A.ID = B.ID WHERE A.ID <> 0this works:SELECT CONVERT(DATETIME,B.Date)FROM AINNER JOIN B ON A.ID = B.ID WHERE A.ID > 0I don't have a 0 in Table A.hmm...why do I get an error message with WHERE a.ID <> 0 ,altough I don't have 0 in a.IDthank you for your help!cheersralf

Invalid column name?

Posted: 18 Jul 2013 11:56 PM PDT

I have this query works great - no problem:[code="sql"]select * from (SELECT pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [month],rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[month] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' [/code]But if I add this to the end:[code="sql"]AND pehearncode = '0002'[/code]I get an Invalid column, how is it invalid? It's referenced and returns in the select??

Read a table within a table

Posted: 18 Jul 2013 06:13 PM PDT

I have a table with articles and a view with additional article information. For an article in the table I can found more records in the view. See example:Table:Article042001204200160420018View:String Name Expr1 Expr2 feature Unit vlg prefix suffix seperator10420012 Merk NULL Mega Mega 1 N N 0420012 Verbinding1 NULL hose tail hose tail 21 N N x 0420012 Nokafstand1MM 40 NULL 40 CD(mm) 28 Y N x 0420012 Materiaal brass NULL brass brass 3 N N 0420012 Maat1Inch 1/2 NULL 1/2 " 5 N Y x 0420012 Maat1MM 13 NULL 13 13 mm 7 N Y x So in my tabel I find article 0420012, in the view I see 6 lines. Now I have to make a SQL what reads the table and concat all values from the view (field feature and unit) in 1 result field, so in the example this must be something like:Mega hose tail 40 CD(mm) brass 1/2" 13mmCan someone advice me how to do this?Bert

Help needed in writing a stored procedure

Posted: 18 Jul 2013 05:35 PM PDT

I have a tablecreate table searchtbl(skills varchar(100),position varchar(40),location varchar(50))with records as skills position locationsqldeveloper contract hydjavadeveloper permanent hydsqldeveloper permanent Bang.netdeveloper contract Bangoracledeveloper contract chennaiphpdeveloper parttime hyd.netdeveloper permanent hydNow,i want to write a stored procedure when i pass a parameter from front end example if i have to seach for .net delvelopr i must only get the details of all .net developers,like that if i want to search hyd employes i must get all the details of hyd people and if i didn't pass any i must get all the records.Is it possible to write all those in on stored procedure.If so how?

Looking for a clever solution

Posted: 18 Jul 2013 11:13 AM PDT

Hi everyone,We have a SP that is quite long and it has a lot of such code:UPDATE table1SET col1 = CASE WHEN ( somecolumn = 'abc' AND somecolumn2 > 1 ) THEN 123 WHEN ( somecolumn = 'abc' AND somecolumn2 <= 1 ) THEN 234 ELSE 0 END, col2 = CASE WHEN ( somecolumn3 = 'abc' AND somecolumn4 > 1 ) THEN 123 WHEN ( somecolumn3 = 'abc' AND somecolumn5 > 1 ) THEN 234 ELSE 0 END As you can see there are a lot of constants here (abc, 1), so if the requirements change and instead of abc, they want to test against bcd, we have to go through the code and make those changes in the code, so that's a maintenance nightmare. How would you handle this kind of code and changing requirements? Thanks in advance.

Error inserting data with T-sql

Posted: 18 Jul 2013 01:53 AM PDT

HelloI am inserting data from one table to another using update,set command and getting the following error.[center]update XDDDepositorSET XDDdepositor.WBeneName = Vendor.RemitName,XDDDepositor.WBeneAddr = Vendor.RemitAddr1,XDDDepositor.WBeneAddr2 = Vendor.RemitAddr2,XDDDepositor.WBeneCity = Vendor.RemitCity,XDDDepositor.WBeneState = Vendor.RemitState,XDDDepositor.WBeneZipPostal = Vendor.RemitZipFrom XDDDepositor, VendorWhere XDDDepositor.vendid like '05%'and XDDDepositor.vendid = Vendor.Vendid and XDDDepositor.WBeneName =''[/center]ERROR: Msg 8152, Level 16, State 14, Line 2String or binary data would be truncated.The statement has been terminated.I know the problem is the character length of the address columns I am inserting data from but is there a way to correct it without altering the columns being updated?WBenBankAddr (char(35),not null) and RemitAddr1 (char(60), not null) WBeneAddr2 (char(35),not null) and RemitAddr2 (char(60), not null)

Can I join on an alias?

Posted: 18 Jul 2013 05:58 AM PDT

Is it possible to create a join on an alias?I have this query that I need to join to a table (month):[code="sql"]SELECT pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as month,rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code]FROM EmpPers JOIN pearhist ON pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] on [month] = v.month[/code]

How to Parse two string Columns using a Function

Posted: 18 Jul 2013 02:35 AM PDT

Sql samples, sql tips

Friday, July 19, 2013