I want to make a report which is using parameter field of a single date. But the same time I have some fields those have to show some data out of the parameter field. And the grouping is same.
For example, a coloum show data of the date which is slected but another field have to show data of the current month but Less and equal to the selected field.
ThankyouYour best bet is to create one as a subreport to the other. These 2 reports link by the parameter field, but use different filter.
That's all I can say with the info you provide.
Good luck.
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Sunday, March 11, 2012
Data not getting plotted in line graph
Hi,
I have a LINE chart with 2 data fields, and 1 category field.
I havent included any series field in the chart.
If the query returns more than 1 records then both the data field values are
getting plotted in the graph.
If the query returns only one record then only the first data point is
plotted. I dont know why the value for second data field is not plotted...
Is it a bug in reporting services?
Please any one reply me asap... I have to fix it for the client by today...
Thanks for help...
Regards,
RanjithTo add more information,
if i change the chart type to any other chart other then LINE graph, it is
getting plotted. Only for Line graph its not working...
Thanks,
Ranjith
"Ranjith Prakash" wrote:
> Hi,
> I have a LINE chart with 2 data fields, and 1 category field.
> I havent included any series field in the chart.
> If the query returns more than 1 records then both the data field values are
> getting plotted in the graph.
> If the query returns only one record then only the first data point is
> plotted. I dont know why the value for second data field is not plotted...
> Is it a bug in reporting services?
> Please any one reply me asap... I have to fix it for the client by today...
> Thanks for help...
> Regards,
> Ranjith
>
>|||Please read this recent thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
It sounds like you have a very similar situation. You can either request the
SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
wait till the official release of SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> To add more information,
> if i change the chart type to any other chart other then LINE graph, it is
> getting plotted. Only for Line graph its not working...
> Thanks,
> Ranjith
> "Ranjith Prakash" wrote:
> > Hi,
> >
> > I have a LINE chart with 2 data fields, and 1 category field.
> > I havent included any series field in the chart.
> >
> > If the query returns more than 1 records then both the data field values
are
> > getting plotted in the graph.
> >
> > If the query returns only one record then only the first data point is
> > plotted. I dont know why the value for second data field is not
plotted...
> >
> > Is it a bug in reporting services?
> > Please any one reply me asap... I have to fix it for the client by
today...
> >
> > Thanks for help...
> >
> > Regards,
> > Ranjith
> >
> >
> >|||Hi Robert,
Thanks a lot for helping me. That did fix my bug.
Thanks again,
Ranjith
"Robert Bruckner [MSFT]" wrote:
> Please read this recent thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
> It sounds like you have a very similar situation. You can either request the
> SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
> wait till the official release of SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
> message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> > To add more information,
> >
> > if i change the chart type to any other chart other then LINE graph, it is
> > getting plotted. Only for Line graph its not working...
> >
> > Thanks,
> > Ranjith
> >
> > "Ranjith Prakash" wrote:
> >
> > > Hi,
> > >
> > > I have a LINE chart with 2 data fields, and 1 category field.
> > > I havent included any series field in the chart.
> > >
> > > If the query returns more than 1 records then both the data field values
> are
> > > getting plotted in the graph.
> > >
> > > If the query returns only one record then only the first data point is
> > > plotted. I dont know why the value for second data field is not
> plotted...
> > >
> > > Is it a bug in reporting services?
> > > Please any one reply me asap... I have to fix it for the client by
> today...
> > >
> > > Thanks for help...
> > >
> > > Regards,
> > > Ranjith
> > >
> > >
> > >
>
>
I have a LINE chart with 2 data fields, and 1 category field.
I havent included any series field in the chart.
If the query returns more than 1 records then both the data field values are
getting plotted in the graph.
If the query returns only one record then only the first data point is
plotted. I dont know why the value for second data field is not plotted...
Is it a bug in reporting services?
Please any one reply me asap... I have to fix it for the client by today...
Thanks for help...
Regards,
RanjithTo add more information,
if i change the chart type to any other chart other then LINE graph, it is
getting plotted. Only for Line graph its not working...
Thanks,
Ranjith
"Ranjith Prakash" wrote:
> Hi,
> I have a LINE chart with 2 data fields, and 1 category field.
> I havent included any series field in the chart.
> If the query returns more than 1 records then both the data field values are
> getting plotted in the graph.
> If the query returns only one record then only the first data point is
> plotted. I dont know why the value for second data field is not plotted...
> Is it a bug in reporting services?
> Please any one reply me asap... I have to fix it for the client by today...
> Thanks for help...
> Regards,
> Ranjith
>
>|||Please read this recent thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
It sounds like you have a very similar situation. You can either request the
SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
wait till the official release of SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> To add more information,
> if i change the chart type to any other chart other then LINE graph, it is
> getting plotted. Only for Line graph its not working...
> Thanks,
> Ranjith
> "Ranjith Prakash" wrote:
> > Hi,
> >
> > I have a LINE chart with 2 data fields, and 1 category field.
> > I havent included any series field in the chart.
> >
> > If the query returns more than 1 records then both the data field values
are
> > getting plotted in the graph.
> >
> > If the query returns only one record then only the first data point is
> > plotted. I dont know why the value for second data field is not
plotted...
> >
> > Is it a bug in reporting services?
> > Please any one reply me asap... I have to fix it for the client by
today...
> >
> > Thanks for help...
> >
> > Regards,
> > Ranjith
> >
> >
> >|||Hi Robert,
Thanks a lot for helping me. That did fix my bug.
Thanks again,
Ranjith
"Robert Bruckner [MSFT]" wrote:
> Please read this recent thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
> It sounds like you have a very similar situation. You can either request the
> SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
> wait till the official release of SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
> message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> > To add more information,
> >
> > if i change the chart type to any other chart other then LINE graph, it is
> > getting plotted. Only for Line graph its not working...
> >
> > Thanks,
> > Ranjith
> >
> > "Ranjith Prakash" wrote:
> >
> > > Hi,
> > >
> > > I have a LINE chart with 2 data fields, and 1 category field.
> > > I havent included any series field in the chart.
> > >
> > > If the query returns more than 1 records then both the data field values
> are
> > > getting plotted in the graph.
> > >
> > > If the query returns only one record then only the first data point is
> > > plotted. I dont know why the value for second data field is not
> plotted...
> > >
> > > Is it a bug in reporting services?
> > > Please any one reply me asap... I have to fix it for the client by
> today...
> > >
> > > Thanks for help...
> > >
> > > Regards,
> > > Ranjith
> > >
> > >
> > >
>
>
Data modelling question
Using SQL 7. I have a table with 2 fields in it that I wish to relate
to a lookup table. DDL for table 1:
to a lookup table. DDL for table 1:
CREATE TABLE [dbo].[tblPedometerReadings] (
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReadingDate] [datetime] NULL ,
[PedometerReading] [smallint] NULL ,
[OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity1Minutes] [smallint] NULL ,
[OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OtherActivity2Minutes] [smallint] NULL
) ON [PRIMARY]
DDL for table 2:
CREATE TABLE [dbo].[tlkpOtherActivities] (
[OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[EquivalentSteps] [smallint] NOT NULL
) ON [PRIMARY]
In my database diagram, I want to create a 1-to-many relationship
between tblPedometerReadings.OtherActivity1 and
tlkpOtherActivities.OtherActivity AND between
tblPedometerReadings.OtherActivity2 and
tlkpOtherActivities.OtherActivitiy. I get a long error message when
attempting the relationship between OtherActivity2 and OtherActivity.
Is there another schema that would work better?
Thanks.(manning_news@.hotmail.com) writes:
> Using SQL 7. I have a table with 2 fields in it that I wish to relate
> to a lookup table. DDL for table 1:
>
> CREATE TABLE [dbo].[tblPedometerReadings] (
> [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReadingDate] [datetime] NULL ,
> [PedometerReading] [smallint] NULL ,
> [OtherActivity1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity1Minutes] [smallint] NULL ,
> [OtherActivity2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [OtherActivity2Minutes] [smallint] NULL
> ) ON [PRIMARY]
> DDL for table 2:
> CREATE TABLE [dbo].[tlkpOtherActivities] (
> [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [EquivalentSteps] [smallint] NOT NULL
> ) ON [PRIMARY]
Which version of SQL Server are you really using? The above scripts
does not work in SQL7 - I can tell that from the use of COLLATE
clauses.
> In my database diagram, I want to create a 1-to-many relationship
> between tblPedometerReadings.OtherActivity1 and
> tlkpOtherActivities.OtherActivity AND between
> tblPedometerReadings.OtherActivity2 and
> tlkpOtherActivities.OtherActivitiy. I get a long error message when
> attempting the relationship between OtherActivity2 and OtherActivity.
> Is there another schema that would work better?
Designwise it sounds OK (save that varchar(50) is a tad long for a
key value). If I understand you right, you get this error message
when using the diagram functionality in Enterprise Manager? I don't
use that tool, but it would somewhat easier to say anything useful,
if you could post the error message.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Using SQL2000.
Here's the error message:
- Unable to create relationship
'FK_tblPedometerReadings_tlkpOtherActivities1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
on table 'tblPedometerReadings' may cause cycles or multiple cascade
paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
other FOREIGN KEY constraints.
OtherActivity can be "Biking-Hard", "Swimming-Easy", things like
that... If the customer doesn't make it clear what data will be
entered, then I leave it at varchar(50) until I get a better idea. I'm
toying with the idea of assigning an ID # to the activity and making
that the key.
Is a reflexive relationship what I need here?
Erland Sommarskog wrote:
> (manning_news@.hotmail.com) writes:
> > Using SQL 7. I have a table with 2 fields in it that I wish to
relate
> > to a lookup table. DDL for table 1:
> > CREATE TABLE [dbo].[tblPedometerReadings] (
> > [ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [ReadingDate] [datetime] NULL ,
> > [PedometerReading] [smallint] NULL ,
> > [OtherActivity1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity1Minutes] [smallint] NULL ,
> > [OtherActivity2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [OtherActivity2Minutes] [smallint] NULL
> > ) ON [PRIMARY]
> > DDL for table 2:
> > CREATE TABLE [dbo].[tlkpOtherActivities] (
> > [OtherActivity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [EquivalentSteps] [smallint] NOT NULL
> > ) ON [PRIMARY]
> Which version of SQL Server are you really using? The above scripts
> does not work in SQL7 - I can tell that from the use of COLLATE
> clauses.
> > In my database diagram, I want to create a 1-to-many relationship
> > between tblPedometerReadings.OtherActivity1 and
> > tlkpOtherActivities.OtherActivity AND between
> > tblPedometerReadings.OtherActivity2 and
> > tlkpOtherActivities.OtherActivitiy. I get a long error message
when
> > attempting the relationship between OtherActivity2 and
OtherActivity.
> > Is there another schema that would work better?
> Designwise it sounds OK (save that varchar(50) is a tad long for a
> key value). If I understand you right, you get this error message
> when using the diagram functionality in Enterprise Manager? I don't
> use that tool, but it would somewhat easier to say anything useful,
> if you could post the error message.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(manning_news@.hotmail.com) writes:
> Using SQL2000.
> Here's the error message:
> - Unable to create relationship
> 'FK_tblPedometerReadings_tlkpOtherActivities1'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
> FOREIGN KEY constraint 'FK_tblPedometerReadings_tlkpOtherActivities1'
> on table 'tblPedometerReadings' may cause cycles or multiple cascade
> paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
> other FOREIGN KEY constraints.
You never said that you had chosen cascading delete/update for the
relationship. You will be able to define the constraint, if you select
NO ACTION. (Which means that an attempt to delete a referenced row
in tlkpOtherActivities will result in an error.)
While SQL 2000 supports DELETE/UPDATE ON CASCADE on foreign keys,
there are many restrictions. In some cases they are necessary, in
some cases the SQL Server developers were a bit on the conservative
side when they disallow cascading.
I don't use cascading DRI myself, so I have not dug into the details.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 19, 2012
data import using wizard
Hello,
I'm having problem importing a simple comma delimited text file into a
simple 3 fields table. I used the DTS wizards, I got to pick my source text
file, but on the following step, it gave me the following error message.
"Invalid delimited data text qualifier, must be followed by a column
delimiter",
I don't understanding what it means, my text file looks fine, is a simple 3
columns text file. Please help this dumb beginner.
Any good beginner book on SQL 2000 I should read'
Thanks in advance for any helpFor sql server - SQL SERVER IN 21 DAYS (BASICS)
FOR dts basics - step by step by Microsoft
Regards
R.D
"SQLbeginner" wrote:
> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||I mean SQL SERVER DTS - STEP BY STEP( see if possible, google it for free
soft copy)
Regards
R.D
"SQLbeginner" wrote:
> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||>> I don't understanding what it means, my text file looks fine, is a simple
Perhaps you might want to verify the input data, since the error message
points to a badly formatted source file. If the file size is smaller,
consider loading into a spreadsheet and see if it looks OK.
For beginners, SQL Server Books Online trumps anything out there.
Anith|||Thanks guys!
I finally figured out something though. Following is a small sample of my
text file that created from Excel using the (csv comma delimited) option:
"12345","Smith","Jones"
"45678","Lewis", "Shank"
On the DTS 'Select File Format' page, it has the following:
File type: ANSI
Row Delimiter: {CR} {LF}
Text Qualifer: Double Quote {"}
But it does not recognize the double quote as the Text Qualifer, if worked
beautifully when I stripped out the double quote. Is there a way to get
around that because 90% of the files I receive have the double quote'
Thanks!!
"Anith Sen" wrote:
> Perhaps you might want to verify the input data, since the error message
> points to a badly formatted source file. If the file size is smaller,
> consider loading into a spreadsheet and see if it looks OK.
>
> For beginners, SQL Server Books Online trumps anything out there.
> --
> Anith
>
>|||You could write a simple asp page where you can browse to the text file
(using file system object) you receive. Then write a loop that reads each
line and loads it into a temp variable and uses the replace function to
replace all quotes with empty space ("").
Or you can get the people who send you the text file to get their shit
straight and stop inserting double quotes. But the business usually doesn't
work with the techies, they usually want you to work around their horse shit
.
Anyways, the code for that should be simple. If you need help, look up code
on the File system object, opening/reading a text file line by line, and the
replace function.
RG
SQLbeginner wrote:
>Thanks guys!
>I finally figured out something though. Following is a small sample of my
>text file that created from Excel using the (csv comma delimited) option:
>"12345","Smith","Jones"
>"45678","Lewis", "Shank"
>On the DTS 'Select File Format' page, it has the following:
>File type: ANSI
>Row Delimiter: {CR} {LF}
>Text Qualifer: Double Quote {"}
>But it does not recognize the double quote as the Text Qualifer, if worked
>beautifully when I stripped out the double quote. Is there a way to get
>around that because 90% of the files I receive have the double quote'
>Thanks!!
>
>
>[quoted text clipped - 6 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1|||There's a space after the comma after Lewis.
Bruce Dunwiddie
http://www.csvreader.com|||That was my typo, there should not be a space anywhere.
"shriop" wrote:
> There's a space after the comma after Lewis.
> Bruce Dunwiddie
> http://www.csvreader.com
>
I'm having problem importing a simple comma delimited text file into a
simple 3 fields table. I used the DTS wizards, I got to pick my source text
file, but on the following step, it gave me the following error message.
"Invalid delimited data text qualifier, must be followed by a column
delimiter",
I don't understanding what it means, my text file looks fine, is a simple 3
columns text file. Please help this dumb beginner.
Any good beginner book on SQL 2000 I should read'
Thanks in advance for any helpFor sql server - SQL SERVER IN 21 DAYS (BASICS)
FOR dts basics - step by step by Microsoft
Regards
R.D
"SQLbeginner" wrote:
> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||I mean SQL SERVER DTS - STEP BY STEP( see if possible, google it for free
soft copy)
Regards
R.D
"SQLbeginner" wrote:
> Hello,
> I'm having problem importing a simple comma delimited text file into a
> simple 3 fields table. I used the DTS wizards, I got to pick my source te
xt
> file, but on the following step, it gave me the following error message.
> "Invalid delimited data text qualifier, must be followed by a column
> delimiter",
> I don't understanding what it means, my text file looks fine, is a simple
3
> columns text file. Please help this dumb beginner.
> Any good beginner book on SQL 2000 I should read'
> Thanks in advance for any help|||>> I don't understanding what it means, my text file looks fine, is a simple
Perhaps you might want to verify the input data, since the error message
points to a badly formatted source file. If the file size is smaller,
consider loading into a spreadsheet and see if it looks OK.
For beginners, SQL Server Books Online trumps anything out there.
Anith|||Thanks guys!
I finally figured out something though. Following is a small sample of my
text file that created from Excel using the (csv comma delimited) option:
"12345","Smith","Jones"
"45678","Lewis", "Shank"
On the DTS 'Select File Format' page, it has the following:
File type: ANSI
Row Delimiter: {CR} {LF}
Text Qualifer: Double Quote {"}
But it does not recognize the double quote as the Text Qualifer, if worked
beautifully when I stripped out the double quote. Is there a way to get
around that because 90% of the files I receive have the double quote'
Thanks!!
"Anith Sen" wrote:
> Perhaps you might want to verify the input data, since the error message
> points to a badly formatted source file. If the file size is smaller,
> consider loading into a spreadsheet and see if it looks OK.
>
> For beginners, SQL Server Books Online trumps anything out there.
> --
> Anith
>
>|||You could write a simple asp page where you can browse to the text file
(using file system object) you receive. Then write a loop that reads each
line and loads it into a temp variable and uses the replace function to
replace all quotes with empty space ("").
Or you can get the people who send you the text file to get their shit
straight and stop inserting double quotes. But the business usually doesn't
work with the techies, they usually want you to work around their horse shit
.
Anyways, the code for that should be simple. If you need help, look up code
on the File system object, opening/reading a text file line by line, and the
replace function.
RG
SQLbeginner wrote:
>Thanks guys!
>I finally figured out something though. Following is a small sample of my
>text file that created from Excel using the (csv comma delimited) option:
>"12345","Smith","Jones"
>"45678","Lewis", "Shank"
>On the DTS 'Select File Format' page, it has the following:
>File type: ANSI
>Row Delimiter: {CR} {LF}
>Text Qualifer: Double Quote {"}
>But it does not recognize the double quote as the Text Qualifer, if worked
>beautifully when I stripped out the double quote. Is there a way to get
>around that because 90% of the files I receive have the double quote'
>Thanks!!
>
>
>[quoted text clipped - 6 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1|||There's a space after the comma after Lewis.
Bruce Dunwiddie
http://www.csvreader.com|||That was my typo, there should not be a space anywhere.
"shriop" wrote:
> There's a space after the comma after Lewis.
> Bruce Dunwiddie
> http://www.csvreader.com
>
Friday, February 17, 2012
Data Getting truncated as I insert i.e. instead of inserting Hello it inserts H
m inserting some data in big-sized fieldand small-sized fields, data of varchar type, int's, dateTime, andothers... i am using something called a stored procedure to add datainto a table.. now when i execute the stored procedure my data getstruncated (although i made the sizes for my fields ridiculously biglike 1000 just in case) for example if i want to enter Jasmine it onlyinserts 'J' in the field
I am sure there's something wrong in the stored procedure, and I amguessing it's a problem of using single vs. double quotes and stufflike that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@.lName varchar,
@.fName varchar,
@.mName varchar,
@.title varchar,
@.craft varchar,
@.lastFour varchar,
@.SSN varchar,
@.dateOfHire varchar,
@.currentProj varchar,
@.status tinyInt,
@.project_id int,
@.updateBy varchar,
@.updateDate varchar,
@.address varchar,
@.email varchar,
@.phone varchar,
@.zip varchar,
@.myfeedBack varchar,
@.ethnicity varchar,
@.userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @.newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,lastFour, SSN, dateOfHire, currentProj, status, project_id,updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@.lName , @.fName , @.mName , @.title , @.craft , @.lastFour , @.SSN ,@.dateOfHire , @.currentProj ,
@.status, @.project_id , @.UpdateBy, @.UpdateDate , @.address , @.email , @.phone , @.zip , @.ethnicity )
SELECT @.newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@.newid ,+'
+@.myfeedBack + ',
+@.userID ,
@.project_id )
SET NOCOUNT ON
END
GO
When i use 2 single quotes it insert the following string: "@.lName" not the actual value of the variable @.lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman','1234','keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==','6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/20052:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345','123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
All of your parameters are declared like this:
@.lName varchar,
Should be like this:
@.lName varchar(1000),
a type of varchar is like varchar(1), meaning a variable length string with a max of 1 character. The number used should likely match the underlying length of the field in the table
I am sure there's something wrong in the stored procedure, and I amguessing it's a problem of using single vs. double quotes and stufflike that within my insert statement...
the following is my stored procedure:
CREATE procedure addLabor
@.lName varchar,
@.fName varchar,
@.mName varchar,
@.title varchar,
@.craft varchar,
@.lastFour varchar,
@.SSN varchar,
@.dateOfHire varchar,
@.currentProj varchar,
@.status tinyInt,
@.project_id int,
@.updateBy varchar,
@.updateDate varchar,
@.address varchar,
@.email varchar,
@.phone varchar,
@.zip varchar,
@.myfeedBack varchar,
@.ethnicity varchar,
@.userID int
as
BEGIN
SET NOCOUNT OFF
DECLARE @.newid INT
insert into laborPersonal ( lName, fName, mName, title, craft,lastFour, SSN, dateOfHire, currentProj, status, project_id,updateBy,
updateDate, address, email, phone, zip, ethnicity)
VALUES
(@.lName , @.fName , @.mName , @.title , @.craft , @.lastFour , @.SSN ,@.dateOfHire , @.currentProj ,
@.status, @.project_id , @.UpdateBy, @.UpdateDate , @.address , @.email , @.phone , @.zip , @.ethnicity )
SELECT @.newid = SCOPE_IDENTITY()
insert into feedBack
(lID, feedBack, userID, project_id)
values
(@.newid ,+'
+@.myfeedBack + ',
+@.userID ,
@.project_id )
SET NOCOUNT ON
END
GO
When i use 2 single quotes it insert the following string: "@.lName" not the actual value of the variable @.lName
my exec statement is this:
EXEC addLabor 'Razor', 'Nazor', 'mid', 'Mr', 'Carpenter Forman','1234','keOWVozC+wmBvaqgkVkZci5y4vFLdTKfZOVG4C6BSN6H2MBP6pdsIWA0SdPAlPJra0EjEj+uXI/kXSiBuwwnKQ==','6/27/2005 12:00:00 AM' , 'O.C. Public Library', 1, 3, '3', '7/25/20052:38:02 PM', '1233 Shady Canyon, Irvine', '', '', '12345','123-12-1234', 'African American', '3'
I appreciate any help or hints
thanks to all
All of your parameters are declared like this:
@.lName varchar,
Should be like this:
@.lName varchar(1000),
a type of varchar is like varchar(1), meaning a variable length string with a max of 1 character. The number used should likely match the underlying length of the field in the table
Data from two tables
I am developing a system that tracks phonecall usage. I have an sql server db that includes two tables which include (amongst others) the fields below them:
phone_emp_mast (contains user info.)
-------
Account_Code
First_Name
Last_Name
Department
detail (contains info on individual calls)
---
Account_Code
Duration
I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department
I tried the following query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;
and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause
Can anyone figure out a better way of doing this? please?The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;|||Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME
but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?
Thanks in advance!|||You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
SUM( CASE WHEN d.duration < 1 THEN 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
The DECODE version would be:
SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls|||I'm actually using SQL server... do you know if SQL server supports this function?|||Originally posted by kieranodwyer
I'm actually using SQL server... do you know if SQL server supports this function?
Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.|||Thanks alot Tony... Thats working perfectly!
phone_emp_mast (contains user info.)
-------
Account_Code
First_Name
Last_Name
Department
detail (contains info on individual calls)
---
Account_Code
Duration
I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department
I tried the following query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;
and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause
Can anyone figure out a better way of doing this? please?The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins
FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p
WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;|||Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME
but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?
Thanks in advance!|||You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:
SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
SUM( CASE WHEN d.duration < 1 THEN 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;
The DECODE version would be:
SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls|||I'm actually using SQL server... do you know if SQL server supports this function?|||Originally posted by kieranodwyer
I'm actually using SQL server... do you know if SQL server supports this function?
Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.|||Thanks alot Tony... Thats working perfectly!
Subscribe to:
Posts (Atom)