Problem in inserting value in view

Hi There,

i have created one view on an exsisting table, but when i am inserting value in it, it is giving the following error msg:

Derived table 'View Name' is not updatable because a column of the derived table is derived or constant.

Thanx

Is this related to SSIS? Well the error is coming straight from SQL Server itself, and is fairly clear to me.

You cannot update a column that is not really a column. So what columns are being updated and can you honestly say they are unadulterated columns, and have not been derived or manipulated in any way before being returned from the view.?

If you want a specific answer then post the UPDATE statement and a the DDL (CREATE VIEW...) for the view.

problem in Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.

From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.

HTH!

sql

problem in connectionstring

when I write this code in web.config :

<configuration><appsettings><addkey="constring" VALUE="WORKSTATION id=I-85096B7FC8F64;packet catalog='test"/' info="True;initial" security="SSPI;data" source="I-85096B7FC8F64;persist" size="4096;integrated"><system.web>

and write this statements in the code of program :

string Constring = ConfigurationSettings.AppSettings("constring"); SqlConnection conn = new SqlConnection(constring);

this error is displyed:

The name 'constring' does not exist in the class or namespace 'C_sharp1.WebForm1'

In C#, instead of:
ConfigurationSettings.AppSettings("constring")
use square brackets:
ConfigurationSettings.AppSettings["constring"]|||

thanks a lot.

the problem is solved by your solution.

Problem Getting COUNT() to return the value Im after

Hello. I have a database with the following pivot table which has one record linking each employee in the company to the offices they work at. I'm using a pivot table instead of a direct reference because some employees work at more than one office.

Table Def: tblOfficePivot
--------
key <- PK ID
officeLink <- Link to office record
employeeLink <- link to employee record.

I want to write a select I can use to return the total number of offices with an emplyee population between X and Y (eg. 1-250, 251-500, 501-1000, etc.)

I can write a query which return one result for each office that falls into the range of employees I define. This query looks like this:

SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250

The above query returns 1 record for each office with between 1 and 250 employees - the result being the employee count. So, if 2 offices fell into this category, A & B, having 50 and 123 employees respectively, the result set would look Like this:

theTotal
---
1. 50
2. 123

What I want instead is the total number of offices, in this case 2.

Is there a way to do this without the COMPUTE clause?

Thanks for any suggestions, this one is driving me crazy.I'd use:SELECT Count(*)
FROM (SELECT COUNT(officeLink) AS theTotal
FROM tblOfficePivot
GROUP BY officeLink
HAVING COUNT(*) BETWEEN 1 AND 250
) AS a-PatP|||Thanks Pat, that worked a charm. Hadn't written a query in that format until now. Definitely something that will come in handy in the future.

Regards, Matt|||What a bright boy

USE Northwind
GO

SELECT Count(*)
FROM (SELECT CustomerId, COUNT(CustomerId)TotalPerId, count(*) AS theTotal
FROM Orders
GROUP BY CustomerId
HAVING COUNT(*) BETWEEN 1 AND 10
) AS a

Problem formatting currency to different decimal places

I'm trying to format a field value to currency on a SQL report.

I need to allow for 4 decimal places to the right of the decimal if the field value contains those digits. (ie. $6.8484). Most of the time I will not have 4 places to the right of the decimal and would like to format according to the amout of decimal places. For example, If the value is 1 dollar, I don't want to format as $1.0000. I need a way to format the values according to the amount of digits to the right of the decimal. (ie. 1 dollar = $1.00, 3.453 = $3.453, 2.4453 = $2.4453)

Is there an easy way to do this using C2,C3, and C4? Please help.

When you say "SQL report", I suppose you mean you are using SQL Server Reporting Services.

In that case, you can just set the Format property of the textbox where you display the numeric field value to C4.

Note: if the query returns the value as string (instead of a numeric value), you will need to explicitly convert the value into its numeric representation first. You can do this in the query or directly in the report textbox by using the CDbl() function to convert a string to a double, e.g. =CDbl(Fields!SomeString.Value).

