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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

I have the following 4 select statements.. but the 4th one doesnt return what i think i should be ge

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :ASP.Net
 

I have a report that is presented to the client. It consists of "Scheduled", "Uncompleted", "Completed" and "All". Now what I PERSONALLY think these reports should return for the first 3 should total what is presented in the "All" report.

if i run the report "Scheduled" it returns 0
if i run the report "Uncompleted" it returns 157
if i run the report "Completed" it returns 59
But when i run the "All" report it returns 210

Which leaves a difference of 6. Can anyone tell me what im doing wrong with the following 4 select statements?

--------------------------------
Here is the scheduled report:
--------------------------------
select p.permit_num,K.task_id,K.task_due_date,K.date_completed,K.complete_ind,K.inspRpt_ind,C.*, R.service_rep_name,L.*,S.sys_name,M.manu_name, SI.sentAgc_date 
from tasks K, service_contracts C,service_reps R,locations L, manufacturers M, sys_inspection SI, systems S left outer join sys_permits sp on sp.sys_id = s.sys_id left outer join permits p on p.permit_id = sp.permit_id and p.active = 1 
where K.service_contract_id=C.service_contract_id and C.sys_id=S.sys_id and C.service_rep_id=R.service_rep_id and M.manu_id=S.manu_id and S.location_id=L.location_id and


View Complete Post


More Related Resource Links

Error: "Select statements included within a function cannot return data to a client"

  
hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

select query doesnt return values

  

hi all, i need your assistance please.

 

select Comments.Id,SuroundId,ArticleId,PosterId,PostRime,Subject,Body,Visible,Users.Username
from dbo.Comments
inner join Users on Comments.PosterId=Users.Id
where SuroundId=@suround and ArticleId=@articleId


for some reason it doesnt return any values.

before i've added the inner join it worked perfectly.

i couldn't find my mistake, though i passed over it several times.

 

thanks. 


ListView DataPager with SQL-Statements that already return only paged data.

  

Hi,

As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.


SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

configure both select and Update statements in ADO.NET

  
 i would like to retrieve a value with select and perform a calculation and then save using update in the same batch code?  

Having subqueries within select statements

  
I want to know whether there are any consequences of having sub queries within a select statement so for example writing queries using this style.     SELECT NAME ,PROFILE CONVERT(VARCHAR(10), (SELECT PROFILEDATE FROM tblTable WHERE ProfileID= tst.[ID] AND [ProfileDate]=(SELECT MAX([ProfileDate]) FROM [EtpForecastDividendsStaging] WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate])), 120) , CONVERT(VARCHAR(10), (SELECT MAX([ProfileDate]) FROM tbltable WHERE [ProfileID]=qry.[ProfileID] AND [ProfileDate]<qry.[ProfileDate]), 120) FROM tbltable join tblothertable The above example is just a sample and not a query i had expect to compile, i am just using it as an illustration. I believe its bad programming, but i may be wrong. I tend to like normal traditional joins where you select the column and join based on the corresponding keys, but I am trying to grasp some justification for my approach.   Any ideas    

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

  
This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

How to write a select to return all metadata

  

Hi;

We need to pull a lot of metadata from the database. Specifically we need to get (on Sql Server 2000, 2005, & 2008):

  1. All tables & views - their schema, name, description, and if they are a system or user object.
  2. All table/view columns - their name, description, data type, and, if it is a foreign key, the table.column of the primary key it links to.
  3. All stored procedures - their name, description, and return type, and if they are a system or user object.
  4. All procedure parameters - their name, description, data type, data length, default (if any), and direction.

We have selects to do this but they are very complex and slow. We recently had an Oracle systems engineer help us on this same topic on Oracle and he had us create a temporary table that had all this info, we then read the rows in the table to get all this info and then dropped the table. It was a gigantic performance improvement.

So... Any suggestions on the best way to pull out this info? We grab it all at once so a temporary table is fine. Individual selects for each step is fine too. Suggestions on how best to do this are very appreciated.

thanks - dave


Very funny video - Reporting as a Metaphor

How to write a select to return all metadata

  

Hi;

We need to pull a lot of metadata from the database. Specifically we need to get (on Sql Server 2000, 2005, & 2008):

  1. All tables & views - their schema, name, description, and if they are a system or user object.
  2. All table/view columns - their name, description, data type, and, if it is a foreign key, the table.column of the primary key it links to.
  3. All stored procedures - their name, description, and return type, and if they are a system or user object.
  4. All procedure parameters - their name, description, data type, data length, default (if any), and direction.

