.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

help with the query statement

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

More Related Resource Links

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

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 

Query help to write case statement in Where clause?

Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

How to avoid "'CREATE VIEW' must be the first statement in a query batch" when I need to use SET Q

I have a problem that I have been unable to solve.  I have a stored procedure in a database on server A that needs to create an indexed view in a database on server B.  (The environment is SQL Server 2005.)  The procedure on server A does the following:  (The EXEC statement inside the quotes is really a variable in my procedure, I've just substituted the value to make the example understandable.) EXEC sp_executesql 'EXEC [ServerB].[Database].dbo.sp_executesql @sqlStatement;' The "outer" sp_executesql causes the "inner" sp_executesql to be execute within the database on server B.  (@sqlStatement contains the CREATE VIEW syntax.)  I am able to create the view that I want to index with no problem.  However, when I attempt to create an index on the view in a subsequent step, it fails with the message "Cannot create index. Object '_dta_mv_51' was created with the following SET options off: 'QUOTED_IDENTIFIER'".  Fair enough, but if I attempt to precede my CREATE VIEW statement with the required QUOTED_IDENTIFIER, I receive an error to the effect that "'CREATE VIEW' must be the first statement in a query batch".  I'm not sure how to get around this problem.  Does anyone have the answer? SQL Server 2008 doesn't object to indexing a view that was created with QUOTED_IDENTIFIER

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

Update query from select statement


Cana you I need to do an update from a select statement...i'm really close here but have not quite figured out the nuance...

the full select statement provides correct results and i've joined the outer table with the inside table....can you make recommendations?

BEGIN TRAN --rollback

UPDATE geprs_price..T_ITEM_PRC_CHG 


		FROM geprs_price..T_ITEM_PRC_CHG gep
		and gep.prc_eff_dt = dm.prc_eff_dt
		LEFT JOIN construct..T_AD_USERS con
		ON dm.CONFIRM_BY  = con.UID
		LEFT JOIN construct..T_AD_USERS crt
		ON dm.CREATE_BY  = crt.UID

		AND dm.PRC_TYP_ID in (39)



Using CASE statement in "IN" clause of query


I have a sql datasource with the ControlParameter called ddlDropDownList.

2 questions:

1. Is my syntax below ok, I am getting a runttime error that the syntax is incorrect.

2. I have the select statement set up the control parameter to get the selected value from the dropdownlist as shown below, is that syntax correct?  especially the "selectedItem.value" for the propertyname property. thx

select fieldOne, fieldTwo from someTable where fieldOne IN 
CASE WHEN @ddlDropDownList='valueABC' THEN ('ABC') WHEN @ddlDropDownList='valueDEF' THEN ('DEF') 

<asp:ControlParameter ControlID="ddlDropDownList" Name="typeSlctd" PropertyName="SelectedItem.Value" Type="String" />

using IF statement with parameter query


I need to do an IF THEN depending on the query result.  If I use the SQLDataSource control...How do I declare and pass a variable for my IF THEN?  Or should I use ADO?  If so, how does it declare and pass variable in ADO? 


SQL Statement to Query Boolean Data Example


Hello All,


Looking for a sample sql statement to query a db table that boolean data.


Here is the scenario...

Records are lodging businesses with fields for each of the amenities.  Example of fields below.








After db is complete users will need to be able to query the db by checking checkboxes of the amenities they desire, click search, and have only those businesess appear with relavent amenities.

What might the a sameple sql statement look like for this?  FYI... at this time unknown how many amenities fields there might be and may need to add new amenities at any time.

Select * From TableName Where.....




What is the alternative for correlated query in select statement?



I have two table a and b and query like this

Select a.X,


       (Select min(b.x) from b...=a.... group by)

from a , b

where a... = b....


So it is using correlated query in select statement and it is very slow , what can I do increase the query performace





from .....



Can anyone provide some help tips on SQL query select statement


Hi, everyone

Can anyone provide some help tips on SQL query select statement.

Sample :

Table1 :

fieldID     fieldvalue

01           value1

02           value2

03           value3

04          value4


Store procedure expect parameter for partition


fieldID    partitionID     fieldvalue

01            1                value1

02            1                value2

03            2                value3

04            2        &n

sql query select inside a select statement???????


hi all,

i have two tables named

1.LookupPageInfo and    (LookupPageInfoId,pathmessageID,pagemessageID)

2.Messages     (MessageId,MessageText,ChannelID)

Now i need to write a query something like this........in the query i need to pass the pathmessageid and needs to get messagetext from Messages Table,i am not sure how i need to write the below query..can someone guide me how can i write a query......as below..

select LookupPageInfoId,



          (select messagetext from Messages where MessageID=PathmessageId) as TEXT  

          from LookupPageInfo

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.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Passing Linq Statement between master and content page


I usually work with windows forms, and am just starting to work with ASP.Net, so appologies if this is a dumb question!

I have a master page which has a search button on it. The results of this are built up using a Linq statement and if there are any results, I want to display the results in a contents page.

On the masterpage, I have the following code:

Private _res

    Public Overridable ReadOnly Property SearchRes()
            Return _res
        End Get
    End Property

 Private Sub DoSearch()
   'some code to get a linq result

    If res.Any Then
            _res = res
            Server.Transfer("SearchResults.aspx", True)
            'no results
            LabelNoRes.Visible = True
        End If
 End Sub

On the SearchResults.aspx page I have this code.

Dim m As pxSite = CType(Page.Master, MasterPage)
        If m.SearchRes IsNot Nothing Then
            Label1.Text = "Results found = " & m.SearchRes.Count
        End If

m.searchRes is always nothing. 

What am I doing wrong here?


sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)
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