w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Computed Column Specification in SQL Server - Time Difference in hours or minutes
ALTER TABLE yourTable ADD yourColumn AS (datediff(hour,[StartTime],[EndTime])) SQLFiddle DEMO EDIT: That was the syntax for alter table, off-course you can also define it during table creation CREATE TABLE tbl1 ( startDate DATETIME, enddate DATETIME, diffCol AS (datediff(hour,startDate,enddate)) ); SSMS also have an option in table designer to add formula for computed columns

Categories : SQL

Computed column for between values
You can try this : SELECT AGE, CASE WHEN AGE BETWEEN '1' AND '3' THEN 'INFANT' WHEN AGE BETWEEN '4' AND '12' THEN 'CHILD' WHEN AGE >='13' THEN 'ADULT' ELSE '' END PERSON_TYPE FROM PERSON

Categories : SQL

Django model with computed column
Instead of defining it at DB level, you can use uuid from python as default value for the uuid field. import uuid def get_uuid(): return str(uuid.uuid4()) class myTable(models.Model): id = models.AutoField(primary_key=True) myuuid = models.CharField(max_length=40, unique=True, default=get_uuid) anotherColumn = models.CharField(max_length=40) class Meta: db_table = "myTable"

Categories : Python

angularjs ng-grid computed column
You could watch for changes to the 'original_data' object and recreate the process_data object every time updates. http://plnkr.co/edit/c4iynItnznKNRoFgeuio?p=preview

Categories : Angularjs

