Is it possible to get a sub total for a Top N filter?
I've got a group that contains that contains a top N filter.
This filters properly for example, I see only 3 rows per group.
However, I cannot get a sub total for only those 3 rows. It calculates
all
of the rows with the main group.
How would I get the sum for only those 3 rows that are being displayed
on
the report.hi,
keep a visibility condition for other subtotals. i mean to say to
select each three records you would have taken mod ,right? take
absolute value now for not showing other totals
some thing like this
Rownumber(Isnothing)\3<2 such that it will be invisible for that row
Thank you
Raj Deep.A
Showing posts with label contains. Show all posts
Showing posts with label contains. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
Generic question about leaf-level of non-clustered indexes
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question:
Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
EricThis is a multi-part message in MIME format.
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet" wrote in message news:11269d=cb.0309100520.6d6a4e1f@.posting.google.com...In the case of a heap, I understand the leaf-level contains some sortof =RowId which points directly to the right page in the table. Good,pretty quick.If the table contains a clustered index, I understand this =is what'sused as bookmark in all the non-clustered indexes of that table.Question: Couldn't this be extremely inefficient if =the clustered index is notunique?If I created a non unique clustered =index on a (small) column with lowselectivity, I might end up with =thousands of rows for each value inthe clustered index.Then a bookmark on =such a value does not seem too good, does it?I suppose there would be =several pages read before reaching the actualdata page...ThanksEric
--=_NextPart_000_00F4_01C3777E.0F0200C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To add to Tom's comments, From memory this will add four bytes to the =size of your field for the subsequent non unique values. Plus an =addition two bytes because you now have a non fixed length column. As =you can see the extra overhead can be huge on something like an INT. If =there is another column you can add to the clustered index that can make =it unique, then I would consider this as it may actually use less space. = I.e. two INTs unique may use less space that one INT with low =selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
http://www.sqlfe.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:exfAK%235dDHA.568@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To add to Tom's comments, From memory =this will add four bytes to the size of your field for the subsequent non unique =values. Plus an addition two bytes because you now have a non fixed length =column. As you can see the extra overhead can be huge on something like an =INT. If there is another column you can add to the clustered index that can =make it unique, then I would consider this as it may actually use less =space. I.e. two INTs unique may use less space that one INT with low selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.comhttp://www.sqlfe.com">http://www.sqlfe.com
"Tom Moreau"= wrote in message news:exfAK%235dDHA.5=68@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet" wrote in message news:11269d=cb.0309100520.6d6a4e1f@.posting.google.com...In the case of a heap, I understand the leaf-level contains some =sortof RowId which points directly to the right page in the table. Good,pretty quick.If the table contains a clustered index, I understand =this is what'sused as bookmark in all the non-clustered indexes of that table.Question: Couldn't this be extremely inefficient if =the clustered index is notunique?If I created a non unique =clustered index on a (small) column with lowselectivity, I might end up with =thousands of rows for each value inthe clustered index.Then a bookmark on =such a value does not seem too good, does it?I suppose there would be =several pages read before reaching the actualdata page...ThanksEric =
--=_NextPart_000_0037_01C3782F.68E72280--|||This question bothered me and it makes much more sense now.
Thanks very much to both of you.
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question:
Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
EricThis is a multi-part message in MIME format.
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_00F4_01C3777E.0F0200C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_00F4_01C3777E.0F0200C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
To add to Tom's comments, From memory this will add four bytes to the =size of your field for the subsequent non unique values. Plus an =addition two bytes because you now have a non fixed length column. As =you can see the extra overhead can be huge on something like an INT. If =there is another column you can add to the clustered index that can make =it unique, then I would consider this as it may actually use less space. = I.e. two INTs unique may use less space that one INT with low =selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.com
http://www.sqlfe.com
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:exfAK%235dDHA.568@.TK2MSFTNGP11.phx.gbl...
Under the covers, it builds a "uniqueifier", so that all "keys" in the =clustered index are unique, even if you created it on a non-unique =column.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Eric Mamet" <eric_mamet_test@.yahoo.co.uk> wrote in message =news:11269dcb.0309100520.6d6a4e1f@.posting.google.com...
In the case of a heap, I understand the leaf-level contains some sort
of RowId which points directly to the right page in the table. Good,
pretty quick.
If the table contains a clustered index, I understand this is what's
used as bookmark in all the non-clustered indexes of that table.
Question: Couldn't this be extremely inefficient if the clustered index is not
unique?
If I created a non unique clustered index on a (small) column with low
selectivity, I might end up with thousands of rows for each value in
the clustered index.
Then a bookmark on such a value does not seem too good, does it?
I suppose there would be several pages read before reaching the actual
data page...
Thanks
Eric
--=_NextPart_000_0037_01C3782F.68E72280
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
To add to Tom's comments, From memory =this will add four bytes to the size of your field for the subsequent non unique =values. Plus an addition two bytes because you now have a non fixed length =column. As you can see the extra overhead can be huge on something like an =INT. If there is another column you can add to the clustered index that can =make it unique, then I would consider this as it may actually use less =space. I.e. two INTs unique may use less space that one INT with low selectivity.
-- Barry McAuslin
Crucis Technical Solutions Limited
support@.sqlfe.comhttp://www.sqlfe.com">http://www.sqlfe.com
"Tom Moreau"
Under the covers, it builds a ="uniqueifier", so that all "keys" in the clustered index are unique, even if you created =it on a non-unique column.
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Eric Mamet"
--=_NextPart_000_0037_01C3782F.68E72280--|||This question bothered me and it makes much more sense now.
Thanks very much to both of you.
Generic Query Designer quotations
I am trying to use the generic query designer so that I can add dynamic
parameters. My problem is that my query contains double quotes already, ie:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS", "524ALPRXD1_BRITE",
"524ALPRXD1_PH", "524ALPRXD1_COND", "524TLPRXD1_TEMP",
"524CLPRXD2_CONS", "524ALPRXD2_BRITE",
"524ALPRXD2_PH", "524ALPRXD2_COND", "524TLPRXD2_TEMP", "524CLPRXD3_CONS",
"524ALPRXD3_BRITE", "524ALPRXD3_PH",
"524ALPRXD3_COND", "524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP",
"615ALKRAFT_BRITE", "565ALPOTWR_CSF",
"565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND", "565ALPRTWR_BRITE",
"524ALTMP11_CSF", "524CLTMP11_CONS",
"524ALTMP11_BRITE", "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP",
"631ALTMP_CSF", "631CLTMP_CONS", "631ALTMP_BRITE"
FROM GP4_LAB
How would I put this into the generic query designer, because when I try to
do it, I get an error "End of statement expected".Try using single quotes unless double quotes are required by your data
provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Double quotes are required. It is an Oracle database, I am using the OLE DB
provider.
"Donovan R. Smith [MS]" wrote:
> Try using single quotes unless double quotes are required by your data
> provider.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||It should work fine. Unless you are building an expression, quotes should
not matter. Have you tried it with the graphical one?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> Double quotes are required. It is an Oracle database, I am using the OLE
> DB
> provider.
> "Donovan R. Smith [MS]" wrote:
>> Try using single quotes unless double quotes are required by your data
>> provider.
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.|||I need to build the query in the generic designer, because I need to create
dynamic parameters. I don't believe I can do that in the graphical designer?
The actual query works fine in the graphical designer.
"Brian Welcker [MSFT]" wrote:
> It should work fine. Unless you are building an expression, quotes should
> not matter. Have you tried it with the graphical one?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > Double quotes are required. It is an Oracle database, I am using the OLE
> > DB
> > provider.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> >> Try using single quotes unless double quotes are required by your data
> >> provider.
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
>
>|||If you are building the SQL dynamically then I suggest you back up and make
sure you are creating the appropriate syntax. Set the source of a textbox to
the expression you are currently doing in the generic designer. Then you can
copy and paste that into whatever Oracle calls their tool to execute ad hoc
queries. Then you can quickly find out whether the syntax is correct.
Consider that the generic designer essentially allows passthrough queries.
You need the exact same syntax that you would have if you were going
directly against Oracle.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
>I need to build the query in the generic designer, because I need to create
> dynamic parameters. I don't believe I can do that in the graphical
> designer?
> The actual query works fine in the graphical designer.
> "Brian Welcker [MSFT]" wrote:
>> It should work fine. Unless you are building an expression, quotes should
>> not matter. Have you tried it with the graphical one?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
>> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
>> > Double quotes are required. It is an Oracle database, I am using the
>> > OLE
>> > DB
>> > provider.
>> >
>> > "Donovan R. Smith [MS]" wrote:
>> >
>> >> Try using single quotes unless double quotes are required by your data
>> >> provider.
>> >>
>> >> --
>> >> Thanks.
>> >>
>> >> Donovan R. Smith
>> >> Software Test Lead
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>>|||Yes, I have done that and confirmed that I am using the correct syntax. I
think the problem is that Oracle requires any field that starts with a number
to have a quote around it to designate it as a string. These quotes are what
is causing me problems in the Generic query designer, because I want to add
dynamic parameters to the query.
"Bruce Loehle-Conger" wrote:
> If you are building the SQL dynamically then I suggest you back up and make
> sure you are creating the appropriate syntax. Set the source of a textbox to
> the expression you are currently doing in the generic designer. Then you can
> copy and paste that into whatever Oracle calls their tool to execute ad hoc
> queries. Then you can quickly find out whether the syntax is correct.
> Consider that the generic designer essentially allows passthrough queries.
> You need the exact same syntax that you would have if you were going
> directly against Oracle.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> >I need to build the query in the generic designer, because I need to create
> > dynamic parameters. I don't believe I can do that in the graphical
> > designer?
> > The actual query works fine in the graphical designer.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> It should work fine. Unless you are building an expression, quotes should
> >> not matter. Have you tried it with the graphical one?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> >> > Double quotes are required. It is an Oracle database, I am using the
> >> > OLE
> >> > DB
> >> > provider.
> >> >
> >> > "Donovan R. Smith [MS]" wrote:
> >> >
> >> >> Try using single quotes unless double quotes are required by your data
> >> >> provider.
> >> >>
> >> >> --
> >> >> Thanks.
> >> >>
> >> >> Donovan R. Smith
> >> >> Software Test Lead
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >>
> >>
> >>
>
>|||dachrist wrote:
> I think the problem is that Oracle requires any field that starts with a number
> to have a quote around it to designate it as a string.
I'm sure there's an Oracle equivalent of SQL Profiler. Would you run
that tool or NetMon and see whether the generic query designer is
passing the appropriate string to Oracle?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you post the statement that you have in the Generic query designer?
That might help generate some ideas on what is going on.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> Yes, I have done that and confirmed that I am using the correct syntax. I
> think the problem is that Oracle requires any field that starts with a
number
> to have a quote around it to designate it as a string. These quotes are
what
> is causing me problems in the Generic query designer, because I want to
add
> dynamic parameters to the query.
> "Bruce Loehle-Conger" wrote:
> > If you are building the SQL dynamically then I suggest you back up and
make
> > sure you are creating the appropriate syntax. Set the source of a
textbox to
> > the expression you are currently doing in the generic designer. Then you
can
> > copy and paste that into whatever Oracle calls their tool to execute ad
hoc
> > queries. Then you can quickly find out whether the syntax is correct.
> > Consider that the generic designer essentially allows passthrough
queries.
> > You need the exact same syntax that you would have if you were going
> > directly against Oracle.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > >I need to build the query in the generic designer, because I need to
create
> > > dynamic parameters. I don't believe I can do that in the graphical
> > > designer?
> > > The actual query works fine in the graphical designer.
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > >> It should work fine. Unless you are building an expression, quotes
should
> > >> not matter. Have you tried it with the graphical one?
> > >>
> > >> --
> > >> Brian Welcker
> > >> Group Program Manager
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > >> > Double quotes are required. It is an Oracle database, I am using
the
> > >> > OLE
> > >> > DB
> > >> > provider.
> > >> >
> > >> > "Donovan R. Smith [MS]" wrote:
> > >> >
> > >> >> Try using single quotes unless double quotes are required by your
data
> > >> >> provider.
> > >> >>
> > >> >> --
> > >> >> Thanks.
> > >> >>
> > >> >> Donovan R. Smith
> > >> >> Software Test Lead
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers
no
> > >> >> rights.
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||This is the statement that works:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
"524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
"524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
"524ALPRXD2_COND", "524TLPRXD2_TEMP",
"524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
"524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
"565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
"565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
"524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
"631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
What I want to do is put in a couple of dynamic parameters. But I add the
=" at the beginning of the statement, and end the statement with ", the query
will not run, with the error "The expression for the query â'PRPQISâ' contains
an error: [BC30205] End of statement expected." If I change the double
quotes within the statement to single quotes, I get an Oracle error,
ORA-00936: Missing Expression. If I remove the quotes all together, I don't
get anything returned from my query.
"Bruce Loehle-Conger" wrote:
> Could you post the statement that you have in the Generic query designer?
> That might help generate some ideas on what is going on.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > Yes, I have done that and confirmed that I am using the correct syntax. I
> > think the problem is that Oracle requires any field that starts with a
> number
> > to have a quote around it to designate it as a string. These quotes are
> what
> > is causing me problems in the Generic query designer, because I want to
> add
> > dynamic parameters to the query.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > If you are building the SQL dynamically then I suggest you back up and
> make
> > > sure you are creating the appropriate syntax. Set the source of a
> textbox to
> > > the expression you are currently doing in the generic designer. Then you
> can
> > > copy and paste that into whatever Oracle calls their tool to execute ad
> hoc
> > > queries. Then you can quickly find out whether the syntax is correct.
> > > Consider that the generic designer essentially allows passthrough
> queries.
> > > You need the exact same syntax that you would have if you were going
> > > directly against Oracle.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > >I need to build the query in the generic designer, because I need to
> create
> > > > dynamic parameters. I don't believe I can do that in the graphical
> > > > designer?
> > > > The actual query works fine in the graphical designer.
> > > >
> > > > "Brian Welcker [MSFT]" wrote:
> > > >
> > > >> It should work fine. Unless you are building an expression, quotes
> should
> > > >> not matter. Have you tried it with the graphical one?
> > > >>
> > > >> --
> > > >> Brian Welcker
> > > >> Group Program Manager
> > > >> SQL Server Reporting Services
> > > >>
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > >> > Double quotes are required. It is an Oracle database, I am using
> the
> > > >> > OLE
> > > >> > DB
> > > >> > provider.
> > > >> >
> > > >> > "Donovan R. Smith [MS]" wrote:
> > > >> >
> > > >> >> Try using single quotes unless double quotes are required by your
> data
> > > >> >> provider.
> > > >> >>
> > > >> >> --
> > > >> >> Thanks.
> > > >> >>
> > > >> >> Donovan R. Smith
> > > >> >> Software Test Lead
> > > >> >>
> > > >> >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > >> >> rights.
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>|||It looks to me that you are coming up against the typical problem of
embedding double quotes in a string. As a coincidence today I have just been
dealing with creating a dynamic sql statement in a stored procedure which
had the same issue but with single quotes. As I mentioned before, the best
way to deal with making sure that your expression is correct is to back off
from trying to create the dataset and first make sure the correct string is
being created. You should just assign the expression to a text box (I create
a report with nothing on it but a textbox).
For instance in your case I started off with this expression for a textbox.
="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
i.e I did what you said you did, added an =" and finished off with a ".
Trying to go into preview it fails with an error. Which is what I expected
would happen. You can not put in double quotes in a string without following
the special string rule, do it twice.
I assigned this string to the textbox:
="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
Notice that everywhere you want your resulting string to have double quotes
I put double quotes twice. In preview the textbox has this:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
Just what you want. When testing this your textbox needs to end up with
exactly the string expected by Oracle. Once you have this then you can
assign the expression to the dataset and away you go.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> This is the statement that works:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
"615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
>
> What I want to do is put in a couple of dynamic parameters. But I add the
> =" at the beginning of the statement, and end the statement with ", the
query
> will not run, with the error "The expression for the query 'PRPQIS'
contains
> an error: [BC30205] End of statement expected." If I change the double
> quotes within the statement to single quotes, I get an Oracle error,
> ORA-00936: Missing Expression. If I remove the quotes all together, I
don't
> get anything returned from my query.
>
> "Bruce Loehle-Conger" wrote:
> > Could you post the statement that you have in the Generic query
designer?
> > That might help generate some ideas on what is going on.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > Yes, I have done that and confirmed that I am using the correct
syntax. I
> > > think the problem is that Oracle requires any field that starts with a
> > number
> > > to have a quote around it to designate it as a string. These quotes
are
> > what
> > > is causing me problems in the Generic query designer, because I want
to
> > add
> > > dynamic parameters to the query.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > If you are building the SQL dynamically then I suggest you back up
and
> > make
> > > > sure you are creating the appropriate syntax. Set the source of a
> > textbox to
> > > > the expression you are currently doing in the generic designer. Then
you
> > can
> > > > copy and paste that into whatever Oracle calls their tool to execute
ad
> > hoc
> > > > queries. Then you can quickly find out whether the syntax is
correct.
> > > > Consider that the generic designer essentially allows passthrough
> > queries.
> > > > You need the exact same syntax that you would have if you were going
> > > > directly against Oracle.
> > > >
> > > > Bruce L-C
> > > >
> > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > >I need to build the query in the generic designer, because I need
to
> > create
> > > > > dynamic parameters. I don't believe I can do that in the
graphical
> > > > > designer?
> > > > > The actual query works fine in the graphical designer.
> > > > >
> > > > > "Brian Welcker [MSFT]" wrote:
> > > > >
> > > > >> It should work fine. Unless you are building an expression,
quotes
> > should
> > > > >> not matter. Have you tried it with the graphical one?
> > > > >>
> > > > >> --
> > > > >> Brian Welcker
> > > > >> Group Program Manager
> > > > >> SQL Server Reporting Services
> > > > >>
> > > > >> This posting is provided "AS IS" with no warranties, and confers
no
> > > > >> rights.
> > > > >>
> > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > >> > Double quotes are required. It is an Oracle database, I am
using
> > the
> > > > >> > OLE
> > > > >> > DB
> > > > >> > provider.
> > > > >> >
> > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > >> >
> > > > >> >> Try using single quotes unless double quotes are required by
your
> > data
> > > > >> >> provider.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Thanks.
> > > > >> >>
> > > > >> >> Donovan R. Smith
> > > > >> >> Software Test Lead
> > > > >> >>
> > > > >> >> This posting is provided "AS IS" with no warranties, and
confers
> > no
> > > > >> >> rights.
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks Bruce! That did it.
"Bruce Loehle-Conger" wrote:
> It looks to me that you are coming up against the typical problem of
> embedding double quotes in a string. As a coincidence today I have just been
> dealing with creating a dynamic sql statement in a stored procedure which
> had the same issue but with single quotes. As I mentioned before, the best
> way to deal with making sure that your expression is correct is to back off
> from trying to create the dataset and first make sure the correct string is
> being created. You should just assign the expression to a text box (I create
> a report with nothing on it but a textbox).
> For instance in your case I started off with this expression for a textbox.
> ="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
> i.e I did what you said you did, added an =" and finished off with a ".
> Trying to go into preview it fails with an error. Which is what I expected
> would happen. You can not put in double quotes in a string without following
> the special string rule, do it twice.
> I assigned this string to the textbox:
> ="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
> Notice that everywhere you want your resulting string to have double quotes
> I put double quotes twice. In preview the textbox has this:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> Just what you want. When testing this your textbox needs to end up with
> exactly the string expected by Oracle. Once you have this then you can
> assign the expression to the dataset and away you go.
> Bruce L-C
>
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> > This is the statement that works:
> >
> > SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> > "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> > "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> > "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> > "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> > "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
> "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> > "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> > "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> > "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> > "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
> >
> >
> > What I want to do is put in a couple of dynamic parameters. But I add the
> > =" at the beginning of the statement, and end the statement with ", the
> query
> > will not run, with the error "The expression for the query 'PRPQIS'
> contains
> > an error: [BC30205] End of statement expected." If I change the double
> > quotes within the statement to single quotes, I get an Oracle error,
> > ORA-00936: Missing Expression. If I remove the quotes all together, I
> don't
> > get anything returned from my query.
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Could you post the statement that you have in the Generic query
> designer?
> > > That might help generate some ideas on what is going on.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > > Yes, I have done that and confirmed that I am using the correct
> syntax. I
> > > > think the problem is that Oracle requires any field that starts with a
> > > number
> > > > to have a quote around it to designate it as a string. These quotes
> are
> > > what
> > > > is causing me problems in the Generic query designer, because I want
> to
> > > add
> > > > dynamic parameters to the query.
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > If you are building the SQL dynamically then I suggest you back up
> and
> > > make
> > > > > sure you are creating the appropriate syntax. Set the source of a
> > > textbox to
> > > > > the expression you are currently doing in the generic designer. Then
> you
> > > can
> > > > > copy and paste that into whatever Oracle calls their tool to execute
> ad
> > > hoc
> > > > > queries. Then you can quickly find out whether the syntax is
> correct.
> > > > > Consider that the generic designer essentially allows passthrough
> > > queries.
> > > > > You need the exact same syntax that you would have if you were going
> > > > > directly against Oracle.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > > >I need to build the query in the generic designer, because I need
> to
> > > create
> > > > > > dynamic parameters. I don't believe I can do that in the
> graphical
> > > > > > designer?
> > > > > > The actual query works fine in the graphical designer.
> > > > > >
> > > > > > "Brian Welcker [MSFT]" wrote:
> > > > > >
> > > > > >> It should work fine. Unless you are building an expression,
> quotes
> > > should
> > > > > >> not matter. Have you tried it with the graphical one?
> > > > > >>
> > > > > >> --
> > > > > >> Brian Welcker
> > > > > >> Group Program Manager
> > > > > >> SQL Server Reporting Services
> > > > > >>
> > > > > >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > >> rights.
> > > > > >>
> > > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > > >> > Double quotes are required. It is an Oracle database, I am
> using
> > > the
> > > > > >> > OLE
> > > > > >> > DB
> > > > > >> > provider.
> > > > > >> >
> > > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > > >> >
> > > > > >> >> Try using single quotes unless double quotes are required by
> your
> > > data
> > > > > >> >> provider.
> > > > > >> >>
> > > > > >> >> --
> > > > > >> >> Thanks.
> > > > > >> >>
> > > > > >> >> Donovan R. Smith
> > > > > >> >> Software Test Lead
> > > > > >> >>
> > > > > >> >> This posting is provided "AS IS" with no warranties, and
> confers
> > > no
> > > > > >> >> rights.
> > > > > >> >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
parameters. My problem is that my query contains double quotes already, ie:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS", "524ALPRXD1_BRITE",
"524ALPRXD1_PH", "524ALPRXD1_COND", "524TLPRXD1_TEMP",
"524CLPRXD2_CONS", "524ALPRXD2_BRITE",
"524ALPRXD2_PH", "524ALPRXD2_COND", "524TLPRXD2_TEMP", "524CLPRXD3_CONS",
"524ALPRXD3_BRITE", "524ALPRXD3_PH",
"524ALPRXD3_COND", "524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP",
"615ALKRAFT_BRITE", "565ALPOTWR_CSF",
"565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND", "565ALPRTWR_BRITE",
"524ALTMP11_CSF", "524CLTMP11_CONS",
"524ALTMP11_BRITE", "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP",
"631ALTMP_CSF", "631CLTMP_CONS", "631ALTMP_BRITE"
FROM GP4_LAB
How would I put this into the generic query designer, because when I try to
do it, I get an error "End of statement expected".Try using single quotes unless double quotes are required by your data
provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Double quotes are required. It is an Oracle database, I am using the OLE DB
provider.
"Donovan R. Smith [MS]" wrote:
> Try using single quotes unless double quotes are required by your data
> provider.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||It should work fine. Unless you are building an expression, quotes should
not matter. Have you tried it with the graphical one?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> Double quotes are required. It is an Oracle database, I am using the OLE
> DB
> provider.
> "Donovan R. Smith [MS]" wrote:
>> Try using single quotes unless double quotes are required by your data
>> provider.
>> --
>> Thanks.
>> Donovan R. Smith
>> Software Test Lead
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.|||I need to build the query in the generic designer, because I need to create
dynamic parameters. I don't believe I can do that in the graphical designer?
The actual query works fine in the graphical designer.
"Brian Welcker [MSFT]" wrote:
> It should work fine. Unless you are building an expression, quotes should
> not matter. Have you tried it with the graphical one?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > Double quotes are required. It is an Oracle database, I am using the OLE
> > DB
> > provider.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> >> Try using single quotes unless double quotes are required by your data
> >> provider.
> >>
> >> --
> >> Thanks.
> >>
> >> Donovan R. Smith
> >> Software Test Lead
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
>
>|||If you are building the SQL dynamically then I suggest you back up and make
sure you are creating the appropriate syntax. Set the source of a textbox to
the expression you are currently doing in the generic designer. Then you can
copy and paste that into whatever Oracle calls their tool to execute ad hoc
queries. Then you can quickly find out whether the syntax is correct.
Consider that the generic designer essentially allows passthrough queries.
You need the exact same syntax that you would have if you were going
directly against Oracle.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
>I need to build the query in the generic designer, because I need to create
> dynamic parameters. I don't believe I can do that in the graphical
> designer?
> The actual query works fine in the graphical designer.
> "Brian Welcker [MSFT]" wrote:
>> It should work fine. Unless you are building an expression, quotes should
>> not matter. Have you tried it with the graphical one?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
>> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
>> > Double quotes are required. It is an Oracle database, I am using the
>> > OLE
>> > DB
>> > provider.
>> >
>> > "Donovan R. Smith [MS]" wrote:
>> >
>> >> Try using single quotes unless double quotes are required by your data
>> >> provider.
>> >>
>> >> --
>> >> Thanks.
>> >>
>> >> Donovan R. Smith
>> >> Software Test Lead
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>>|||Yes, I have done that and confirmed that I am using the correct syntax. I
think the problem is that Oracle requires any field that starts with a number
to have a quote around it to designate it as a string. These quotes are what
is causing me problems in the Generic query designer, because I want to add
dynamic parameters to the query.
"Bruce Loehle-Conger" wrote:
> If you are building the SQL dynamically then I suggest you back up and make
> sure you are creating the appropriate syntax. Set the source of a textbox to
> the expression you are currently doing in the generic designer. Then you can
> copy and paste that into whatever Oracle calls their tool to execute ad hoc
> queries. Then you can quickly find out whether the syntax is correct.
> Consider that the generic designer essentially allows passthrough queries.
> You need the exact same syntax that you would have if you were going
> directly against Oracle.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> >I need to build the query in the generic designer, because I need to create
> > dynamic parameters. I don't believe I can do that in the graphical
> > designer?
> > The actual query works fine in the graphical designer.
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> It should work fine. Unless you are building an expression, quotes should
> >> not matter. Have you tried it with the graphical one?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> >> > Double quotes are required. It is an Oracle database, I am using the
> >> > OLE
> >> > DB
> >> > provider.
> >> >
> >> > "Donovan R. Smith [MS]" wrote:
> >> >
> >> >> Try using single quotes unless double quotes are required by your data
> >> >> provider.
> >> >>
> >> >> --
> >> >> Thanks.
> >> >>
> >> >> Donovan R. Smith
> >> >> Software Test Lead
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >>
> >>
> >>
>
>|||dachrist wrote:
> I think the problem is that Oracle requires any field that starts with a number
> to have a quote around it to designate it as a string.
I'm sure there's an Oracle equivalent of SQL Profiler. Would you run
that tool or NetMon and see whether the generic query designer is
passing the appropriate string to Oracle?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Could you post the statement that you have in the Generic query designer?
That might help generate some ideas on what is going on.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> Yes, I have done that and confirmed that I am using the correct syntax. I
> think the problem is that Oracle requires any field that starts with a
number
> to have a quote around it to designate it as a string. These quotes are
what
> is causing me problems in the Generic query designer, because I want to
add
> dynamic parameters to the query.
> "Bruce Loehle-Conger" wrote:
> > If you are building the SQL dynamically then I suggest you back up and
make
> > sure you are creating the appropriate syntax. Set the source of a
textbox to
> > the expression you are currently doing in the generic designer. Then you
can
> > copy and paste that into whatever Oracle calls their tool to execute ad
hoc
> > queries. Then you can quickly find out whether the syntax is correct.
> > Consider that the generic designer essentially allows passthrough
queries.
> > You need the exact same syntax that you would have if you were going
> > directly against Oracle.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > >I need to build the query in the generic designer, because I need to
create
> > > dynamic parameters. I don't believe I can do that in the graphical
> > > designer?
> > > The actual query works fine in the graphical designer.
> > >
> > > "Brian Welcker [MSFT]" wrote:
> > >
> > >> It should work fine. Unless you are building an expression, quotes
should
> > >> not matter. Have you tried it with the graphical one?
> > >>
> > >> --
> > >> Brian Welcker
> > >> Group Program Manager
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > >> > Double quotes are required. It is an Oracle database, I am using
the
> > >> > OLE
> > >> > DB
> > >> > provider.
> > >> >
> > >> > "Donovan R. Smith [MS]" wrote:
> > >> >
> > >> >> Try using single quotes unless double quotes are required by your
data
> > >> >> provider.
> > >> >>
> > >> >> --
> > >> >> Thanks.
> > >> >>
> > >> >> Donovan R. Smith
> > >> >> Software Test Lead
> > >> >>
> > >> >> This posting is provided "AS IS" with no warranties, and confers
no
> > >> >> rights.
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >|||This is the statement that works:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
"524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
"524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
"524ALPRXD2_COND", "524TLPRXD2_TEMP",
"524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
"524TLPRXD3_TEMP", "615CLKRAFT_CONS", "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
"565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
"565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
"524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
"631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
What I want to do is put in a couple of dynamic parameters. But I add the
=" at the beginning of the statement, and end the statement with ", the query
will not run, with the error "The expression for the query â'PRPQISâ' contains
an error: [BC30205] End of statement expected." If I change the double
quotes within the statement to single quotes, I get an Oracle error,
ORA-00936: Missing Expression. If I remove the quotes all together, I don't
get anything returned from my query.
"Bruce Loehle-Conger" wrote:
> Could you post the statement that you have in the Generic query designer?
> That might help generate some ideas on what is going on.
> Bruce L-C
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > Yes, I have done that and confirmed that I am using the correct syntax. I
> > think the problem is that Oracle requires any field that starts with a
> number
> > to have a quote around it to designate it as a string. These quotes are
> what
> > is causing me problems in the Generic query designer, because I want to
> add
> > dynamic parameters to the query.
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > If you are building the SQL dynamically then I suggest you back up and
> make
> > > sure you are creating the appropriate syntax. Set the source of a
> textbox to
> > > the expression you are currently doing in the generic designer. Then you
> can
> > > copy and paste that into whatever Oracle calls their tool to execute ad
> hoc
> > > queries. Then you can quickly find out whether the syntax is correct.
> > > Consider that the generic designer essentially allows passthrough
> queries.
> > > You need the exact same syntax that you would have if you were going
> > > directly against Oracle.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > >I need to build the query in the generic designer, because I need to
> create
> > > > dynamic parameters. I don't believe I can do that in the graphical
> > > > designer?
> > > > The actual query works fine in the graphical designer.
> > > >
> > > > "Brian Welcker [MSFT]" wrote:
> > > >
> > > >> It should work fine. Unless you are building an expression, quotes
> should
> > > >> not matter. Have you tried it with the graphical one?
> > > >>
> > > >> --
> > > >> Brian Welcker
> > > >> Group Program Manager
> > > >> SQL Server Reporting Services
> > > >>
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > >> > Double quotes are required. It is an Oracle database, I am using
> the
> > > >> > OLE
> > > >> > DB
> > > >> > provider.
> > > >> >
> > > >> > "Donovan R. Smith [MS]" wrote:
> > > >> >
> > > >> >> Try using single quotes unless double quotes are required by your
> data
> > > >> >> provider.
> > > >> >>
> > > >> >> --
> > > >> >> Thanks.
> > > >> >>
> > > >> >> Donovan R. Smith
> > > >> >> Software Test Lead
> > > >> >>
> > > >> >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > >> >> rights.
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>|||It looks to me that you are coming up against the typical problem of
embedding double quotes in a string. As a coincidence today I have just been
dealing with creating a dynamic sql statement in a stored procedure which
had the same issue but with single quotes. As I mentioned before, the best
way to deal with making sure that your expression is correct is to back off
from trying to create the dataset and first make sure the correct string is
being created. You should just assign the expression to a text box (I create
a report with nothing on it but a textbox).
For instance in your case I started off with this expression for a textbox.
="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
i.e I did what you said you did, added an =" and finished off with a ".
Trying to go into preview it fails with an error. Which is what I expected
would happen. You can not put in double quotes in a string without following
the special string rule, do it twice.
I assigned this string to the textbox:
="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
Notice that everywhere you want your resulting string to have double quotes
I put double quotes twice. In preview the textbox has this:
SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
Just what you want. When testing this your textbox needs to end up with
exactly the string expected by Oracle. Once you have this then you can
assign the expression to the dataset and away you go.
Bruce L-C
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> This is the statement that works:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
"615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
>
> What I want to do is put in a couple of dynamic parameters. But I add the
> =" at the beginning of the statement, and end the statement with ", the
query
> will not run, with the error "The expression for the query 'PRPQIS'
contains
> an error: [BC30205] End of statement expected." If I change the double
> quotes within the statement to single quotes, I get an Oracle error,
> ORA-00936: Missing Expression. If I remove the quotes all together, I
don't
> get anything returned from my query.
>
> "Bruce Loehle-Conger" wrote:
> > Could you post the statement that you have in the Generic query
designer?
> > That might help generate some ideas on what is going on.
> >
> > Bruce L-C
> >
> > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > Yes, I have done that and confirmed that I am using the correct
syntax. I
> > > think the problem is that Oracle requires any field that starts with a
> > number
> > > to have a quote around it to designate it as a string. These quotes
are
> > what
> > > is causing me problems in the Generic query designer, because I want
to
> > add
> > > dynamic parameters to the query.
> > >
> > > "Bruce Loehle-Conger" wrote:
> > >
> > > > If you are building the SQL dynamically then I suggest you back up
and
> > make
> > > > sure you are creating the appropriate syntax. Set the source of a
> > textbox to
> > > > the expression you are currently doing in the generic designer. Then
you
> > can
> > > > copy and paste that into whatever Oracle calls their tool to execute
ad
> > hoc
> > > > queries. Then you can quickly find out whether the syntax is
correct.
> > > > Consider that the generic designer essentially allows passthrough
> > queries.
> > > > You need the exact same syntax that you would have if you were going
> > > > directly against Oracle.
> > > >
> > > > Bruce L-C
> > > >
> > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > >I need to build the query in the generic designer, because I need
to
> > create
> > > > > dynamic parameters. I don't believe I can do that in the
graphical
> > > > > designer?
> > > > > The actual query works fine in the graphical designer.
> > > > >
> > > > > "Brian Welcker [MSFT]" wrote:
> > > > >
> > > > >> It should work fine. Unless you are building an expression,
quotes
> > should
> > > > >> not matter. Have you tried it with the graphical one?
> > > > >>
> > > > >> --
> > > > >> Brian Welcker
> > > > >> Group Program Manager
> > > > >> SQL Server Reporting Services
> > > > >>
> > > > >> This posting is provided "AS IS" with no warranties, and confers
no
> > > > >> rights.
> > > > >>
> > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > >> > Double quotes are required. It is an Oracle database, I am
using
> > the
> > > > >> > OLE
> > > > >> > DB
> > > > >> > provider.
> > > > >> >
> > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > >> >
> > > > >> >> Try using single quotes unless double quotes are required by
your
> > data
> > > > >> >> provider.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Thanks.
> > > > >> >>
> > > > >> >> Donovan R. Smith
> > > > >> >> Software Test Lead
> > > > >> >>
> > > > >> >> This posting is provided "AS IS" with no warranties, and
confers
> > no
> > > > >> >> rights.
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >|||Thanks Bruce! That did it.
"Bruce Loehle-Conger" wrote:
> It looks to me that you are coming up against the typical problem of
> embedding double quotes in a string. As a coincidence today I have just been
> dealing with creating a dynamic sql statement in a stored procedure which
> had the same issue but with single quotes. As I mentioned before, the best
> way to deal with making sure that your expression is correct is to back off
> from trying to create the dataset and first make sure the correct string is
> being created. You should just assign the expression to a text box (I create
> a report with nothing on it but a textbox).
> For instance in your case I started off with this expression for a textbox.
> ="SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS","
> i.e I did what you said you did, added an =" and finished off with a ".
> Trying to go into preview it fails with an error. Which is what I expected
> would happen. You can not put in double quotes in a string without following
> the special string rule, do it twice.
> I assigned this string to the textbox:
> ="SELECT PRODUCTION_DATE, TESTDATE, ""524CLPRXD1_CONS"", "
> Notice that everywhere you want your resulting string to have double quotes
> I put double quotes twice. In preview the textbox has this:
> SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> Just what you want. When testing this your textbox needs to end up with
> exactly the string expected by Oracle. Once you have this then you can
> assign the expression to the dataset and away you go.
> Bruce L-C
>
> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> news:9069F07D-C554-4172-BD89-E4760FF11316@.microsoft.com...
> > This is the statement that works:
> >
> > SELECT PRODUCTION_DATE, TESTDATE, "524CLPRXD1_CONS",
> > "524ALPRXD1_BRITE","524ALPRXD1_PH", "524ALPRXD1_COND",
> > "524TLPRXD1_TEMP","524CLPRXD2_CONS", "524ALPRXD2_BRITE","524ALPRXD2_PH",
> > "524ALPRXD2_COND", "524TLPRXD2_TEMP",
> > "524CLPRXD3_CONS","524ALPRXD3_BRITE","524ALPRXD3_PH","524ALPRXD3_COND",
> > "524TLPRXD3_TEMP", "615CLKRAFT_CONS",
> "615ALKRAFT_TEMP","615ALKRAFT_BRITE",
> > "565ALPOTWR_CSF","565CLPOTWR_CONS", "565CLWW11_CONS", "524ALTMPSS_COND",
> > "565ALPRTWR_BRITE","524ALTMP11_CSF", "524CLTMP11_CONS","524ALTMP11_BRITE",
> > "524ALTMP11_PH", "524ALTMP11_COND", "524TLTMP11_TEMP","631ALTMP_CSF",
> > "631CLTMP_CONS", "631ALTMP_BRITE" FROM GP4_LAB WHERE (PRODUCTION_DATE = ?)
> >
> >
> > What I want to do is put in a couple of dynamic parameters. But I add the
> > =" at the beginning of the statement, and end the statement with ", the
> query
> > will not run, with the error "The expression for the query 'PRPQIS'
> contains
> > an error: [BC30205] End of statement expected." If I change the double
> > quotes within the statement to single quotes, I get an Oracle error,
> > ORA-00936: Missing Expression. If I remove the quotes all together, I
> don't
> > get anything returned from my query.
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> > > Could you post the statement that you have in the Generic query
> designer?
> > > That might help generate some ideas on what is going on.
> > >
> > > Bruce L-C
> > >
> > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > news:0C34220B-C34E-4117-87A6-F9835BC80B98@.microsoft.com...
> > > > Yes, I have done that and confirmed that I am using the correct
> syntax. I
> > > > think the problem is that Oracle requires any field that starts with a
> > > number
> > > > to have a quote around it to designate it as a string. These quotes
> are
> > > what
> > > > is causing me problems in the Generic query designer, because I want
> to
> > > add
> > > > dynamic parameters to the query.
> > > >
> > > > "Bruce Loehle-Conger" wrote:
> > > >
> > > > > If you are building the SQL dynamically then I suggest you back up
> and
> > > make
> > > > > sure you are creating the appropriate syntax. Set the source of a
> > > textbox to
> > > > > the expression you are currently doing in the generic designer. Then
> you
> > > can
> > > > > copy and paste that into whatever Oracle calls their tool to execute
> ad
> > > hoc
> > > > > queries. Then you can quickly find out whether the syntax is
> correct.
> > > > > Consider that the generic designer essentially allows passthrough
> > > queries.
> > > > > You need the exact same syntax that you would have if you were going
> > > > > directly against Oracle.
> > > > >
> > > > > Bruce L-C
> > > > >
> > > > > "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > news:32DB3644-D789-45E9-A4F1-35173AA6A4D7@.microsoft.com...
> > > > > >I need to build the query in the generic designer, because I need
> to
> > > create
> > > > > > dynamic parameters. I don't believe I can do that in the
> graphical
> > > > > > designer?
> > > > > > The actual query works fine in the graphical designer.
> > > > > >
> > > > > > "Brian Welcker [MSFT]" wrote:
> > > > > >
> > > > > >> It should work fine. Unless you are building an expression,
> quotes
> > > should
> > > > > >> not matter. Have you tried it with the graphical one?
> > > > > >>
> > > > > >> --
> > > > > >> Brian Welcker
> > > > > >> Group Program Manager
> > > > > >> SQL Server Reporting Services
> > > > > >>
> > > > > >> This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > >> rights.
> > > > > >>
> > > > > >> "dachrist" <dachrist@.discussions.microsoft.com> wrote in message
> > > > > >> news:F8F77C8B-FEC5-4D48-A272-2CC7AD4286B1@.microsoft.com...
> > > > > >> > Double quotes are required. It is an Oracle database, I am
> using
> > > the
> > > > > >> > OLE
> > > > > >> > DB
> > > > > >> > provider.
> > > > > >> >
> > > > > >> > "Donovan R. Smith [MS]" wrote:
> > > > > >> >
> > > > > >> >> Try using single quotes unless double quotes are required by
> your
> > > data
> > > > > >> >> provider.
> > > > > >> >>
> > > > > >> >> --
> > > > > >> >> Thanks.
> > > > > >> >>
> > > > > >> >> Donovan R. Smith
> > > > > >> >> Software Test Lead
> > > > > >> >>
> > > > > >> >> This posting is provided "AS IS" with no warranties, and
> confers
> > > no
> > > > > >> >> rights.
> > > > > >> >>
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
Monday, March 19, 2012
Generating File names on the fly
Hi,
I want to create a package that can process a flat file based on the current data. i.e. name of the file contains current date and some predefined characters.
What is the best way to process it?Use a property expression on the ConnectionString property of your FlatFile connection manager to set it to the correct filename (containing the date).
-Jamie
Monday, March 12, 2012
Generating a personalised report for every vendor and mailing out the same
I have the following reporting scenario.
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants us to generate a report for each
customer/vendor from this table and email the resulting output in a
.pdf report to the email address on this record. It is almost like the
account statement for each customer or vendor for that month.
Please clarify how I can implement this solution with DTS. I can see a
stored procedure can go through all the customers but how do I
generate the statement for each customer into a .pdf output and then
email that file to that vendor/customers email address from SQL+DTS
Thanks
KarenKaren
Start with www.sqldts.com
declare @.sql varchar(255)
set @.sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1"
queryout "c:\temp\1.csv" -c -T -SSERVER -Usa -Ppass'
exec master..xp_cmdshell @.sql
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0504111747.90b8491@.posting.google.com...
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants us to generate a report for each
> customer/vendor from this table and email the resulting output in a
> .pdf report to the email address on this record. It is almost like the
> account statement for each customer or vendor for that month.
> Please clarify how I can implement this solution with DTS. I can see a
> stored procedure can go through all the customers but how do I
> generate the statement for each customer into a .pdf output and then
> email that file to that vendor/customers email address from SQL+DTS
> Thanks
> Karen
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants us to generate a report for each
customer/vendor from this table and email the resulting output in a
.pdf report to the email address on this record. It is almost like the
account statement for each customer or vendor for that month.
Please clarify how I can implement this solution with DTS. I can see a
stored procedure can go through all the customers but how do I
generate the statement for each customer into a .pdf output and then
email that file to that vendor/customers email address from SQL+DTS
Thanks
KarenKaren
Start with www.sqldts.com
declare @.sql varchar(255)
set @.sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1"
queryout "c:\temp\1.csv" -c -T -SSERVER -Usa -Ppass'
exec master..xp_cmdshell @.sql
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0504111747.90b8491@.posting.google.com...
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants us to generate a report for each
> customer/vendor from this table and email the resulting output in a
> .pdf report to the email address on this record. It is almost like the
> account statement for each customer or vendor for that month.
> Please clarify how I can implement this solution with DTS. I can see a
> stored procedure can go through all the customers but how do I
> generate the statement for each customer into a .pdf output and then
> email that file to that vendor/customers email address from SQL+DTS
> Thanks
> Karen
Generating a personalised report for every vendor and mailing out the same
I have the following reporting scenario.
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants reporting services to generate a report
for each customer/vendor from this table and email the resulting .pdf
report to the email address on this record.
Please clarify how I can implement this solution can reporting
services do this.
Thanks
KarenPlease look up data-driven query in Books Online. You will need to create a
subscriptions table with a column for each piece of data that is different
among all the report subscribers'; e-mail, vendor number, etc.
Charles Kangai, MCDBA, MCT
"Karen Middleton" wrote:
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants reporting services to generate a report
> for each customer/vendor from this table and email the resulting .pdf
> report to the email address on this record.
> Please clarify how I can implement this solution can reporting
> services do this.
> Thanks
> Karen
>
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants reporting services to generate a report
for each customer/vendor from this table and email the resulting .pdf
report to the email address on this record.
Please clarify how I can implement this solution can reporting
services do this.
Thanks
KarenPlease look up data-driven query in Books Online. You will need to create a
subscriptions table with a column for each piece of data that is different
among all the report subscribers'; e-mail, vendor number, etc.
Charles Kangai, MCDBA, MCT
"Karen Middleton" wrote:
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants reporting services to generate a report
> for each customer/vendor from this table and email the resulting .pdf
> report to the email address on this record.
> Please clarify how I can implement this solution can reporting
> services do this.
> Thanks
> Karen
>
Generating a flat file
Hi ya,
I'm generating a flat file from SSIS package and i'm having some problems.
My package contains this Data Flow which is connecting to a Database and importing the records. I did design a script component since the file I generate has some padding.
Here is the code for the component so that you really understand what i'm trying to achieve over here.
Dim toPadTo As Int32
Dim myDate As String
Dim MultVal As Int64
Dim myMonth As Int16, myMonth2 As String
myMonth = CShort(Row.TransDate.Month)
If myMonth <= 9 Then
myMonth2 = myMonth.ToString.PadLeft(2, CChar("0"))
Else
myMonth2 = myMonth.ToString
End If
myDate = CStr(Row.TransDate.Date.Day) + myMonth2 + CStr(Row.TransDate.Year)
MultVal = CInt(Row.TransAmount * 100)
Dim myChkLength As Int16 = CShort(MultVal.ToString.Length)
Dim myCombine As String = CStr(MultVal) & CStr(myDate)
Dim myCombine2 As String = CStr(MultVal) & CStr(myDate)
Select Case myChkLength
Case 4
toPadTo = 30 - (myCombine.ToString.Length)
Case 5
toPadTo = 30 - (myCombine.ToString.Length - 1)
Case 6
toPadTo = 30 - (myCombine.ToString.Length - 2)
Case 7
toPadTo = 30 - (myCombine.ToString.Length - 3)
End Select
' + myDate.ToString.Length - 1))
Row.myConvert = myCombine2.PadLeft(toPadTo, CChar("0"))
Dim MyNewRow As String = myCombine2.PadLeft(toPadTo, CChar("0"))
Dim ChkSign As Int16
ChkSign = CShort(Math.Sign(CDec(MyNewRow)))
If ChkSign = 1 Then
Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))
ElseIf ChkSign = -1 Then
Row.myConvert = MyNewRow.PadLeft(2, CChar("-"))
Else
Row.myConvert = MyNewRow.PadLeft(2, CChar("+"))
End If
End Sub
Obviously i don't think this is the best approach hence i'm asking? not to mention the sort of problem i'm getting with the + and - insertion part of the code.
To give you an example of how the value would be:
Actual value: 159.23
After transformation it should be like this : +00000000000015923
Depending on the amount x no zeros should be inserted.
Any other way to achieve it? Apart from this I also need to generate a sequence no with some string at the end of the ragged file. How would i go for it ?
Cheers
Rizshe
Read the data in from the SQL database, add a script transformation using the input column, add an output column of type string length 30, transform like: -
Dim Style As String = "+0000000000000000000000000000#;" _
+ "-0000000000000000000000000000#;" _
+ "+0000000000000000000000000000#"
with row
dim n as int = CInt(.myoldcolumn * 100)
.mynewcolumn = Format(n, Style)
end with
then simply output the new columns to the flat file
|||Hi Paul,
Your code doesn't seem to do anything different then my own above. Perhaps you misunderstood me.
I would need to put the 0 padding and depending on the + or - in the amount column also put the sign.
The problem i'm getting is that the amount varies from 0.94 to 124.78 and i would need to pad it accordingly.
Cheers
Rizwan
|||Rizwan,
I may have missed something, but I believe that the code does exactly that. it creates 30 character strings with the correct sign...
|||
Hi Paul,
My deepest apologies. I must be blind that i didn't look at your code carefully.
Yes it does work.
Thank you very much and again forgive me for my stupid mistake
Rizwan
Subscribe to:
Posts (Atom)