.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

replacing function within a join

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

I am trying to replace a function and I'm thinking of the cleanest and neatest way to rewrite this, I can rewrite this within the SQL statement but I can see that it has a lot of string manipulation within it. I am thinking that one can use a cross apply here or other techniques.




CREATE                FUNCTION fnReturnFunction(
	@FAMILY_STD_FLAG char(1),
	@SUB_INDUSTRY char(8),
	@REIT_INDEX_FLAG char(1),

View Complete Post

More Related Resource Links

Cross Join and error Two sets specified in the function have different dimensionality.

WITH   SET   [InternetSET] AS {   CROSSJOIN ([APM Channel].[APM Channel Desc].&[Interactive] , {        [APM SubChannel].[APM Sub Channel Desc].&[Affiliates]       ,[APM SubChannel].[APM Sub Channel Desc].&[Partner]       ,[APM SubChannel].[APM Sub Channel Desc].&[Unreferred]       ,[APM SubChannel].[APM Sub Channel Desc].&[SMS]       ,[APM SubChannel].[APM Sub Channel Desc].&[Search]       ,[APM SubChannel].[APM Sub Channel Desc].&[Media]       ,[APM SubChannel].[APM Sub Channel Desc].&[E-mails] } ) }   SET   [Top1SET] AS {   TOPCOUNT([InternetSET],1,[Measures].[% Approve]) } MEMBER   [Top1]   AS   SUM([Top1SET],[Approved Apps]) / SUM([Top1SET],[Basic Apps]), Format_String='0.00%'   MEMBER   [Internet]   AS SUM([InternetSET],[Approved Apps]) / SUM([InternetSET],[Basic Apps]), Format_String='0.00%' select { // if I use the set below instead of the member below it I get // error : Two sets specified in the function have different dimensionality [Top1set] // if I use the member below instead of the set above the query works // but the name of the item is not dis

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Does SQL Server has the similar function just like Trace Event 10104 for hash join in Oracle?


My question is  how can SQL server display the  information about the buckets when doing the hash join just like the function of Trace Event 10104 in Oracle.By the way ,In Oracle ,the Event 10104 event dumps hash join statistics .
The buckets message what i needed is similar as the following,that is to say ,it must contains the information about buckets .
01 ### Hash table ### 
02 # NOTE: The calculated number of rows in non-empty buckets may be smaller 
03 # than the true number. 
04 Number of buckets with 0 rows: 16373 
05 Number of buckets with 1 rows: 0 
06 Number of buckets with 2 rows: 0 
07 Number of buckets with 3 rows: 1

how to use table valued function with join on table in server 2000


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt

replacing ok and cancel button of an alert message with YES an NO button in asp.net web application.


i want to replace ok and cancel button of an alert message with YES an NO button in asp.net web application.When a user will click yes the data will be saved in the database and if user clicks that will act as a reset button and do not save the data in database and the control will move with as usual to the redirect page.Any pointers will be highly appreciated.I am usin g C# language.

Content page control passes as argument to javascript function


Here is the content page:


<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="JavaDynamicCopyValue.aspx.cs" Inherits="JavaDynamicCopyValue" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
 <script type="text/javascript" >
 function copyValue(input, output )
   ouput.value = input.value;
   return true;
 <asp:TextBox ID="txt1" runat="server" onchange="copyValue(this,document.getElementById('<%=txt2.ClientID%>'))"></asp:TextBox>
    <br />
    <br />
    <asp:TextBox ID="txt2" runat="server"></asp:TextBox>


I got a javascript run time error says output is undefined. if I view source of the page, the txt1 markup became:

<input name="ctl00$ContentPlaceHol

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.


I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Public Shared function doubt????

I am using VB.net/ASP.NET and SQL Server 2000 for a web application.

For populating the dropdowns, I wrote a Public Shared function LoadDDL(), in which I pass the dataset and dropdown name to set.

I have my doubts that this function will produce unexpected results for concurrent users. And will mix the results among sessions because it is a Shared function.

I am confused here. Is it the right way to go?

Please advice. Thanks in advance.


Trim function in C Sharp

How do i use "trim" function in C Sharp? I have a string and i want to strip off spaces from the end of the string. Any idea how do i do it??


Pure C++: Invoking Generic or Function Templates


As I promised last time, in this month's column I'll walk through the process of defining and invoking a generic or template function under C++\CLI. A function template or a generic function begins with the template or generic keyword followed by its parameter list.

Stanley B. Lippman

MSDN Magazine October 2005

Cutting Edge: Using an Eval Function in Web Services


Web Services are often presented as the perfect tool for pro-grammers. They're interoperable, based on open standards such as SOAP and WSDL, and are fully integrated with the Microsoft® . NET platform.

Dino Esposito

MSDN Magazine September 2002

way of calling "javascript function of parent window from child window."

The article way of calling "javascript function of parent window from child window." was added by raja.s on Sunday, June 06, 2010.

Hi,Here i am going to explain how to call the javascript function of a parent window from the child window(pop-Up).Some may know before reading this post.Parent Java Script Function:script language="Javascript" type="text/javascript">

Need function to find difference between two dates


Hi all

I need a function written in vb to find the exact month difference between two dates.

For example

    Start Date : 01/01/2010

    End Date   : 15/02/2010

 then the difference value should be like this 1.5  i.e i need the difference in decimal value.

>>>How to use javascript function in aspx file?



I have a file util.js and there some funtions in same file.

For example functions name are keyUp, keyDown, isNumeric, isAlphaNumeric

I have used these functions in aspx code behind in a textbox to restrict user that he may not be able to input any special character.

Before, I used the code directly in aspx file but I have do it for different textboxes therefore to avoid much coding in aspx file I created util.js and put all code in this file but how to use these particular functions in aspx file I couldn't find the way.

Anyone have idea. Do share with me.

I used the reference like this:


' Font-Names="Verdana" Font-Size="8pt" Onkeyup= "keyUp(event.keyCode)" Onkeydown="return isAlphaNumeric(event.keyCode);" onpaste="return false;">

ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).


I Am Continiously Getting Error For Following Line Of Code, It Work  Fine In Other Database

Am I Doing Any Mistake

if (sh3itno != "" && dpsh3no != "")
                string queryy = "SELECT [CHEQNO] FROM [SH3MAST] WHERE (([CHEQDATE] = " + chqdate + ") AND ([FAVOUR] = " + favour + ") AND ([CHQTYPE] = " + ctype + ") AND ([TRSTN] = " + trstn + ") AND ([TRBANK] = " + trbank + ") AND ([SH3ITNO] = " + sh3itno + ") AND ([DPSH3NO] = " + dpsh3no + "))";
                OdbcCommand cmd3 = new OdbcCommand(queryy, connen1);
                string chqno =cmd3.ExecuteScalar().ToString(); (Here Iam Getting Error)
                tbl.Rows[i]["cheque no"] = chqno;
Stack Trace: 

[OdbcException (0x80131937): ERROR [S1000] [Microsoft][ODBC Visual FoxPro Driver]Function name is missing ).]
   System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) +39
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, Strin
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