.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL query issue

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hi, I have a question that is pricking me as to how to solve it? Here is my requirement and table structures: create table #docs ( docid int, setupid int, docfname varchar(20), docmname varchar(20), doclname varchar(20), ) create table #setups ( docid int, setupid int ) -- A single doc can have multiple setups insert into #docs select 1, 100, 'Spock', 'Moreau', 'Livingston' union select 2, 101, 'Demento', 'Dolittle', 'Kevorkian' union select 3, 103, 'Faust', 'Zhivago', 'Jekyll' union select 4, 104, 'Freud', 'Laura', 'Phil' insert into #setups select 1, 200 union select 1, 300 union select 2, 200 union select 2, 400 union select 4, 200 union select 4, 500 /* If the doc has multiple setups I would like to copy over the data for the all the setupids as below */ select * from #docs select * from #setups drop table #docs drop table #setups I am trying to get this result using union and left out join etc., using a query and not using cursors/loops. Please help me and thanks for the responses. docid setupid docfname docmname doclname 1 100 Spock Moreau Livingston 1 200 Spock Moreau Livingston  --- data copied over 1 300 Spock Moreau Livingston  --- data copied over 2 101 Demento Dolittle Kevorkian 2 200 Demento Dolittle Kevorkian    --- data copied over 2 400 Demento Dolittle&n

View Complete Post

More Related Resource Links

Data query task issue

Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

Sql query inner join issue

