How Can I Create Two Column with Math Function In one Column Like Below.
Create Table Tbl_V_Voucher_Details
(
Id Int IDentity (1,1) Primary Key,
Catid Int Foreign Key References Tbl_V_Voucher_Info(Id),
ItemId Int Foreign Key References Tbl_V_Item(Id),
Discription varchar
(100),
Qty Int,
Price Float,
Qty * Price As Total
)
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
We have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
Hi,I'm sure this is a common problem.. to create a single field from awhole column, where each row would be separated by a comma.I can do this for a specified table, and column.. and I've created afunction using VBA to achieve a more dynamic (and very slow) solution..so I would like to implement it using a user defined function in sql server.The problems I'm facing are, that I can't use dynamic sql in afunction.. and I also can't use temporary tables which could build up a'standard' table from parameters given to then perform the function on.So, with these limitations, what other options do I have?Cheers,Chris
View 1 Replies View RelatedHi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies View Relatedwhen creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View RelatedI have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
I am trying to add a column to query based on the value of another column in the query.
I first tried creating a calculated field in SSRS 2008 with this statement:
=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)
The report runs but I get a "#ERROR" when I place the field on the report.
I next tried creating a new column with the SQL statement:
SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =
Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'
[Code] .....
This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."
Can anyone assist me with a script that adds a new column to a table then inserts new values into the new column based on the Table below. i have included an explanation of what the script should do.
Column from
Parts Table Column from
MiniParts New Column in
(Table 1 ) (Table 2 ) MiniParts (Table2)
PartsNum
MiniPartsCL
NewMiniPartsCL
1
K
DK
1
K
K
1
Q
Q
0
L
L
0
L
LC
0
D
G
0
S
S
I have 2 tables in a database. Table 1 is Parts and Table 2 is MiniParts. I need a script that adds a new column in the MiniParts table. and then populate the new column (NewMinipartsCL) based on Values that exist in the PartsNum column in the Parts Table, and MiniPartsCL column in the MiniParts columns.
The new column is NewMiniPartsCL. The table above shows the values that the new column (NewMiniPartsCL) should contain.
For Example
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "DK" ( as shown in the table above).
Anytime you have "1" in the PartsNum column of the Parts Table and the MiniPartsCL column of the MiniParts Table has a "K" , the NewMiniPartsCL column in the MiniParts Table should be populated with "K" ( as shown in the table above). etc..
Hi AllI'm trying to find a math function (if it exists) in SQL Server. If itdoesnt exist, then maybe someone can tell me what its called so I cando a bit more reading on itBasically I want to do this:Parameter Components1 12 23 1, 24 45 1, 46 2, 47 3, 48 89 1, 8and so onI'd like to be able to call a function and it would return true orfalse like sofunctionname(1, 9) = trueso 1 is a component of 9functionname(2, 9) = falseso 2 is not a component of 9functionname(4, 5) = trueso 4 is a component of 5If anyone could tell me if it exists in C#, VB.NET, VB6 or VBScript,I'd appreciate it!Thanks in advanceSam
View 2 Replies View RelatedFor example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
I need to periodically send a set of data to a vendor. They need to have a column what is an unique identifyer but they cannot handle 16 digit GUI.So I have this my_old_person table with person_GUI as uniqueidentifier. It got 200K rows in it. I am thinking creating a staging utility table:
Create table my_new_person
(person_GUI varchar(255) not null,<--this will contains the same GUI from the old
Person_ID nvarchar(20))<--send tis to vendor
At the beginning of my nightly ETL, I will run this to get the new data:
Insert into my_new_person
select a.person_GUI, xyz
from my_old_person a join my_new_person b
on a.person_GUI=b.person_GUI
where not exists (Select person_GUI from my_old_person)
How do I implement this person_ID to make it unique and populate correctly in above xyz?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Hello FriendsSuppose I'm designing one database table- ABC master and it has fields like field1, field2........field5. From these fields, Field1 is PK and 2 & 3 and mandatory. So while creating this table, is it necessary to specify the field 2 & 3 as NotNull in Null column.If I specify them as Null then does it effect aby thing. As i know that before saving the data to the database the programming logic checks whether the mandatory data entered or not?Reply back..Thanks
View 3 Replies View RelatedI have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.
An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.
Thoughts?
I have a Member table and I have a MemberView.
I want the view to return the "count" from a complex query related to the Member record and I'm looking for a good way to do that.
I was thinking:
select M.*, (select count(*) from UDF)
but that just smells bad.
Any pointers would be great.
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
I have a ipaddress column is there where i need to split the column into two columns because of values like below
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
I have written the below query but it will throuh some error.
select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?
View 3 Replies View RelatedI am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
View 1 Replies View RelatedI have a report requirement, i am new to reports.
I want to have two lines of column headers.( with 7 columns in parent columns header)
and 7 columns in child column header.
The first column header will show a parent record.
And after the parent record i want to show the next child related horizontal column headers and will show all child record related to the above parent record.
its a one to many: 1 parent record and below all child records for that parent record.
can i use subreports controls to show all child records of the parent.
Thank you very much for the information.
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
How to Change Order of Column In Database Tables
View 10 Replies View RelatedWith Sql Server Management Studio, while creating/modifying a table I want to specify one of its columns to store values in lower case only. Can it be done through the designer or by some other means?
N.B.: Using SQL Server 2005.
I created composite clustered index key based on Gender and Salary column
The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>
got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?
And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20)
I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?
Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?
Does this make sense? Surely other foplks have run into this....
My thanks!
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
I am sharing one sql query and o/p:
select distinct case
when LastStatusMessageIDName = 'Program completed with success' then 'Office 2013 SP1 Installed Successfully'
when LastExecutionResult = '2013' then 'Machine Does not have Office 2013'
when LastExecutionResult = '17023' then 'User cancelled installation'
when LastExecutionResult = '17302' then 'Application failed due to low disk space.'
[Code] .....
The below is the output for the given query,here i want to see only one comment value in my list and the count is also sum of all where comment should be Application will be installed once machine is online(Bold columns o/p)
Comment Machine Name
Application will be Installed once machine is Online 4
Application will be Installed once machine is Online 12
Application will be Installed once machine is Online 42
Application will be Installed once machine is Online 120
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I need o/p like below:in single line
Application will be Installed once machine is Online 178
Machine Does not have Office 2013 25
User cancelled installation 32
Application failed due to low disk space 41
Office 2013 SP1 already Exist 60
I am querying a tableA with 1.8 million rows, it has id as its primary key and is a clustered index. I need to select all rows where I order by lastname. Its taking me 45 seconds. Is there anything i can do to optimize the query.Will creating a fulltext index on lastname If so, can you give me an example on how to create a full text index on lastname?
[Project1].[Id] AS [Id],
[Project1].[DirectoryId] AS [DirectoryId],
[Project1].[SPI] AS [SPI],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[NPI] AS [NPI],
[Project1].[AddressLine1] AS [AddressLine1],
[Project1].[AddressLine2] AS [AddressLine2],
[code]...
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 Replies View RelatedI have a function say func()
create function func()
(
select p1 as Price , m1 as Market, [Price]*[Market] from tableP1, tableM1
}
Now i want to add one column into it whose value is multiple of Price*Market. How can i achieve this. function is not allowing me to do this. I don't want to calculate Price and market again for the third column as while calculating it requires many Case statements.
I finally got my UDF working, but now I'm faced with another problem. I can't seem to use it for anything![:I
First, here is the function itself:
CREATE FUNCTION dbo.fnDueDate
(
@DOP AS DATETIME,
@INTERVAL AS TINYINT,
@ThisDate DATETIME
)
RETURNS DATETIME
BEGIN
DECLARE@Temp DATETIME
IF @dop <= @ThisDate +(DATEADD(month,1,@dop))
SELECT@Temp = DATEADD(month, @Interval, @dop)
ELSE
SELECT@Temp = DATEADD(month, (@Interval - DATEDIFF(month, @dop, @ThisDate) % @Interval) % @Interval + DATEDIFF(month, @dop, @ThisDate), @dop)
RETURN @Temp
END
It is called using something like this
SELECT dbo.fnDueDate('20060315', 3, GETDATE())
This function executes(correct term?) with three parameters, DOP, Interval, and ThisDate, which correspond to a column from a table (mytable.dop), an interval in months, and ThisDate (GETDATE())
Now, I have determinied that the function works great, after several posts and a lot of help from our SQLteam gurus/yaks.
However, I can't seem to use it in a stored procedure.
Here is some sample data from myTable
ResidentsID Last First DOP
1 Jones Bridget 09/08/2004
2 Longstocking Pippi 08/01/2006
etc.
What I would like to do is generate this:
Last First DOP Next3month Next6Month
Jones Bridget 09/08/2004 12/08/2006 3/08/2006
Longstocking Pippi 08/01/2006 11/01/2006 5/01/2006
Using the data from each row for DOP
Help. Please!
Stephen