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


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

Select from multi-statement UDF with where 1=0

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

Hi everybody,

I'm running this query and I believe it first tries to calculate the function and then applies WHERE 1=0 condition. In my case I prefer to not run function at all, of course.

I'll try to figure out later if I can get rid of this query, but is my understanding correct that this function will always execute regardless of the WHERE condition? Is there a way to trick SQL Server to not do unnecessary job?

SELECT SUM(i.QuantityOnHand) AS Quantity,
      SUM(i.CostOfGoodsOnHand) AS Ext_Cost,
      AVG(i.AverageCost) as Unit_Cost,
      i.locatn_id
   FROM dbo.fn_CostOfGoodsOnHand
     ('FIFO', '20110520 23:59:59',
      NULL, 1,
      'RETAIL  ', 'MENSCLOTH ', 'QKSLVSHIRT',
      'RETAIL  ', 'ME


View Complete Post


More Related Resource Links

Use BDC data in list as multi-select column

  
I have a document library that uses values from another system for its metadata.  Right now, we copy the values from the primary system into SharePoint so users can choose them.  I want to find a way to use the BDC connection that I have set up to automatically pull those values.  The problem is that the current metadata is set to multi-select.  So, I have a file that is in the library and it can be used for multiple clients - clients is a column in my library and users can select many entries.  When I set up a column to pull BDC data, it only allows single entry.  Does anyone have any ideas?

web services transactions (select statement)

  
 Hi everybody, i'm just getting started in web services and i have a problem connecting to a database. All i want is that my web service recieves a string parameter and return a dataset so that in the client aplication i just show the result of the web service in a datagrid. Here's the code of the web service:    <%@ WebService Language="C#" CodeBehind="Service1.asmx.cs" %> using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Linq;using System.Web;using System.Web.Services;using System.Web.Services.Protocols;using System.Xml.Linq;using System.EnterpriseServices;using System.Data.SqlClient;namespace Farmacia{    [WebService(Namespace = "http://localhost/MyWebServices")]    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]    [ToolboxItem(false)]    public class Farmacia : System.Web.Services.WebService    {        private SqlDataAdapter da;        [WebMethod(TransactionOption=TransactionOption.RequiresNew)]        public DataSet Consultas (string sel)        {            if (sel == "")&

SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

  
Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

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

DVWP connections with multi-select lookup columns

  
I have a question concerning the Data View Web Part using SharePoint Designer 2007.   I have two list (A and B). List A has a lookup column (called B-items) with multiple selections to items in List B(using the Title column from B).   I create a web part page and insert a data view of using List A. I create another data view with data from List B. Next, I make a Web Part Connection between the two data views with A passing B-items column as parameter to List B data view. I then create a filter on the List B data view with a comparison of List B’s Title column equal to the parameter of List A’s B-items value when a user selects an item from List A data view. The problem is nothing appears in List B’s data view.   When I try the reverse of the above scenario it works fine.     I understand that this is properly functionality, but is there a way to achieve my first scenario? If so, how can it be accomplished?   I have looked through the web for help and found the answer here http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/749b7477-f37f-4724-94b3-b6ace770e73a seems to be what I’m looking for. However, I am unsure how to implement his code into the web part page.   I am a worse than a novice with xsl so if anyone gives an example I would greatly appreciate step by step on what code is needed a

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

How do i use the select statement in functions?

  
hi friends, i have table-value function, but the has the bug in the body and i cannot debug, becuase it does not allow the select statement in it. is there away to print values to the screen, when debugging? PS: in not using a SP to debug it thanks

Assistance with SELECT statement requested

  
Hi All, I am having trouble getting started on writing a select statement to meet the following requirements.  I have a table with Employee Ids, address types, and address information.  I would like to obtain one record for each EmplID based upon the Type column; the PRF type record if it exists, otherwise then the PRI record.  (See Table A) TableA Rcd #, EmplID, Type, Address Data 1, 000001, PRI, etc 2, 000001, PRF, etc 3, 000002, PRI, etc 4, 000003, PRI, etc 5, 000003, PRF, etc 6, 000003, OTH, etc So, my result set from the table above would be: 2, 000001, PRF, etc 3, 000002, PRI, etc 5, 000003, PRF, etc What is the best way to obtain this information via a SELECT statement?  Thanks in advance, Steve  

Get output from storedprocedure and assign in Select statement

  
Is it possible to get the output from stored procedure and assign into select statement. for example. Here prc_GetComplete is another storeprocedure to get the value for @Completes INSERT INTO TABELE1 (ID, address, completes ) SELECT NEWID(), t2.address, @Completes = EXEC prc_GetComplete t2.ID , @Completes OUT FROM TABELE2 t2   Regards, teesh

ODBC Select if Statement in MAS90 query

  
Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

NULL label doesn't appear on drop down or multi-select parameter.

  
Hi, I am using SSRS 2005 SP3. I have the following query that I use for my parameter: SELECT 0 AS c1, 'NOT Specified' AS c2 UNION SELECT 1 AS c1, NULL AS c2 UNION SELECT 2 AS c1, 'Test 1' AS c2 UNION SELECT 3 AS c1, 'Test 2' AS c2 Let's call my parameter prm1. The type of prm1 is string and it has "Allow null value" and "Allow blank value" options ticked. C1 is the value field and C2 is the label field for prm1. When I preview the report, and I didn't see NULL in my drop down list,  but that record has been shown as 1 (which is the value). I am wondering if this is a normal behavior of SSRS? It's not a big deal. I can replace NULL with something else, but I am just curious. Cheers, Uzzie

ReportViewer 2008 multi-select parameter alignment

  
We are using SSRS 2008 SP1 & ReportViewer 2008 SP1. The multi-select parameter options are left aligned in the drop-down listbox on the Report Server but when run thru ASP.Net & reportViewer, they are aligned in the center of the drop-down list. This is very problematic because the width of the drop-down is also narrow compared to the text size of the fields. I tried wrapping the ReportViewer in a Div tag (<div style="vertical-align: left; text-align: left">), but still no joy.  Would appreciate any help thanks.

Select statement

  
Hi All. Greetings. I have 2 tables.Table =Person ID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID fakeID---------- ----------- --------------1 1 442 6 553 2 66select * from person, detail where person.id=detail.person_id and fakeID=55I want it show me record of 55 because of Person_ID 6 is not in Table Person and i am using operator AND,so It is not showing any thing.But i want, it show me record of 55 so what i change in this statement or which opertor i use .... so it show me.select * from person, detail where person.id=detail.person_id and fakeID=55I mean i want if value 6 in the (table Person) it show me the name of Person, but if ID 6 or value 6 not there even it show mebut show me empty place of name. Because 6 i required. ID Person ---------- ----------- 6 Any if there is 6 in the (Table Person) then ID Person ---------- ----------- 6 Rubi Thanks Advance

Textbox -> Value -> Gridview -> Select Statement

  
Hi guys. I have textbox that searches for first or last name. Result show in gridview. But when add value in text box and push my button Find nothing happen. Here code:aspx:........ <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="MobileNum" DataSourceID="SqlDataSource5" style="font-family: Verdana" onselectedindexchanged="GridView1_SelectedIndexChanged"> <Columns> <asp:BoundField DataField="FNAME" HeaderText="FNAME" SortExpression="FNAME" /> <asp:BoundField DataField="LNAME" HeaderText="LNAME" SortExpression="LNAME" /> <asp:BoundField DataField="MobileNum" HeaderText="MobileNum" ReadOnly="True" SortExpression="MobileNum" /> <asp:BoundField DataField="HomeNum" HeaderText="HomeNum" SortExpression="HomeNum" /> <asp:BoundField DataField="OfficeNum" HeaderText="OfficeNum" SortExpression="OfficeNum" /> <asp:Bo

How to read excel header name and put into SELECT statement?

  
Hi,        I'm trying to read Excel file header column name. Then I can query data from excel using SELECT statement. For instance. column name is name, department and email.  Then I want this three header name being detected using code and put in select statement. User no need to care about the header name and we can use code to read the header name instead of hardcode header name inside SELECT statement.       Please provide the details. Thanks.

Multi select control does not work with Infopath. :(

  
I create new lookup column that allows multiple values.  The important part is the column is the Title(linked to item) so the item is shown as Hyperlink item to the actual item.  This works fine, but then I would like to fine tune my form with infopath.  When I open this form it complains about these columns, basically will not let me open the form without first deleting these lookup columns.  So I then try the other way I create a lookup column from infopath and this work fine, the only problem being is these items no longer so up as hyperlinks to the original items.  This is a major bumma, any one have any suggestions to get this working?

Multi Select Sorting help

  
I need help with a bit complex JavaScript. This is what I am trying to do: I have a database table with id and title. I am populating the multi select list with the values "id + title" (ex: 1234 Title1) What I need to do is sort the list based on the items selected. There is a sort button which when a user clicks after selecting his titles should push the selected items to the top of the list and sorted and the list that's not selected must also be sorted based on the title. For ex if I have these in the select list:my options will look like this <option value="122" id="001">122 Amazing Facts</option><option value="890" id="002">890 Famous Seven</option><option value="345" id="003">345 Niagara Falls</option>......<option value="100" id="007">100 Zimbabwe</option>122 Amazing Facts 890 Famous Seven 345 Niagara Falls 879 Spain Champions 865 Trinidad & Tobago 213 Victoria Falls100 Zimbabwe They are all sorted on the title (or the ID since I am populating them based on the titles). This will be the first pass when I populate them. Now the user selects 890 Famous Seven, 213 Victoria Falls, 100 Zimbabwe and hits the sort button. The result should look like this 890 Famous Seven 213 Victoria Falls 100 Zimbabwe 122 Amazing Facts 345 Niagara Fa
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