w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Stored procedure inserting values into a table using Table-Valued Parameters
You can use the MERGE statement to capture the category IDs. DECLARE @changes TABLE (ChangeID VARCHAR(10), Id INTEGER); DECLARE @JustSomeRandomVariable INT; MERGE Category AS TARGET USING @data AS SOURCE ON TARGET.Category = SOURCE.s WHEN NOT MATCHED THEN INSERT ([Category]) VALUES (SOURCE.s) WHEN MATCHED THEN UPDATE SET @JustSomeRandomVariable = 1 OUTPUT $action, inserted.Id INTO @changes; The random variable in the merge statement makes sure that updates get logged into the @changes table variable. Now you can use the @changes to update your link table. INSERT INTO Link SELECT ProductID, ChangeID FROM @changes Just retrieve the required ProductID with a simple select query. EDIT: This could potentially result in double records in the Link table. You might need

Categories : Sql Server

How to insert table valued parameters into Sql Azure from Node?
The parameters to the function query have to be passed as an array (see example here). Try calling it as such, it should work: sql.open(connectionString, function(err, conn) { if(err) { return console.error('could not connect to sql', err); } var tableValuedObject = { Data: 'Hello World' }; var query = 'usp_InsertSomeTable ?'; conn.query(query, [tableValuedObject], function(err, result) { if(err) { return console.error('error running insert', err); } }); });

Categories : SQL

Returning unique valued entities with their count?
This will do the trick SELECT tag, COUNT(*) `count` FROM table1 GROUP BY tag ORDER BY `count` DESC Output: | TAG | COUNT | |------|-------| | food | 2 | | shop | 1 | Here is SQLFiddle demo

Categories : Mysql

How can I manipulate the data from a Table-Valued Parameter of a User-Defined Table Type passed into a Stored Procedure?
You can create a temp table in the parent procedure and act on it from within the child procs. Here is a crude example; of course, in a production setting you would beef this up with some defensive programming / transaction handling / etc. create type CustomTable as table (Id int primary key, Name varchar(10)); go create procedure dbo.Worker1 as begin delete from #work where Id > 5; end go create procedure dbo.Worker2 as begin insert into #work select -1, 'New One'; end go create procedure dbo.Driver (@input CustomTable readonly) as begin -- #work is created by dbo.Driver but visible to all child procs -- its also scoped to this instance of Driver select * into #work from @input; -- do complicated work exec dbo.Worker1; exec dbo.Worker2;

Categories : SQL

Table Variable In a table Valued function
When you're using multiple statements in a function and returning a table, i.e. a Table-Valued User-Defined Function, you need a certain syntax, something like: CREATE FUNCTION dbo.MyFunction(@ID int) RETURNS @Mytable TABLE ( -- Columns returned by the function ID int PRIMARY KEY NOT NULL, -- (Other columns as required) ) AS BEGIN --Various statements to populate @Mytable RETURN; -- Returns @Mytable END See Table-Valued User-Defined Functions for more information. If you have a function that just has RETURNS TABLE with no definition of the table being returned, this is an Inline User-Defined Function. Inline user-defined functions are a subset of user-defined functions that return a table data type. Inline functions can be used to achieve the functionality o

Categories : SQL

Table-Valued-Function with cte
I would be inclined to first get the list of dates and then apply the function: with dates as ( select @fromdate as date union all select date + 1 from dates where dates.date < @todate ), T(employee_id,actualogin,actuallogout) as ( select t.* from dates cross apply dbo.ufn_GET_ATTENDANCE(dates.date) )

Categories : SQL

