My SQL query is getting an error:
#1054 - Unknown column 'teacher_invoices' in 'on clause'
[code]
SELECT
users.user_id,
users.type,
users.email,
users.firstname,
users.surname,
teacher_invoices.invoice_date,
teacher_invoices.teacher_id
FROM users
INNER JOIN teacher_invoices
ON users.user_id=teacher_invoices=teacher_id
WHERE type = 'teacher' AND teacher_invoices.invoice_date >= '2013-01-01'
[code]
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
Hello,
I need to perform an update to one of my columns in my gridView. Is it possible to pass in the "column name" during runtime as a parameter to a stored procedure. I tried doing that but it doesn't seem to work? I might be doing something wrong of course.
Can anyone give me some advice on how to do this?
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
View 2 Replies View RelatedI installed a script that is suppose to accept paypal, however on trying totest a payment, I get this error msg:ErrorDatabase access errorand I get this error msg emailed to me:Unknown column 'State' in 'field list'Query: 'INSERT INTO `TransactionsMembership` (ID, Sum, State ) VALUESwhat could be swrong, importantly how do I fix it?
View 2 Replies View RelatedHi,
I just installed SQL Server 2005 on Windows Vista.
I am getting an error "An unknown error occurred in the WMI
provider. Error Code 8000000A" when connecting to a web server. The
reporting services is running. Is there any other installation that is
missing. Please help.
protected void Button4_Click(object sender, EventArgs e)
{
string selectedItem = DropDownList1.SelectedItem.Text;
if (selectedItem == "zezo")
{
string connectionString2 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection2 = new SqlConnection(connectionString2);
connection2.Open();
SqlCommand command2 = new SqlCommand("finde", connection2);
command2.CommandType = CommandType.StoredProcedure;
command2.Parameters.Add("@columen_name", SqlDbType.NVarChar).Value = "taher";
command2.ExecuteNonQuery();
}
and this is the stored procedure
ALTER PROCEDURE dbo.finde @columen_name nvarchar(50)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
declare
@maxcount int,@sql varchar(8000)
set @maxcount=(select max(taher)+1 from counter)
set @sql='insert into counter (' + @columen_name +')
values ( @maxcount )'
exec (@sql)
************************
and this is the error
Server Error in '/NTSOLUTIONS' Application.
--------------------------------------------------------------------------------
Must declare the scalar variable "@maxcount".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@maxcount".
Source Error:
*********************************************
so i can not find where is the exact point
zezo
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
Hi all,I have the below user-defined function on mssql 2000 and I can't workout why i'm getting the following error:-----Server: Msg 156, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 15Incorrect syntax near the keyword 'IF'.Server: Msg 170, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 23Line 23: Incorrect syntax near ')'.----------CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)RETURNS moneyASBEGINRETURN (/* if the box is a paperback */IF (SELECT COUNT(BoxID) AS NoOfBoxes FROM OutworkerBoxes WHERE BoxID= @boxid AND BoxCode LIKE '%PAPER%') > 1/* If the books are paperback, charge 15p each and add on 30p for adescription book to make 45p */SELECT ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks * 0.30)FROM OutworkerBoxes WHERE BoxID = @boxidELSE/* If the books are normal, charge 25p each and add 20p on fordescription books to make 45p */SELECT ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks * 0.20)FROM OutworkerBoxes WHERE BoxID = @boxid)END-----Below is the sql for the table it works with:-----CREATE TABLE [OutworkerBoxes] ([BoxID] [int] IDENTITY (1, 1) NOT NULL ,[OutworkerID] [int] NOT NULL ,[ImportedBy] [int] NULL ,[StartRef] [int] NOT NULL ,[endref] [int] NOT NULL ,[DateIssued] [datetime] NOT NULL ,[BoxCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[DealerID] [int] NULL ,[StatusID] [int] NOT NULL ,[IssuedBy] [int] NOT NULL ,[BoxNotes] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,[DateImported] [datetime] NULL ,[NoOfDescriptionBooks] [int] NOT NULL CONSTRAINT[DF_OutworkerBoxes_NoOfDescriptionBooks] DEFAULT (0),CONSTRAINT [PK_OutworkerBoxes] PRIMARY KEY CLUSTERED([BoxID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO-----If anyone can advise me i'd be most grateful.Thanx in advanceJames
View 2 Replies View RelatedI recently tried Re-Installing SQL Server 2005 and setup fails with the following error message. I found a forum that instructed me to make sure that the Distributed Transaction Corrdinator Service is started and that the NT AUTHORITYNetworkService is set under the Log on as tab. I verified both of those settings in my services snap-in under the management console. Looks like setup is failing during this section: Integration Services - Configuring Components...
------------------------------
Microsoft SQL Server 2005 Setup
Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: You must have administrative credentials to perform this task. Contact your system administrator for assistance.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80131501
An unknown error (-50) occurred.
So I can not recieve my email.
Running Entourage Student Ed.2004, Mac OS 10.5!
does any know what this means:
View 3 Replies View RelatedExample, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies View RelatedI have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,
[Code]....
Hi All,
I was testing SQL Mail and I kept getting Unknown Recipient error regardless of what type of user names I tried to put in, I'd appreciate it if any of you could help.
Server: NT 4.0 Server (SP4)
SQL Server: 6.5 (SP4)
Mail Client: Exchange (5.0)
Since my company doesn't have a designated mailbox for SQL Server, I set it up to use my personal mailbox and profile. I was able to get SQL Mail up and running but couldn't not figure out a correct user name to pass to xp_sendmail.
The usual format of our e-mail addresses is <last name><first name>@xxx.com so I tried '<last name><first name>', '<last name>, <first name>' (the same format shown in the Address Book)...etc. but none of those worked.
Am I missing something?
Hi All,
I am new to sql server 2005.
I tried to connect to reporting services configuration manager.
it gives an error
An Unknown error has occured in WMI provider.
error code 80040219.
so please help me out regarding this issue.
I am encountering this error when tried to execute a simple select statement against the Enterprise Edition SQL 2000 and SQl2005
An error occurred while executing batch. Error message is: Unknown error "-1".
Please advise what is the cause of this.
Thanks.
Hi all,
I have the following query...
SELECT Count(*)
FROM Incidents I
WHERE (Priority = 1)
AND (Time_First_Unit_On_Scene IS NOT NULL)
AND (DateDiff(s, Time_ClockStart, Time_First_Unit_On_Scene) <= 480)
AND (Response_Date BETWEEN '1-Apr-2004')
AND ('31-Mar-2005 23:59:59')
AND (I.Disposition_ID <> 9 )
...and I get the following error message...
System.Data.OleDb.OleDbException: Difference of two datetime columns caused overflow at runtime.
... any one know what it could be?
Thanks
Tryst
I am trying to enable FILESTREAM on a SQL 2012 installation that is supporting a Sharepoint 2013 implementation. When I try to enable FILESTREAM using the SQL Server Configuration Manager I receive the following error:
There was an unknown error applying the FILESTREAM settings.
Check the parameters are valid. (0x80041008)
I have done this succussfully in the past in SQL2008R2/Sharepoin2010 environment but this is my first attempt with SQL2012/Sharepoint2013 and I am not sure how to proceed with troubleshooting.
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I have a SQL2008 database, running Standard Edition 64-bit, database owns by sa, connected to Management Studio using Windows Authentication mode. When I tried to generate scripts from a database, I got the following error messages:-
[Operation is not valid due to the current state of the object. (SqlManagerUI)]
which happened at one particular table. I have reviewed this table definitions are normal, and I could select data from it.
I couldn't find any information anywhere relating to this error messages, except that someone got it when they were trying to change the authentication mode or sa password.
Hi all,
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!
Below is an overview of my problem:
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
I'm trying to activate FILESTREAM on my SQL Server 2012, however i'm not accomplishing it. I've tried to follow this instructions: [URL] ...
My SETUP is the following:
Windows 8 (64 bit)SQL Server Standard Edition - Service Pack 1 (64 bit)SQL Version: (11.0.3128.0)
When I go to the configuration manager, on the FILESTREAM tab has a message:
"A previous FILESTREAM configuration attempt was incomplete.
FILESTREAM may be in an inconsistent state until re-configured."
If I click Enable FILESTREAM and "Apply", I get the following message:
"There was an unknown error applying the FILESTREAM settings. Check the parameters are valid. (0x80070002)"
I also read somewhere to run "EXEC sp_configure", which produces the result:
"filestream access level - 0 2 2 0"
Running "SELECT SERVERPROPERTY ('FilestreamEffectiveLevel');" returns 0.
I'm trying to execute a SSIS package via proxy user but I keep getting the following error message regardless of I have tried to do to fix it, I have done the following so far:-
1. Recreated the proxy user
2. Retyped the password, under credentials
Message : Unable to start execution of step 1 (reason: Error authenticating proxy "proxyname", system error: Logon failure: unknown user name or bad password.). The step failed.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
Hi i want to join two tables basing on like condition of the column values of two tables.
My query is like this:
select
pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName",
pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign",
pc_assign_worklist.pyLabel AS "pyLabel",
pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus",
pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" ,
pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" ,
pc_assign_worklist.pxCreateOpName AS "pxCreateOpName",
pc_index_workparty.MemberIdentifier AS "MemberIdentifier",
pc_index_workparty.LastName AS "Last Name",
pc_index_workparty.FirstName AS "First Name",
pc_index_workparty.pxInsName AS "pxInsName"
from
dbo.pc_assign_worklist, dbo.pc_index_workparty
where
pxAssignedOperatorID ='dasxkx1'
AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC
-----------
i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
...............
WHERE
af.Resort=@strResort
AND
(af.AccomType = 'H' OR af.AccomType = 'O')
AND
max_date>20040721
order by max_date.
Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
'max_date>20040721 '.
Thanks
In a SPROC I am creating, is there a way to use a columnName as a parameter and then do a filter on that based on a second parameter such as @columnValue ?
So instead of having to construct the WHERE clause or doing a bunch of IF statements to see what the column name is from the parameter and doing a query based upon that, is there a way to tell it to do a WHERE clause where @columnName = @columnValue ?
I do not want to use dynamic SQL string concatenation...
Hi :From a crystal report i get a list of employee firstnames as a stringinto my store procedure. Why is it comming this way ? hmmmmmm it's aquestion for me too.ex: "e1,e2,e3"here are my tablestblProjectsProjectId123tblEmployeeemployeeId FirstName1 e12 e23 e3tblProjectsToEmployeeProjectId employeeId1 11 21 32 12 23 13 23 34 14 3i need to find out the project ids all 3 of these employees worked on.so the out put i need isprojectId13How can i get it ????????????now i can use replace command to format it to a OR clause or ANDclauseSET @string= 'employeeId =' + '''' + REPLACE('e1,e2,e3',',',''' ORemployeeId = ''') + ''''some thing like this.OR clause will give me all 4 projects.in('e1','e2','e3') will give me all 4 projects.of cause AND command will not give me any.other method i tried was adding the employee table 3 times into thesame SQL string and doing some thing likeWHERE (empTable1.Firstname ='e1' AND (empTable1.Firstnamein('e2','e3'))AND (empTable2.Firstname ='e2' AND (empTable1.Firstname in('e1','e3'))AND ...and goes alone. this gives me some what i needed. but it's a verymessy way of doing it, because i get a comma seperated stringparameter i have to construct the sql string on the fly.any help or direction on this matter would greatly appreciated.thankseric
View 3 Replies View Related
Hi,
I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?
Thanks,
Martin
Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.
Code Block
DECLARE @MyTableVar table(
Test1 int NOT NULL,
Test2 int NOT NULL
);
SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';
UPDATE [TestTable] SET [Test3] = [Test3] + 'ds'
WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);
Hi
I need suggestion for a query. Consider following 2 tables.
Table-1 "T1"
-----------
|ID|Name
|1 |abc
|2 |def
|3 |erw
|4 |rwg
|5 |her
Table-2 "T2"
----------
|ID|Qty
|1 |12
|1 |2
|2 |22
|3 |10
|2 |14
I want a query which displays ID, Name and MAX(Qty) for each item where Max(Qty)>=10 i.e. result should be
Result
----------
|ID|Name|Qty
|1 |abc |12
|2 |def |22
|3 |erw |10
I tried:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE MaxQty>=10
But it fails as computed or inline query columns can not be added in where clause.
However following works:
Select t1.*, (Select Max(Qty) From T2 where ID=t1.ID) as MaxQty
FROM T1 t1
WHERE (Select Max(Qty) From T2 where ID=t1.ID) >=10
BUT IS IT OPTIMIZED?
Please suggest an optimized way to handle such scenarios.