We have selects to do this but they are very complex and slow. We recently had an Oracle systems engineer help us on this same topic on Oracle and he had us create a temporary table that had all this info, we then read the rows in the table to get all this info and then dropped the table. It was a gigantic performance improvement.

So... Any suggestions on the best way to pull out this info? We grab it all at once so a temporary table is fine. Individual selects for each step is fine too. Suggestions on how best to do this are very appreciated.

thanks - dave


Very funny video - Reporting as a Metaphor

Select Sql does not return the data eventhough the data does in particular table.

  

Hi Dude,

I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column .  So i have wrote the sql statment like in below.

SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718'

But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718').

When i execute the above select statement, there is no result for it. Please let me know anyone face the same problem? Please give a solution for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.

Thanks in Advance

 


INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

  
I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.
Stephen

MSSQL 2005 JDBC driver multiple select statements in stored procedure

  

Hello,

we have a stored procedure with multiple select in it.

 

Example

Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3

 

Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.

 

 

 


instead of a buncha sql select statements

  
I have a query that i need to enter facility code and location code from an excel spreadsheet.... The only problem is that sometimes there is only facility code and other times there is facility code and location code.....  
Select e.FirstName, e.LastName, e.Address1, e.Address2, e.City, e.State, e.ZipCode, f.Code as FacilityCode, f.Name from Employees e 
join Locations l on l.Id= e.LocationId 
join Facilities f on f.Id = l.FacilityId 

where f.Code=
and l.Code=

and e.HewittEmploymentStatus='active' 
  • and e.FullTimeStatus='f' order by f.Code
  • FAC=
  • LOC=
  • 800
  • 544
  • 802
  • 345
  • 0001
  • 334

Two select statements Combined?

  

OK here is what I am rocking with.....

 

--select distinct l.Name as LocationName,l.Code as LocationCode, f.Name as FacilityName, f.Code as FacilityCode
--from Locations l 
--inner join Facilities f on f.Id=l.FacilityId

 

This t-sql statement above selects every Facility/Location combination  695 combos

 

 

Select e.FirstName,e.LastName, am.Password, au.UserName,
 l.Code as locationCode, l.Name, f.Code As FacilityCode,f.Name as facilityName

from Employees e
inner join aspnet_Membership am on am.UserId=e.UserId
inner join aspnet_Users au on au.UserId=e.UserId
inner join Locations l on l.Id=e.LocationId
inner 

Why SELECT statements obtains Exclusive Locks?

  
Hello,

My understanding below report indicates that process52bf948 obtained exclusive lock on PK_tblTrips, while statement which is being executed is SELECT. Why would that happen?


<process id="process52bf948" taskpriority="0" logused="94912" waitresource="KEY: 5:72057594122600448 (bd0015f6d794)" waittime="4496" ownerId="362013974" transactionname="user_transaction" lasttranstarted="2010-10-27T11:06:43.880" XDES="0x13cff2f970" lockMode="S" schedulerid="21" kpid="3260" status="suspended" spid="208" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-10-27T11:10:53.367" lastbatchcompleted="2010-10-27T11:10:53.367" lastattention="2010-10-27T11:10:03.003" clientapp="Internet Information Services" hostname="WEBAPP6" hostpid="3036" loginname="masteruser" isolationlevel="read committed (2)" xactid="362013974" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
    

Javascript Confirm , return statements on window.beforeUnload

  

Hi all,
I'm using javascript function window.onbeforeunload to display a message to the user before he exits the page.
This javascript is calling a server method( ASP.NET and Vb.net) , if the user decides to exit the page. Else do nothing.
I'm having trouble with javascript(newbie here).The trouble is:
Originally while exiting the page, i used to do anything.This is how i used to alert the user
  if (showUnloadMessage)
{
return('You have attempted to leave this page.If you have made any changes to the fields without clicking Submit button,This Data will be saved as This Month Report.Are you sure you want to exit the page?');
}

But now i have to call a server side script based on user answer.If he decides to Quit, i have call server side function.I have written the code as below, but It gives the the message" you have attempted", and then also gives confrim message.I dont want that confirm message. I want only The "you have attempted to leave" message.If i remove the confirm () , then how do i alert the user. Where do i put these retun statemets.
Please help me correct the following javascript. The rerun statements are soo confusing to me..!!!
Thanks again

 

Code snippet:

select '?' excecuted against a server usingn management studio return a "?"

  

The server is MS SQL2008 R2 with collation Latin1_General_BIN2

I get the same problem when I put the statement in a unicode text file and execute using sqlcmd

Any pointers appreciated

Thanks

 

 

 


Categories: 
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