so i have a datagridview in a vb from. my plan is to display a table named emplyees, having the following fields: [employees] ([lastname], [firstname], [cost_center], [division], [job_title], [hiring_date], [exit_date], [functional_manager], [member_of], [backup_by], [out_of_office], [desk_phone], [mobile_phone], [comments]. But I need to make an inner join with 3 other tables. job_titles, divisons and rss. How should i writethe select command for this, having the inner join forthe follwing fields: division- division.name,job_title-job_titles.name , and member_of- rss.group_name. I have no idea how to do it. pls help Thanks

Issue with CAML Query

I am having a RadGrid in Sharepoint Webpart with each row having a checkbox and multiple Columns where one of the Columns is ColumnID. When a multiple checkboxes are selected, their respective ColumnID Value is used to Query and generate report. I am using CAML Query to pass in the ColumnID of each checkbox selected and generate report based on the ColumnID Value. But I am having issue with the Query I have written. I have tried two ways: One of the ways was to Hardcode just two ColumnID Values as I was not sure how to read array of multiple columnID values. But I was wondering where I was going wrong since it was throwing following error: "Multiple with same name ColumnID were used" Telerik.Reporting.ReportParameter param1 = new Telerik.Reporting.ReportParameter(); param1.Name = "ColumnID"; param1.Type = Telerik.Reporting.ReportParameterType.Integer; param1.AllowBlank = false; param1.AllowNull = true; param1.Text = "ColumnID"; param1.Visible = true; param1.Value = "200"; this.ReportParameters.Add(param1); Telerik.Reporting.ReportParameter param2 = new Telerik.Reporting.ReportParameter(); param2.Name = "ColumnID"; param2.Type = Telerik.Reporting.ReportParameterType.Integer; param2.AllowBlank = false; param2.AllowNull = true; param2.Text = &qu

Issue with one of simple query

Hi team, I have a issue in the one of my query select   * from Prod3 where pnumber in (select distinct Policy from prod3_excel) order by pnumber This query gives me 8361 records and there are 8419 records in Prod3 how can i get the remaining records in Prod3 that is 58? select * from Prod3 where pnumber not in (select distinct Policy from prod3_excel) order by pnumber why this is not working? Thanks, Eshwar

Another Issue with CAML Query



I know this question has to be put in Telerik Forums but its something related to CAML and data being bound programmatically. So, please take a minute to share your thoughts on my issue. Thanks.

In my previous post here , I had issue with CAML Query, which works great now. So, here is one of my issue related to it: I am using a Report which has Subreport. So, I have set the datasource of Main Report to be a DataTable that holds the CAML Query (the result CAML Query from previous post here ). Now, for the same parameter values, for each Report, I want its subsequent subreport to be be populate programmatically. But, this CAML Query do

Issue with CAML Query regarding date comparision.

I have a below query in one the eventhandlers. All I do in the query is to get all records where Next_Date column is < CurrentDateTime ( I have shown current date below in full date format but it will be variable).

What I am observing is the comparision seems to be failing or not considering time format. For example the below query should not return any records when NEXT_DATE is 9/30/2010 23:59:59 and CurrentDateTime is 9/30/2010 06:22:22. However the query does return records. It looks like the comparision is just happening with Date and not time. What could be the problem? Please suggest.

         <FieldRef Name='Next_x0020_Date' />
         <Value Type='DateTime'>2010-09-30T13:13:13Z</Value>
      <FieldRef Name='Next_x0020_Date' Ascending='False' />

Query String Issue


Hello everyone,

I just started with learning MVC so please bear with me. I have a grid displayed on my Main page(Index) and when I click on the Details Link for a particular row(suppose productID=1) it should invoke the following action otherwise redirectToAction("Index"):


        // GET: /Home/Details
        public ActionResult Details(int? ProductID)
            if (!ProductID.HasValue)
                return RedirectToAction("Index");
                return View("Details");

The ProductID in my case is always Null and it actually ends up redirecting back to the main page as expected. Why is that happening. Somebody please help.

Thank You

REST Query Batch Issue


Hi All,

I've got an issue when querying the listdata.svc from a Silverlight client.   

I construct the query as follows:

var parametersQuery = context.Parameters.Expand("AssociatedTask") as DataServiceQuery<ParametersItem>;

If I execute the query individually and then process the results then the AssociatedTask collection gets populated, if I include it as a batch then it does not.

I've examined the query in Fiddler and as far as I can tell the request and response are identical.

Anyone shed any light on the issue?

A query plan issue


There are 3 paths in stored procedure p2:

create proc p2
 @n int
 declare @sql varchar(100)
 if @n=1
 set @sql='select *from ta where id='+cast(@n as varchar(10))
 else if @n=2
 set @sql='select *from tb where id='+cast(@n as varchar(10))
 set @sql='select *from tc where id='+cast(@n as 

Performance issue with select query



I have simple select query as below

SELECT DISTINCT BCEGPrsLinesCd, BCEGCmlLinesCd,CountyFedInfoPrcStdNbr

FROM TPSeleBCEGAddress WITH(nolock)




AND LTRIM(RTRIM(PrefixStreetDirCd))='E'






AND LTRIM(RTRIM(BCEGEffectiveYr))='1998'

Table TPSeleBCEGAddress has 64 Lakhs records in it and it takes around 22 Seconds to execute this query. Please let me know a way to improve performance of this select query.

Primary key defined on this table comprises of 16 columns.






Query issue - Pivot?


I a query that returns the following:

Day    TypeID     Product A     Product B
1        1            10               20 
1        2            30               40
2        1            50               60 
2        2            70               80

But, i need to return the following:
Day    Product A Type1     Product A Type2     Product B Type1
     Product B Type2
1       10                        30                        20                        40

LINQ to XML Query Issue



I wanted to retreive "Summary" attribute from following XML using LINQ to XML. Contract and Cancellation fee are working fine. Getting issue in discount. help will be  appreciated.

 var res = from feed in doc.Descendants("pp")
          where feed.Attribute("code").Value == "XYZ-ABC-00"
         select new


            Con_Summary = feed.Element("contract_term").Attribute("Summary").Value,

            Can_Summary = feed.Element("cancellation_fees").Element("cancellation").Attribute("Summary").Value,

            Dis_Summary = feed.Element("discounts")



<pp code="XYZ-ABC-00" >

    <fuel_types elec="true" gas="true" dualOnly="false" />

    <contract_term period="months" Summary="3 Year Contract">36</contract_term>

Issue in Query


Hi Experts,

Need help in below query

select TO_CHAR(joining_date,'DD-MON-YYYY') from project

above query Throwing invalid number error in oracle. Here joining_date is a varchar field. But if I change this to date datatype then it is working fine.

Need a solution in the query it self without changing the datatype of column. How to do this.

Issue with List template not restricting query


I am using DynamicData with DomainService over Entity framework. I have a model where patient has 1 or more claims. On the patient list, when I click on Claims property, the list shows all claims, not just ones of the clicked patient, even though the querystring has the patient id in it. The only thing I can think of is that I am using uniqueidentifiers as the ids. Have you guys got any ideas.

The url that gets passed to the claims list looks like this: http://localhost:6307/Dynamic/Claim/List.aspx?PatientId=8a784f6f-b0bc-4486-9baf-728d4f9f24b1

performance issue - slow query


Hi  - I have a query which is running very slow under a certain condition. I have 2 tables, Employees and EmployeeGroups. Employee has employees personal information and employeegroups has a mapping of each employee to the group it belongs to. One employee can be a part of multiple groups. The problem is with the below query.

SELECT emp_GUID, emp_firstname,emp_LastName
FROM Employee E join EmployeeGroups ON Emp_Guid=EMG_Emp_GUID
where EMG_GUID In ('multiple group GUIDs') and emp_Lastname like 'abc%'

EMG_Emp_GUID - Employee id of the employee

EMG_GUID - ID of the group employee belongs to.

The query runs well with less number of groups but it slows down as the number of groups i.e data increases. The tables is well indexed.

I am running out of ideas on this. Need help.

Cheers!!! SqlFrenzy

doubt about sql query : Issue with leading zeros


In my program I need to add leading zero to the column : " pagenumber" which is  having the values from 0 to 99.

I have used below query  : 

select '0' + cast(pagenumber as varchar) tablename

 output : 00 



It is working fine but when I tried to use condition in my  query as

(since i need to add leading zero only to the single digit numbers)

select pagenumber =

case when pagenumber < 10 then '0' + cast(pagenumber as varchar) 

else pagenumber


from tablename

Im getting output as : 0



The output generated is not having leading zeros.

I tried using right('0' + convert(varchar,pagenumber) , 2) as pagenumber  but I have the same issue as described above.

I have spent more than a day but couldn't figure out where the issue is. Can someone plz help me with it.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend