.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

How to use SQL Hints within CTE/subquery

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Hi, I am trying to use the SQL Server Hints in CTE or in subquery but it is giving me the syntax error. Below is the sample code.   WITH Page AS ( SELECT * FROM Table_1 INNER JOIN Table_2 ON Table_1.c1=Table_2.c1 WHERE Table_1.c2='sample' OPTION (FORCE ORDER ); )   Regards, Jigs

View Complete Post

More Related Resource Links

form feild hints are not displaying for textboxses inside create user wizard?



I'm using following JS code to display input hint box besides the textbox whenever that asp.net textbox receives focus. 

I'm doing this for a registration form and all of the textboxes are inside the asp.net create user wizard control.

<script type="text/javascript">
function addLoadEvent(func) {
  var oldonload = window.onload;
  if (typeof window.onload != 'function') {
    window.onload = func;
  } else {
    window.onload = function() {

function prepareInputsForHints() {
	var inputs = document.getElementsByTagName("TextBox");
	for (var i=0; i<inputs.length; i++){
		// test to see if the hint span exists first
		if (inputs[i].parentNode.getElementsByTagName("span")[0]) {
			// the span exists!  on focus, show the hint
			inputs[i].onfocus = function () {
				this.parentNode.getElementsByTagName("span")[0].style.display = "inline";
			// when the cursor moves away from the field, hide the hint
			inputs[i].onblur = function () {
				this.parentNode.getElementsByTagName("span")[0].style.display = "none";

Linq Subquery



I have a query that looks like this in SQL:

FROM StockMateriaPrima SMP
WHERE SMP.Fecha = (SELECT Max(Fecha)
                    FROM StockMateriaPrima SMP2
                    WHERE SMP2.Id = SMP.Id )

Can anyone tell me how to write this query using LINQ in VB.Net ?

Thanks !

Subquery returned more than 1 value.

Hello, I need a gander at this query to figure out what i'm doing wrong. update Performance set pmpg = (select PMPG from Performance_Calculator where EFFDT_Converted = (select CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)) and Performance.DR_CODE = Performance_Calculator.FW_OWN group by Performance_Calculator.FW_OWN, Performance_Calculator.PMPG); basically the performance calculator, in the subquery, does return around 320 rows of data.  I thought the where clause would match up the rows of data and insert the pmpg into the performance table but instead I get subquery returned more than  1 value.  This is true but only if the performance_calculator.fw_own = performance.dr_code wasn't there.  I'm confused on this... any suggestions?

Sum With SubQuery

Hi people i am looking for This solution SELECT   funcionarioId, (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem i have this Query and it is Working Well and the result is: FuncionarioId | Value 1                   3010.00 2                   6000.00 1                   NULL 2                   50000.00 3                   2556.00 2                   300.00 4                   NULL  What i want is to SUM this SUM(VALUE) for each FuncionarioId, i dont have that column, those values is calculated from (SELECT SUM(Carga.ValorTotal) FROM CARGA WHERE Carga.ViagemId = Viagem.ViagemId) FROM Viagem. Thanks

Subquery returned more than 1 value

SELECT (SELECT fare_amount FROM fare_tab WHERE (class_code = 'ECO')) AS Economy, (SELECT fare_amount FROM fare_tab AS f2 WHERE (class_code = 'TOU')) AS Tourist FROM fare_tab AS f1 I always received an error of: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can I solve this?

How to write query with subquery more efficient

Hello, I have this query which generates sick days. I have a sick start date and end date, and by joining it with a date table I get all sick days for each employee. But as you can see i select a number of fields, but in the subquery i select a number of fields which are the same. Is it possible to write it more efficient? Thans in advance! SELECT z .ziekmeldingID, z .hrmID, z .ZiekStartDatum, z .ZiekEindDatum, z .ZiekStartTijd, z .ZiekEindTijd, z .ZiekOpen, z .ZiekTypeID, z .ZiekRedenID, z .ZiekStart_KEY, z .ZiekEind_KEY, z .Verzuimduur, D .Datum AS ZiekDag,   CAST(CONVERT(char(8), D.Datum, 112) AS int) AS ZiekDag_KEY, z .hr301_date, z .PercentageZiek, z .PercentageHersteld FROM     (   SELECT dbo .hr300.hr300_obj AS ziekmeldingID, dbo .hr300.hr300_date_start AS ZiekStartDatum,   CASE   WHEN dbo.hr300.hr300_date_end IS NOT NULL   THEN dbo.hr300.hr300_date_end   ELSE getdate()   END AS ZiekEindDatum, dbo .hr300.hr300_time_start AS ZiekStartTijd, dbo .hr300.hr300_time_end AS ZiekEindTijd, dbo .hr300.hr300_open AS ZiekOpen, dbo .hr300.hr104_obj AS ZiekTypeID, dbo .hr300.hr038_obj AS ZiekRedenID, dbo .hr300.hr200_obj AS hrmID,   CAST(CONVERT(char(8), dbo.hr300.hr300_date_start, 112) AS int) AS ZiekStart_KEY,   CAST(CONVERT(char(8), dbo.hr300.hr300_da

UNION hints

Are there any UNION hints that would allow for a UNION to determine uniqueness based only a specific columns instead of all columns?   I specifically need UNION instead of UNION ALL however one entity is showing up in both datasets however counts are different making two records when in reality I just want the first.

Error: 'SubQuery Returned More than 1 Value'

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1.  The code normally works however, every once in a while an exception is thrown: Code Snippet com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value.  This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?Stored Procedure: Code Snippet ALTER Procedure [dbo].[addRecord]@userID int,@itemID int,@info varchar(50),@comment varchar(50),@output int outputASDeclare @dateSubmitted datetimeset @dateSubmitted = getDate();--Insert the new record. THIS TABLE has an ID identity Primary Key --column that auto-increments.insert into RecordTable(UserPerson, Information, DateSubmitted)values (@userID, @info, @dateSubmitted);---Get the ID Assigned in the record table.  The Item table ---has a Foreign key on this column.Declare @assignedID  intset @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)/**Set the output parameter.*/set @output = @assignedID;--Now update the Item Table.insert into Item (ID, RecordID, Comment)values (@item,@assignedID, @comment);

insert query with subquery

Hello, I have 2 tables, one fact table (FeitDienstDagen) which contains employments, and a reference/dimension table, vDimMedewerker,  which contains a history of employees and their departments. IN the fact table I want to insert the correct WerkID from vDimMedewerker. So the employeeID (ms120_obj) must be same,  and the DienstDag_KEY (int date Key) must be greater then or equal to  the first werk_start_KEY (int date key) for that employee,  Ordered descendant. How to do that? Her my query, but it doesn't do the job :-) USE TEAMSOFT_TBSDB GO DELETE FROM dbo.FeitDienstdagen GO INSERT INTO [TEAMSOFT_TBSDB].[dbo].[FeitDienstdagen]            ([DienstverbandID]            ,[Deeltijdfactor]            ,[DienstDag_KEY]            ,[MutatieID]            ,[DienstverbandStart]            ,[DienstverbandEind]            ,[UrenWeek]            ,[Contracturen]            ,[UrenDag]     

problem with subquery.

I am having trouble writing this query using row_number. please help SELECT cust.IPDCCode, cust.remaindervalue, planvalue, cust.taccode FROM personprofile cust WHERE cust.profileid = 312456 AND cust.profileurn = ( SELECT MAX(profileurn) FROM personprofile WHERE profileid= 312456 AND reviseddate= '2009-02-22 00:00:00.000' )

Stored Procedure Subquery Issue

Hi, In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.  Thanks. ALTER PROCEDURE [dbo].[MainTbl] -- Add the parameters for the stored procedure here @Name nvarchar(5), AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Begin Transaction DECLARE @DetailsColour nvarchar(6); -- Insert statements for procedure here Begin -- Details DECLARE @CountDetails int; DECLARE @FirstEntryDetails int; SET @CountDetails = (SELECT COUNT (Name) FROM [DetailsTbl] WHERE Name = @Name ) if (@CountDetails > 1) Begin SET @FirstEntryDetails = (SELECT TOP(1) NameID FROM [DetailsTbl] WHERE Name = @Name ORDER BY NameID ASC) DELETE FROM [DetailsTbl] WHERE Name = @Name AND (NameID != @FirstEntryDetails) End Set @DetailsColour = (SELECT DetailsColour FROM [DetailsTbl] WHERE Name = @Name) if @DetailsColour is null begin set @DetailsColour = 'RED' end DECLARE @FoundData int; SET @Found

Subquery returning more than 1 value

Hey Forummers,I have a problem regarding this code:select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u where ToID = '92FBC025-5E08-4533-AF36-BF208391E4D6' and Accepted = 0SELECT NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) AS 'UserName', [Message], DateCreated, TypeIDFROM Notifications n, aspnet_Users uWHERE ToID = '92FBC025-5E08-4533-AF36-BF208391E4D6' AND Accepted = 0The problem lies in the code in bold. and the error message is as follows:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.I have a table called Notifications which has ToID which is a uniqueidentifier of the receiver and FromID which is a uniqueidentifier of the sender.What I want to achieve is to return a set of notifications to the client side according to the ToID of the table, but I do not want to display the FromId GUID in the client where the ToID is linked to. Instead I want to display a username in place of the FromID GUID which field is located in the aspnet_Users table.Any help would be appreciated and thanks in advance... =)CheersZeph

Cannot perform an aggregate function on an expression containing an aggregate or a subquery


How do i get around this???



sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost


record count for subquery which is used for INSERT

My current code is like this

Insert into ReportDetail( Partcipantid, Reportid)

      select distinct ParticipantID , 3 from abc   where

        closed = 0 and ( isnull ( primaryphone, '' )

Inserting foreign key value using subquery




I'm playing around with a training-wheel database.  I have a table that stores magazine subscription info.  I have a lookup table that stores the delivery period, ie. 'weekly', 'monthly', etc.  I have a primary key ID field in tblPeriods and a foreign key field in the main table, tblMags.  I want to insert data into tblMags without having to memorize the correlation between the period key and the period itself.  Here's what I tried first:


 ( vcName,
  fkPeriodID )
 ( 'The New Yorker',
  (SELECT  tblPeriods_ID FROM tblPeriods
   WHERE vcPeriod = 'Weekly'));


This gives the following message:


Server: Msg 1046, Level 15, State 1, Line 6
Subqueries are not allowed in this context. Only scalar expressions are allowed.


I'm thinking there must be a way to insert data like this and take advantage of a foreign key relationship without looking up a value each time.  Any ideas?






Subquery filter (how?)


SELECT     fec_doc, num_doc, fec_ven, monto, (SELECT sum(monto) as aas FROM cxc_op_m) r
FROM         cxc_op_m t
WHERE     (fec_doc <= ?) AND (cod_cli_f = ?) AND r.fec_doc <= t.fec_doc
GROUP BY num_doc, fec_doc, fec_ven,Helle monto


Can't handle a running sum.

This query works, but without filters.

SELECT     t.fec_doc, t.num_doc, t.fec_ven, t.monto, (SELECT sum(monto) as runningsum FROM cxc_op_m) 
FROM         cxc_op_m t
GROUP BY num_doc, fec_doc, fec_ven, monto

when i try this above do not work.

PDF Indexing - hints tips?


I've followed all the instructions I could find on these forums about configuring the Adobe iFilter for PDF indexing in Search Server Express.  Specifically the Adobe documentation.

When I run a full crawl of my file shares I still do not get any indexing of PDF file contents.  The filenames themselves come up in a search but I cannot search by content.

I know the PDFs I'm testing against are searchable.  But the following errors are still logged in the crawl logs:

The filtering process could not load the item. This is possibly caused by an unrecognized item format or item corruption.


Anyone have any other helpful tips to get this working?



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