Need to call CLR Table-valued UDF from a View
You may try following construct (though I think it's a bad idea): create view myView as select IDs.ID, f.Value from (values (1), (2), (3), (4), (5), (6)) IDs(ID) -- possible ID values cross apply udf(IDs.ID) f GO select * from myView where ID=6 This approach, however, probably will be near useless if UDF has multiple parameters, and useless, if it has parameters of poorly-countable or uncountable types.

Categories : Sql Server

Table Valued Function and Entity Framework
Here is a very good article on the newer features of Entity Framework that provide direct support for Table Valued UDFs. MSDN blog on Table-Valued Function Support in Entity Framework. For greater depth, this article provides significant detail. EDM and store functions exposed in LINQ. One of the great advantages of the recent support for Table-Valued UDFs involves support for Full-Text Search capabilities. Read more about that here: Full text search features involving database objects.

Categories : C#

Join to Table-valued Function failing
You need CROSS APPLY to call the TVF with a correlated parameter. SELECT t.Value, s.* FROM dbo.Test t CROSS APPLY dbo.CTESplitChunk (t.Value, ';') s WHERE t.Setting = '3151'

Categories : Sql Server

table Valued Function Query Optimize
I see you are using SQL Server XML data type. Do you know you can index it? Try with this: CREATE PRIMARY XML INDEX PXML_tab_tabxml ON tab (tabxml) CREATE XML INDEX IXML_tab_tabxml ON tab (tabxml) USING XML INDEX PXML_tab_tabxml FOR PATH; First statement will shred your xml to internal structure (index rows) and enable SQL Server more effectively query such disassembled XML. This is known as a PRIMARY XML INDEX. However, if you are querying your XML from some table using a value() method, consider running the second statement which will further speed up such operations. This is known as a SECONDARY XML INDEX and can be of three different types. Be aware that creating such XML indexes on large tables where XML already exists can take time.

Categories : SQL

multi statement table valued function
select q.Question_ID, q.Answer, count(*) as Total, count(*) * 100 / (select count(*) from questions as t where t.Question_ID = @question_id) as [Percentage] from questions as q where q.Question_ID= @question_id group by q.Question_ID, q.Answer

Categories : Mysql

Using Execute() method in Table-valued function SQL Server
I think using dynamic SQL is unnecessary in your case, so try this one - Query: CREATE FUNCTION SortRoutePartByDay ( @date DATETIME ) RETURNS TABLE AS RETURN SELECT * FROM dbo.RoutePartPart WHERE DATENAME(weekday, @date) = 1 Small info: Functions on SQL Server are not the same as stored procedures, they have several limitations on the things that can be done. For example, you can't use dynamic SQL. Update: CREATE FUNCTION SortRoutePartByDay ( @date DATETIME ) RETURNS TABLE AS RETURN SELECT p.* FROM dbo.RoutePartPart p CROSS JOIN ( SELECT [WeekDay] = DATENAME(weekday, @date) ) t WHERE ([WeekDay] = 'Monday' AND [Monday] = 1) OR ([WeekDay] = 'Tuesday' AND [Tuesday] = 1) OR ([WeekDay] = 'Wedne

Categories : SQL

Creating a set from Table Valued Function in Entity Framework
Why not create a recordset-returning parameterized stored procedure (SP) with the UserID as a parameter? Entity Framework can work with this. You could call your TVF in the SP, or just transliterate the TVF content in the SP. If you needed add/delete/update functionality, you may need SPs for those functions too, if your query is non-updateable.

Categories : C#

Execute a Table Valued Function as an IQueryable in Entity Framework
You can convert the result to an IQueryable by using AsQueryable(): IEnumerable<Record> result = {call TVF}; IQueryable<Record> query = result.AsQueryable(); return query;

Categories : C#

How does one use SSDT to define a stored procedure which has a table-valued parameter?
SSDT is indeed smart enough to order the type in the publish script before the sproc. Therefore, there was no need to worry about their being in separate files. Turned out to be pretty neat.

Categories : Sql Server

Remote table-valued function calls are not allowed while copying data
The error message seems pretty self-explanatory to me. One potential workaround would be to create a stored procedure on the remote server: CREATE PROCEDURE dbo.whatever AS BEGIN SET NOCOUNT ON; SELECT *,getdate() MyTimeStamp From sys.dm_io_virtual_file_stats(NULL, NULL); END GO Then on the local server: Insert into AkshayDestination..StudentDestination EXEC AkshaySource.databasename.dbo.whatever; Another idea is to use dynamic SQL: Insert into AkshayDestination..StudentDestination EXEC AkshaySource...sp_executesql N'SELECT *, GETDATE() FROM sys.dm_io_virtual_file_stats(NULL, NULL);';

Categories : Sql Server

PostgreSQL: Serially apply table-valued function to a set of values and UNION ALL results
I think it's impossible to do in current version of PostgreSQL (9.2). In 9.3 there would be LATERAL join which does exactly what you want. You can, however, apply function returning set of simple values: select id, func(id) as f from tbl1 sql fiddle demo

Categories : SQL

TSQL Split Function to create multiple rows and columns (table valued function)
One way to do this is XML DECLARE @str VARCHAR(2000) = 'Master Section 1,Stuff 1,234345,3453453,23423423; Master Section 1,Stuff 2,6786787,6576576,76577; Master Section 2,Section 2 Stuff 1,898676,9870970,8708798' DECLARE @strXML VARCHAR(8000) = '<table><row><col>' + REPLACE(REPLACE(@str,';','</col></row> <row><col>'),',','</col><col>') + '</col></row></table>' DECLARE @XML XML = CAST(@strXML AS XML) SELECT line.col.value('col[1]', 'varchar(1000)') AS col1 ,line.col.value('col[2]', 'varchar(1000)') AS col2 ,line.col.value('col[3]', 'varchar(1000)') AS col3 ,line.col.value('col[4]', 'varchar(1000)') AS col4 ,line.col.value('col[5]', 'varchar(1000)') AS col5 FROM @XML.nodes('/table/row') AS line(c

Categories : Sql Server

PHP - MYSQL not returning result within set parameters
It may be a case of needing brackets around the OR statements like so; SELECT * FROM documents WHERE (cat=1 OR cat=3) AND date>=2001 AND date<=2003 AND run <90 ORDER BY RAND() LIMIT 0,10

Categories : PHP

Method without parameters returning List in Web services
You can get a JSON from your List and send the array of bytes of the JSON's file. And in the calling method you get that array of bytes, create a temp file (if it's necessary), get the List from the JSON's file. I do this is my application between Android and Java, and it's working fast than send the Object, because I zip the file too. []s William Bertan

Categories : Java

TryXXX-like methods with "out" parameters vs returning a nullable value type?
Nullable was introduced - like generics - in C# 2.0. There is a lot of code that predates that. And to be honest, I am not a big fan of just returning a Nullable whenever something can go wrong. What you usually have is a method like SomeOtherType Something(SomeType inputData) { ... } which will throw an exception if something goes wrong. Now in some cases, you want to avoid an exception, so there is an alternative way: the one you gave. bool TrySomething(SomeType inputData, out SomeOtherType result) { ... } However, an exception is precise in what went wrong, a boolean value isn't. So if it gives you false, you have little more information that "It didn't work". In some cases, that might be enough. Me, I like to distinguish a "My collection did not contain that value" error from a

Categories : C#

Read returning parameters when calling a lua function using LuaInterface
Try to return the value. object[] res = lua.DoString ("return some_lua_function()"); Like this https://github.com/NLua/NLua/blob/master/tests/LuaTests.cs#L473

Categories : C#

Iterating through table to use as parameters in function to create large new table
"Computer_Type, Jul_Num, Aug_Num, and Sep_Num" One-column-per-month works for reporting or a data-entry interface, but you are going to drive yourself absolutely bonkers if you actually store the data that way. If you have the means to go back and change that table to "Computer_Type, Year, Month, Num" or "Computer_Type, Date, Num", then you should do that first.

Categories : SQL

Overloading an operator through friend function and returning a type different from the rhs parameters
You don't really need to define operator+ as friend: template<int I> class A { double X; template<int> friend class A; public: A(A const&) = default; A(double x) : X(x) {} template<int J> A(A<J> const&a) : X(a.X) {} template<int J> A<I+J> operator+ (A<J> const&a) { return A<I+J>(X+a.X); } }; int main() { A<0> a0(3); A<1> a1(4); auto ax = a0+a1; } Moreover, the result returned by operator+(a,b) should really be identical to that obtained by operator=(a) followed by operator+=(b).

Categories : C++

SQL returning all rows in one table where a column valule is equal to matching column value joined to another table
I am not sure if this is exactly what you want but you could probably twik it a bit to better fit your needs: SELECT Purchasse FROM PURCHASSE INNER JOIN INFO ON INFO.Name = PURCHASSE.Name WHERE INFO.ID = yourID This should give you Purchasse for any given ID that the Name match for the two columns.

Categories : Sql Server

set table name with hibernate name parameters
It is not possible to set table name dynamically.You can set dynamically column names.it is not possible to set table name try like this select a.name from '+table name+'where a.id = '2'

Categories : Java

returning 1st column from table over and over again
$('.tablecellname') selects all elements in the entire document that match. Instead, in your function you want to find children of the currently iterated element: $(this).find('.tablecellname').html()

Categories : Jquery

Returning a table from a cursor
Your (simplified) function could look like this: CREATE OR REPLACE FUNCTION my_cursor() RETURNS SETOF RECORD AS $func$ DECLARE _ucn text; BEGIN FOR _ucn IN SELECT ucn FROM customer LOOP RETURN QUERY SELECT * FROM get_custom_fields(_ucn) WHERE name = 'POP'; END LOOP; RETURN; END $func$ LANGUAGE plpgsql; I am assuming data type text for ucn here. But really, you should define the RETURN type, to avoid having to provide a column definition list with every call. If you just want the column field_value, and it is type text: CREATE OR REPLACE FUNCTION my_cursor() RETURNS SETOF text AS $func$ DECLARE _ucn text; BEGIN FOR _ucn IN SELECT ucn FROM customer LOOP RETURN QUERY SELECT field_value FROM get_custom_fields(_ucn) WHERE name

Categories : Postgresql

Querying a table in Excel with parameters using VBA
VBA code that generates a dynamic parameterized query against AdventureWorksDW2012. Put the parameter, for example USD, in cell A1. Sub DynamicParameterizedQuery() Dim lo As ListObject Set lo = ActiveSheet.ListObjects.Add(xlSrcExternal, "ODBC;Driver={SQL Server Native Client 11.0};Server=.;Database=AdventureWorksDW2012;Trusted_Connection=yes", True, xlYes, Range("A2")) lo.QueryTable.CommandType = xlCmdSql lo.QueryTable.CommandText = "SELECT * FROM DimCurrency WHERE CurrencyAlternateKey = ?" With lo.QueryTable.Parameters.Add("Currency code", xlParamTypeVarChar) .SetParam xlRange, ActiveSheet.Range("A1") .RefreshOnChange = True End With lo.QueryTable.Refresh BackgroundQuery:=False End Sub If don't have AdventureWorksDW2012 installed you can

Categories : SQL

Returning table from CLR stored procedure
To my knowledge, the only way to send results that did not originate from the context connection is to build a new result set metadata object and then send the rows up one at a time. Something like: private static void sendResultsetRecords(SqlDataReader dataReader) { SqlMetaData[] meta = createMetaDataForResultset(dataReader); SqlContext.Pipe.SendResultsStart(new SqlDataRecord(meta)); sendEachRecordOfData(dataReader, meta); SqlContext.Pipe.SendResultsEnd(); } You can find the complete implementation in the tSQLt.ResultsetFilter method that is part of the tSQLt database unit testing framework for SQL Server. The relevant code starts at line 56 of this file: http://sourceforge.net/p/tsqlt/code/HEAD/tree/tSQLtCLR/tSQLtCLR/ResultSetFilter.cs#l5

Categories : Sql Server

Jquery returning all to much data I only want the table
The page you're calling is url: $(this).attr('href') and that's a web-page you're just on with all headers and stuff... You have to put your functions in a separate .php file that doesn't contain a whole web-page frame and only outputs the text you want. If that's not possible then create in your success callback function with document.createDocumentFragment(), pass the whole response inside the fragment and pull out your stuff you need with something like myHTML = docFrag.getElementsByTagName('div')[0] and then pass this with append to your form. You might also want to set something like contentType: "text/plain; charset=utf-8" in your ajax call. By the way: If you're not using the convenience of parsing variables inside strings you might want to use a different way (the other quotes)

Categories : Ajax

Inserting zero-valued rows
You can use COALESCE or NVL to define an alternative to NULL --:: category 1 INSERT INTO dbo.xxx SELECT Category = 'cat_1', x = COALESCE (SUM(x),0), y = COALESCE (SUM(y),0), z = COALESCE (SUM(z),0) FROM dbo.yyy x WHERE DateKey >= period1;

Categories : SQL

How to design a table for nodes with 'dynamic' parameters
Fastest way is using a flat structure: Figure out your max amount of fields and what types those fields will be Create a big wide flat table that has columns like "text1", "text2", "date1", "date..5" etc. Have another table that maps those generic column names to whatever your custom nomenclature is based on the type foreign key Sane people usually use JSON or XML to store dynamic content: A lot of RDBMS have good support for parsing XML You can create a table with a few of the standard columns like "Type" and "Name" and ahve xml columns for "CustomValues"

Categories : Database

Returning narrowed down SELECT based on associated table
Depending on whether or not you need the WHERE condition before you add the 3rd table, you can try one of these 2 options: SELECT * FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.FIELDA = T2.FIELDA INNER JOIN TABLE3 T3 ON T1.FIELDA = T3.FIELDA WHERE T1.FIELDB = 'aaa' AND T3.FIELDC = 12 or: SELECT * FROM (SELECT T1.FIELDA, T2.FIELDB FROM TABLE1 T1 LEFT OUTER JOIN TABLE2 T2 ON T1.FIELDA = T2.FIELDA WHERE T1.FIELDC = 'aaa')T3 INNER JOIN TABLE3 T4 ON T3.FIELDA = T3.FIELDA AND T4.FIELDC = 12 I hope this gives you something to work with. If you provide some sample data, I can set up a working exa

Categories : SQL

Why isn't this test SQL injection returning all rows in the table?
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'"; $result = mysql_query($query); See how your SQL string variable is different to the variable you put in the query function? You are building $query_bad but passing $query to the mysql_query() function.

Categories : PHP

table adapter returning incorrect results
foreach (string[] item2 in Results) { objSqlCommands.sqlCommandInsertorUpdate2("INSERT", item2);//You were mixed up with Results here } I think your code might be this (I'm not sure if your objSqlCommands.sqlCommandInsertorUpdate2 can handle a string[] passed in?) foreach (string[] item2 in Results) { foreach(string item in item2) objSqlCommands.sqlCommandInsertorUpdate2("INSERT", item); }

Categories : C#

Vector-valued Constraints in NLOpt
Make sure that your function eqcontr has the same form as your objective function function. Maybe post it as well, so it'll be easy to understand. Also, I cannot see where condition is defined.

Categories : Python

calculate vector valued Hessian in R
vcov(out1) returns an estimate of the scaled variance-covariance matrix for the parameters in your model. The inverse of the cross product of the gradient, solve(crossprod(J)) returns an estimate of the unscaled variance-covariance matrix. The scaling factor is the estimated variance of the errors. So to calculate the scaled variance-covariance matrix (with some rounding error) using the gradient and the residuals from your model: df = length(Hobs) - length(out1$par) # degrees freedom se_var = sum(out1$fvec^2) / df # estimated error variance var_cov = se_var * solve(crossprod(J)) # scaled variance-covariance print(var_cov) print(vcov(out1)) To brush up on non-linear regression and non-linear least s

Categories : R

Selected Valued in JSF PRIMEFACES not been displayed
p:commandButton and p:commandLink actions does not work inside data table, data list or any component which is populated by a List in backing bean if the list is not enclosed by p:column. Enclose each item which is shown in table by p:column. <p:dataList value="#{tableBean.applicantlist}" var="applicant" id="applicants" paginator="true" rows="5" paginatorTemplate="{PreviousPageLink} {CurrentPageReport} {NextPageLink} {RowsPerPageDropdown}" rowsPerPageTemplate="5,10,15" type="none"> <p:column > <h:outputText value="#{applicant.jobtitle}, #{applicant.useremail}" style="margin-left:10px" /> </p:column > <p:column > <p:commandButton icon="

Categories : Java

Three valued logic with nullable bool?
I would stick with the nullable boolean, as this is how you will represent it in the database. Either NULL, Yes or No. From Nullable Types (C# Programming Guide) Nullable types are instances of the Nullable struct. A nullable type can represent the correct range of values for its underlying value type, plus an additional null value. For example, a Nullable, pronounced "Nullable of Int32," can be assigned any value from -2147483648 to 2147483647, or it can be assigned the null value. A Nullable can be assigned the values true false, or null. The ability to assign null to numeric and Boolean types is especially useful when you are dealing with databases and other data types that contain elements that may not be assigned a value. For example, a Boolean field in a databas

Categories : C#



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.