-- Robert

|||The C4 format will work for the four decimal place values but how do handle the three and two decimal situations. The report values should not display as $4.4200 if the initial value is $4.42. This also applies to three digit values( $4.421 should not display as $4.4210 on the report).|||I'm not sure if there are any standard functions to determine how many decimal places the value has (if not, you can write a custom function to do it). Suppose we have such a function called GetDecimalPlaces, you can set the format property to =IIF(GetDecimalPlaces(<fieldvalue>) <= 2, C2, GetDecimalPlaces(<fieldValue>) = 3, "C3", "C4").

Problem finding values with aggregate functions

Hi all!

In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?

CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */

insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)

select min(row_value), row_name from some_table group by row_nameJon wrote:
> Hi all!
> In a statement I want to find the IDENTITY-column value for a row
that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
> CREATE TABLE some_table
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> row_value integer,
> row_name varchar(30)
> )
> GO
> /* DROP TABLE some_table */
> insert into some_table (row_name, row_value) VALUES ('Alice', 0)
> insert into some_table (row_name, row_value) VALUES ('Alice', 1)
> insert into some_table (row_name, row_value) VALUES ('Alice', 2)
> insert into some_table (row_name, row_value) VALUES ('Alice', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 2)
> insert into some_table (row_name, row_value) VALUES ('Bob', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 4)
> insert into some_table (row_name, row_value) VALUES ('Celine', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 6)
> select min(row_value), row_name from some_table group by row_name

*Assuming* that row_name/row_value combinations are unique, then it
would be:

select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name

*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())|||Jon (jonsjostedt@.hotmail.com) writes:
> In a statement I want to find the IDENTITY-column value for a row that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?

Yes:

select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name

What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem dynamicly populating a checkboxlist from a query

I have the follow, i get the right amout of checkboxes but they all have the same value(System.Data.Common.DbDataRecord)

Dim objconnAsNew SqlConnection(connstring_MPR)Dim objcmdAs SqlCommand =New SqlCommand("SELECT [Parts Master Table].COMMD_CODE as comcode FROM [Parts Master Table] INNER JOIN [Warehouse balance table] ON [Parts Master Table].PART_NUMBER = [Warehouse balance table].PART INNER JOIN POREPORT ON [Parts Master Table].PART_NUMBER = POREPORT.[Part Number] INNER JOIN [DEMAND TABLE] ON [Parts Master Table].PART_NUMBER = [DEMAND TABLE].PART WHERE POREPORT.[PO Bal] > 0 OR [DEMAND TABLE].QTY > 0 or [Warehouse balance table].ONHAND > 0 and [Parts Master Table].M_B = 1 AND [Warehouse balance table].WHSE = 'sgr' AND ([Parts Master Table].FAMILY NOT LIKE 'lam%' or [Parts Master Table].FAMILY NOT IN ('ULTCH', 'REMOT', 'MKSIN')) GROUP BY [Parts Master Table].COMMD_CODE ORDER BY [Parts Master Table].COMMD_CODE", objconn)

objconn.Open()

chkComCode.DataSource = objcmd.ExecuteReader(CommandBehavior.CloseConnection)

chkComCode.DataBind()

objconn.Close()

First thing is, run your query in Query Analyzer and see it returns the same value(comcode) for each row. And secondly set DataText field and DataValue field. [even though it is not necessary for your query]

chkComCode.DataTextField="comcode"
chkComCode.DataValueField="comcode"

|||

yep i had to go into source and set the textfield/value field to = comcode

Problem creating Data-Driven Subscription

Using SS2005, I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. Where it says specify a command, I put in exec sp_ReportMaster. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

I don't know why it shows parameters in Reporting Services when I run the report from there, but when I try to create the subscription, it shows that I don't have any parameters.

Thanks, Iris

I figured out my problem.

Thanks, Iris

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable