Posted: 06 Oct 2013 05:40 PM PDT

Hi there,I have a table code page in Chinese_PRC_Stroke_90_CI_AS and I am facing a 'select' problem now when data has special character in the field. Table data stores the following, RYP1836-K RYP1836-K1 RYP1836A-K RYP1836A-K1 RYP1836B-K RYP1836B-K1 RYP1836C-K RYP1836C-K1 RYP1836D-K RYP1836D-K1 Scenarios, 1) it returns 0 record when I issue statement like thisselect part_code from s_stkmst where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1' order by part_code2) it returns only 6 records when I applies collation order (miss out first 2 records) select REPLACE(part_code,'-','#'),part_code, source from s_stkmst where part_code>= REPLACE('RYP1836-K','-','#') AND part_code<=REPLACE('RYP1836D-K1','-','#')order by part_code collate SQL_Latin1_General_Cp437_BINRYP1836A#K RYP1836A-K RYP1836A#K1 RYP1836A-K1 RYP1836B#K RYP1836B-K RYP1836B#K1 RYP1836B-K1 RYP1836C#K RYP1836C-K RYP1836C#K1 RYP1836C-K1 Any idea to overcome this? Thanks in advanceWilson

Posted: 06 Oct 2013 06:44 PM PDT

I have a table like below[quote]MarkId ClassId ExamId SubjectCode RollNumber InternalMark ExternalMark AcadamicYear1 11 1 ITA01 3401101 12 85 2013-2014 2 11 1 ITA01 3401102 18 83 2013-20143 11 1 ITA01 3401103 21 89 2013-20144 11 1 ITA02 3401101 23 86 2013-20145 11 1 ITA02 3401102 23 94 2013-2014[/quote]My query for getting mark for classwise is below.[code="other"][quote]ALTER procedure [dbo].[sp_getmarkforclass](@classname varchar(30),@examname varchar(30),@medium varchar(30),@acyear varchar(30))asbegindeclare @paramlist varchar(max),@query nvarchar(max),@query1 nvarchar(max),@examid int,@classid int,@paramlist1 varchar(max)select @classid=ClassId from schoolcampus.dbo.Class_details where ClassName=@classname and Medium=@medium and Academicyear=@acyearselect @examid=ExamId from ExamNameSetting_details where ExamName=@examnameset @paramlist=STUFF((select distinct ',[' + SubjectCode + ']' from schoolcampus.dbo.Mark_details where classid=@classid and ExamId=@examid for xml path('')),1,1,'')set @paramlist1=STUFF((select distinct ',[' + Subjectname +']' from schoolcampus.dbo.SubjectRegistration_details where SubjectCode in (select SubjectCode from Mark_details where ClassId=@classid and ExamId=@examid)for xml path('')),1,1,'' )set @query=N'select * from(select sb.AdmissionNumber,sa.RollNumber,sc.Name,sr.SubjectName,sa.ExternalMark from schoolcampus.dbo.Mark_details sa inner join schoolcampus.dbo.StudentRollnumber_details sb on sa.RollNumber=sb.RollNumber inner join StudentAdmission_details sc on sc.AdmissionNumber=sb.AdmissionNumber inner join SubjectRegistration_details sr on sr.SubjectCode=sa.SubjectCode ) p PIVOT(Max(ExternalMark)for SubjectName IN ('+@paramlist1+')) AS pvt'set @query1=N'select * from(select sb.AdmissionNumber,sa.RollNumber,sc.Name,sr.SubjectName,sa.InternalMark from schoolcampus.dbo.Mark_details sa inner join schoolcampus.dbo.StudentRollnumber_details sb on sa.RollNumber=sb.RollNumber inner join StudentAdmission_details sc on sc.AdmissionNumber=sb.AdmissionNumber inner join SubjectRegistration_details sr on sr.SubjectCode=sa.SubjectCode ) p PIVOT(Max(InternalMark)for SubjectName IN ('+@paramlist1+')) AS pvt'execute(@query) execute(@query1) end[/quote][/code]This stored procedure return two table for mark as internal mark and external mark. but i want to get the internal and external mark in a single table like below method.AdmissionNumber Rollnumber Name Tamil|internalMark Tamil|externalMark English|internalmark like wise above. how can make a query like that.my output for External mark table is.[quote]AdmissionNumber RollNumber Name English Maths Science Social Science Tamil 100 401102 karthick 74 70 66 64 65 101 3401103 bharathi 70 65 64 60 60[/quote]

single quotes

Posted: 06 Oct 2013 05:06 AM PDT

i have a stored procedure where the customername parameter is a comma separatedi am calling that parameter in the dynamic where clause the problem is am getting the error unclosed single quotation,reason am getting that error becausevalues of customer names column are as followWill o'smithRoa'sso the customer name parameter is comma separated and used in the where clause

Grouping on hierarchy members when higher levels are hidden

Posted: 06 Oct 2013 07:52 AM PDT

I currently have the following hierarchy in place:Service Summary --> Service Definition --> Service DescriptionThere has been a recent change in the hierarchy where some of the service definitions changed their Service Summary roll up. Since our database is utilizing Type 2 history tracking, we can see the Service Definition assignment's change when the report is including the Summary\Service Definition.Many times, our users want to hide the Service Summary on their reports and just report on Service Definition. When they do this, the Service Definition that had a change in the Service Summary is now duplicated.For example:When Service Summary is used:ACT Service Summary Assertive Community Treatment Team Definition XXXXCommunity Assertive Community Treatment Team Definition XXXXWhen user hides Service Summary: Assertive Community Treatment Team Definition XXXX Assertive Community Treatment Team Definition XXXXHow can we force the query to group on the service definitions if the service summary is not displayed while maintaining the hierarchy? If I break apart the hierarchy and create separate dimensions, then the query aggregates as desired - but I loose the drilling capability of the hierarchy.Any thoughts?Thanks,Bob Lang

Sql samples, sql tips

Monday, October 7, 2013

[SQL Server 2008 issues] Help needed on select issue from Chinese_RPC_Stroke_90 table

[SQL Server 2008 issues] Help needed on select issue from Chinese_RPC_Stroke_90 table

No comments:

Post a Comment

Search This Blog