How to use a total of a computed column as a value in another query?
Your situation is that you have quote lines with altlineref and calc_groupingref defined on them. Several rows might have calc_groupingref with the same value, but only one has the correct value of altlineref (all the other have NULL values). Here is an alternative way to formulate your query. The inner query just aggregates the quote lines by lead and calc_groupingref. In doing so, it finds the maximum value and the sum of the values. The outer query then combines this information with lead: select l.leadno, l.lead_desc, sum(case when maxref = 0 then ql.val end) as NONSPEC_VALUE, sum(case when maxref = 1 then ql.val end) as SPEC_VALUE from dba.lead l join (SELECT ql.leadno, sum(ql.calc_value) as val, MAX(ql.altlineref) as maxref FROM dba.a_quote

Categories : SQL

ensure data integrity with computed column
Calculated columns can break data integrity. If you build a table like this create table line_items ( order_num integer primary key references orders (order_num), line_item_num integer not null, qty integer not null, price_each decimal (13, 2) not null, price_extended decimal (13, 2) not null ); the dbms doesn't guarantee that the extended price is always the product of "qty" and "price_each". You can guarantee that, though, with a CHECK constraint. price_extended decimal (13, 2) not null check (price_extended = qty * price_each) In production, you'd also have CHECK constraints on "line_item_num", "qty", and "price_each". Probably none of those should be negative, and there should be a reasonable upper bound on each of them, too. You can build that table without the co

Categories : Database

Computed field value based on other column while insertion
As I said in the comment, you can use a temporary value for the name column. You can use a request like : INSERT INTO `table` VALUES('', 'name') /*let's assume name is the real name you want to insert*/ I'm not sure then how to use a trigger, but you may want to write something like this : delimiter # CREATE TRIGGER update_name_after_insert INSERT ON `table` for each row begin update `table` set name = CONCAT_WS("_", id, name) end#

Categories : Mysql

how to put a filter on magento collection on a computed column value
you can simply use having clause $collection->getSelect()->having('distance >10') directly in your query take a look at this question http://magento.stackexchange.com/questions/6305/how-to-add-dynamic-field-in-magento-collection

Categories : Magento

how to make sqlite column aliases numeric or computed
A column alias is an identifier like a table name or column name. To use an arbitrary string, you have to quote it, like with other identifiers: SELECT 42 AS "42 is the answer!!!" You cannot do computations on identifiers.

Categories : SQL

sqlalchemy/postgresql: database column computed default value
I found my answer here. Details are below (using sqlalchemy): create_fn_my_tran_set_num = DDL( ''' CREATE OR REPLACE FUNCTION fn_my_tran_set_num() RETURNS TRIGGER AS $$ DECLARE last_transaction_id INTEGER; BEGIN last_transaction_id := MAX(transaction_id) FROM my_tran WHERE tenant_id = NEW.tenant_id; IF last_transaction_id IS NULL THEN NEW.transaction_id := 1; ELSE NEW.transaction_id := last_transaction_id + 1; END IF; RETURN NEW; END$$ LANGUAGE plpgsql ''') event.listen(MyTran.__table__, 'after_create', create_fn_my_tran_set_trx_id) create_tg_my_tran_set_num = DDL( ''' CREATE TRIGGER tg_my_tran_set_num BEFORE INSERT ON my_tran FOR EACH ROW EXECUTE PROCEDURE fn_my_tran_set_num(); ''') event.listen(MyTran.__table__, 'after_create', create

Categories : Postgresql

Not getting the desired results in a computed column when 1 or more of the columns used have NULLS
Concatenating strings with null will result in null, see the remarks section here for more detail You should handle the nulls as appropriate CREATE TABLE People ( Firstname VARCHAR(50), Lastname VARCHAR(50) NOT NULL, Fullname AS CAST(CASE WHEN Firstname IS NOT NULL THEN Lastname + ', ' + Firstname ELSE Lastname END AS VARCHAR(105)) ) demo Arguably though, can it be a full name without both a first and last name, perhaps here null makes sense Edit: if either Firstname or Lastname can be null then you'd want to do something like this instead CASE WHEN Firstname IS NULL OR Lastname IS NULL THEN COALESCE(Firstname, Lastname) ELSE Lastname + ', ' + Firstname END demo

Categories : Sql Server

Entity Framework 4.1 Code First - Computed/Calculated column not being inserted
DatabaseGeneratedOption.Computed means that's the value is computed by the database engine. So EF will never update this value, only read from the db. So if you want this value in the db: - remove DatabaseGeneratedOption.Computed and set the value in your code, or - set a computed column at database engine side. Otherwise you should set RefId as not mapped. But in this case you will have no column in the db.

Categories : Entity Framework

requirejs & knockoutjs ko.computed Pass a function that returns the value of the ko.computed
Since Knockout 2.0, dependent observables are called computed observables. So: self.typeList = ko.dependentObservable(); Should be replaced with: self.typeList = ko.computed(function(){ // Compute value here... return value; });

Categories : Knockout Js

HighCharts : Basic column chart, but not showing correct colors in column
I came across some syntax errors, but as a result I reproduced your example here: http://jsfiddle.net/WVgDM/ Colors are as you described.

Categories : Highcharts

php to display table; column one with mysql column comments and column two transposed row results
I'd recommend you use the mysqli fetch_fields() function on the result set, to get information about the columns contained in the resultset, rather than querying views in INFORMATION_SCHEMA. The fetch_fields() will work for queries that reference only a subset of columns in a table, or queries that return expressions, or return columns from more than one table. http://php.net/manual/en/mysqli.quickstart.metadata.php

Categories : PHP

R- Perform operations on column and place result in a different column, with the operation specified by the output column's name
It seems strange that you would store your operations in your column names, but I suppose it is possible to achieve: As always, sample data helps. ## Creating some sample data mydf <- setNames(data.frame(matrix(1:9, ncol = 3)), c("L1", "L2", "L3")) ## The operation you want to do... morecols <- c( combn(names(mydf), 2, FUN=function(x) paste(x, collapse = "+")), combn(names(mydf), 2, FUN=function(x) paste(x, collapse = "-")) ) ## THE FINAL SAMPLE DATA mydf[, morecols] <- NA mydf # L1 L2 L3 L1+L2 L1+L3 L2+L3 L1-L2 L1-L3 L2-L3 # 1 1 4 7 NA NA NA NA NA NA # 2 2 5 8 NA NA NA NA NA NA # 3 3 6 9 NA NA NA NA NA NA One solution could be to use eval(parse(...)) within lapply to perform the calculati

Categories : R

Loop through Column A in Excel VBA, get a random word from Column B and output the row in Column C
Try: Sub HTH() Dim rCell As Range Dim iRandom As Integer For Each rCell In Range("A1:A20") iRandom = Application.WorksheetFunction.RandBetween(1, 5) rCell.Offset(, 2).Value = CStr(rCell.Value & Cells(iRandom, "B").Value) Next rCell End Sub As you have tagged Excel 2007 you may need to use this instead: iRandom = Round((5 - 1) * Rnd + 1, 0)

Categories : Excel

Two Column CSS - Right Column Fixed Width, Left Column ariable
floatting doesn't need funny negative margins ? or i didnot understand what you meant. Here's example with floatting display (inline-block was already proposed) display +reverse order (works with inline-block if you wish) : http://codepen.io/anon/pen/xnlGa You have display:flex as well , but not yet good enough implemented actually in my own opinion :)

Categories : HTML

Display DataGrid Column as Hyperlink Column depending on column value
1) Set the AutoGenerateColumns property of the datagrid to false. 2) Create a template column instead of a bound column for the status. 3) Set the 'DataField' property for every column (Except the template column) so they know which value to display from the sql datasource. 4) Edit the template column and add a html div in there with the id divStatus <asp:TemplateField HeaderText="Status"> <ItemTemplate> <div id="divStatus" runat="server"> </div> </ItemTemplate> </asp:TemplateField> Iterate through all the rows after setting the datasource of the gridview and do something like the following. for(int i = 0; i < dataSet.Tables[0].Rows.Count; i++) { HtmlGenericControl divStatus = (HtmlG

Categories : C#

How to extract SQL Server image column data into readable format and store into another column?
You can do it converting the field first to varbinary and than to varchar. declare @t table (i image) insert into @t values('some text') select i, CAST(cast(i as varbinary(max)) as varchar(max)) from @t

Categories : Sql Server

SQL Server error.Explicit value for Identity Column....can only be specified when a column list is used and IDENTITY_INSERT is ON
Try this one - CREATE PROCEDURE dbo.spInsertUserRegister ( @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @Username NVARCHAR(50), @Password NVARCHAR(50), @Designation NVARCHAR(50), @Department NVARCHAR(50), @IsAdmin BIT ) AS BEGIN INSERT INTO dbo.tblRegisterUser (FirstName, LastName, Username, [Password], Designation, Department, IsAdmin) SELECT @FirstName, @LastName, @Username, @Password, @Designation, @Department, @IsAdmin DECLARE @ID BIGINT SELECT @ID = SCOPE_IDENTITY() --SET IDENTITY_INSERT dbo.tblLogin ON; INSERT INTO dbo.tblLogin (UserName, [password], [uid], IsAdmin) SELECT @Username, @Password, @ID, IsAdmin FROM tblRegisterUser WHERE UserName = @Username AND [password] = @Password --SET IDENTITY

Categories : Sql Server

Within a SQL Server view - how to combine multiple column results into one column
My suggestion would be to UNPIVOT the data in Table2 so you can easily join on the data, then you can return the table1 description and the table2 id. The UNPIVOT portion of this query using CROSS APPLY: select col, value, t2.Id from table2 t2 cross apply ( select 'table1id1', table1id1 union all select 'table1id2', table1id2 union all select 'table1id3', table1id3 ) c (col, value); See SQL Fiddle with Demo. This gives a result: | COL | VALUE | ID | --------------------------- | table1id1 | 1 | 1 | | table1id2 | 2 | 1 | | table1id3 | 3 | 1 | | table1id1 | 2 | 2 | | table1id2 | 3 | 2 | | table1id3 | (null) | 2 | | table1id1 | 3 | 3 | Now that you have the data in rows, you can easily join on the value column to return the id: select

Categories : SQL

SQL Server: How to replace words in one column if/when the same word is found in another column?
I guess my question is "why keep the data in a list format after processing?". This format is not in normal form. I would create the following tables. raw_data ( user_id, food_list ) categories ( food_name, food_category ) processed_data ( user_id, food_category, food_cnt ) hospital_data ( user_id, unknown_name ) The raw_data table is the unprocessed data. I would use a string split function to parse the data into rows. Convert the data to same case and compare against food category. Any matches should be counted and saved in the processed_data(). Any left over names w/o match should be put in the hospital_data() table. These are miss spelled entries that need to be added to the category table. This solution would be a two pass run. 1 - Loads most of the data.

Categories : SQL

Second column defaulting to identity column SQL Server 2005
You can create a trigger that would set the default after insert: create trigger defaultGroupId on t after insert as update t set groupID = id where id in (select id from inserted); Here's a fiddle showing it in action: http://sqlfiddle.com/#!3/4e5b2/1

Categories : Sql Server

SQL Server : update column based on similar column
For these types of updates, I like to use an updatable CTE: with toupdate ( select t.*, max(prev_date) over (partition by item) as new_prev_date from t ) update toupdate set prev_date = new_prev_date where prev_date <> new_prev_date; -- optional `where` clause There are other approaches. Standard SQL would be: update t set prev_date = (select max(prev_date) from t t2 where t2.item = t.item);

Categories : Sql Server

Two column layout in twitter bootstrap with first column a fixed width, the second column width fills the rest of the space
You can try something like this: <div class="row-fluid"> <div class="span12"> <div class="fixed_width"></div> <div class="variable_width"></div> </div> </div> And CSS div.fixed_width { width: 300px; // or try with: "float:left;" too } div.variable_width { overflow: hidden; // if you use float:left in the other, don't use overflow here, but try with: "width:100%;" } Here you can find a more general example (http://jsfiddle.net/glee/6Cavr/)

Categories : CSS

Look at Column A, Match With Column E, Copy Column B to Column F
So you want to put the data in lines 1 to 8, column B to D, into line 1, column F and following, because for lines 1 to 8 the '11' in column A matches the '11' in E1? E.g. B1->F1, C1->H1 (this is the first position where I'm missing some order), D1->G1, ??->I1 (where does the content from column I come from?), (append the next source line in the same target line:) B2->J1, ... First of all you need to clarify where the data in cols F to I (and further) comes from. Then: If my assumption about your intention is right, then this brings up other questions: Does col E always contain all possible values of col A or might there be values (e.g. '14') in col A that are missing in col E? Are the positions of the values in col E fixed? Here are some suggestions, code might follow after clari

Categories : Excel

Append column's data to another column in SQL Server
How about: UPDATE MyTable SET Field2 = ISNULL(Field2, '') + ISNULL(Field1, '') To join the values into Field2. And UPDATE MyTable SET Field1 = NULL To clear out Field1 once you are sure the first script worked.

Categories : SQL

SQL server - How to copy data in a column to other column
You should use varchar(max) instead of text. The following adds the column and sets the value: alter table t add newcol varchar(max); update t set newcol = name;

Categories : SQL

SQL Server - update column based on another column value
How about something like UPDATE TableA SET Value = CASE Type WHEN 'DOG' then B.Dog WHEN 'CAT' then B.Cat WHEN 'RABBIT' then B.Rabbit ELSE Value END FROM TableB b SQL Fiddle DEMO

Categories : Sql Server

merge_oracle_ORA-01747: invalid user.table.column, table.column, or column specification
In Java Prepared statement you can use '?' only for column names in where clause. You are trying to extend this concept to other places, which is invalid. Here is the problematic section in your code: SELECT ? corpusid, ? corpus_name, ? corpusdesc Here is the Note from javadocs: Note: The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

Categories : Java

Conditional Computed Columns in SQL Server Views
IIF is a new feature in SQL Server 2012 (http://www.mssqltips.com/sqlservertip/2570/new-logical-functions-in-sql-server-2012-iif-and-choose/). You can't design in 2012 and expect it to work in 2008.

Categories : SQL

How to disable identity from the existing column & after adding data , how to enable identity again on the same column in SQL Server 2008?
Use SET IDENTITY_INSERT (http://msdn.microsoft.com/en-us/library/ms188059.aspx). SET IDENTITY_INSERT TableName ON; -- Insert Data. SET IDENTITY_INSERT TableName OFF;

Categories : Sql Server

Concatenating a column within the same column in SQL Server
My solution [I am not sure if I understand your problem well] user function create function inonerow (@orderid int) returns nvarchar(max) begin declare @data nvarchar(max) SELECT @data =coalesce(@data + ',','')+ CONVERT(varchar,prodid) from SalesOrder inner join product on product.prodid = salesorder.prodid where orderid=@orderid and product.oshold = 1 return @data end select distinct orderid, dbo.inonerow(orderid) from salesorder inner join product on product.prodid = salesorder.prodid where product.onhold = 1 Hope this works :)

Categories : SQL

Column A numbers matched with Column B numbers that are assigned to Column C =column D?
It is hard to guess what your data looks like, so assuming your data looks as below: Here is my solution, I think it is a simple VLOOKUP problem: Paste this =IF(ISERROR(VLOOKUP(C2,$A$2:$A$4,1,FALSE)),"","SoldToPlaceA") to E2 and drag it down to copy.

Categories : Excel

no column was specified for column [x] of [table] and invalid column name
Total guess, but try this alternate syntax. SELECT [MonthlyAvg] = AVG(Totals) from ( SELECT [DateOf] = [DATE] , [Totals] = SUM(Assets) FROM Assets inner join Funds on Assets.FundCode = Funds.FundCode WHERE FeeGroupID = 17 and (([Date] >= '1/1/2013') AND ([Date] <= '4/1/2013')) and ((Funds.EndDt >= '4/1/2013') OR (Funds.EndDt is null)) GROUP BY [DATE] ) as MonthlyTotals

Categories : SQL

Django South migrations fails with column does not exist, for a column that hasn't been introduced yet
The problem was that the auto-generated 0023 migration in the forwards function had the following in it: for a in Blog.objects.all(): a.uuid = u'' + str(uuid.uuid1().hex) a.save() that calls the model based on the latest content, so author_bio was in it. To fix it then call the model from the "orm" like so: for a in orm.Blog.objects.all(): a.uuid = u'' + str(uuid.uuid1().hex) a.save()

Categories : Misc

Select query to return records where column A has one to many relationship column B - ORA-00937 error
Move the HAVING clause to the sub-query and include the count also in the sub-query SELECT. Select SQ.contact_id from ( select a.contact_id ,count(a.location_account_id) from crm.asset_plus a where a.contact_id is not null group by a.contact_id having count(a.location_account_id) > 1 ) SQ If you need to include the count in your final output - then just use the sub-query be itself. Based on the comments below, here is a revised query, that counts the DISTICT values of the locatin_account_id Select SQ.contact_id from ( select a.contact_id ,count(distinct a.location_account_id) from crm.asset_plus a where a.contact_id is not null grou

Categories : SQL

JTable Column defaults with a data value on Load.Now , I need a combobox on the same Column with that default value already selected
Here is the Sample Code: public class Test extends JFrame { public void init() { JTable table = new JTable( new Object[][] { { "Paul J" , "20" }, { "Jerry M" , "30" }, { "Simon K" , "25" } }, new String[] { "Name" , "Age" } ); table.getColumnModel().getColumn(1).setCellEditor( new SampleCellEditor() ); getContentPane().add( new JScrollPane( table )); setSize( 400, 200 ); setVisible(true); } // Sample Editor to Show Combobox with all sample values in that column // also can edit the value to add new Value that is not in the column public static class SampleCellEditor extends DefaultCellEditor { public SampleCellEditor( ) { super( new JComboBox() ); } public Com

Categories : Java

@AttribueOverride(name="col1", column=@Column(nullable=true)) throwing cannot set field to null value
The column is nullable, but the field in your entity is a primitive type (int, long, or boolean, presumably). There's no way for Hibernate to store null into a primitive type field. It should be Integer or Long or Boolean instead. That's what the error message says: "Null value was assigned to a property of primitive type".

Categories : Java



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