DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

Distinct Type Count

I have a weird MDX request and I'm unsure of how to accomplish this.

My relation table has two fields Department(int) and Employee Type(int)

I need to get a distinct count of the number of distinct employee types per Department.

E.g for the data below: (calculated member) DistinctTypeCount=4 (when dep=1) (four distinct types of employees in this department

Dep. Emp. Type

-

1 23

1 2

1 4

1 23

1 4

1 4

1 10

Can anyone suggest an mdx query for this calculated member? If I redesigning the relational view on which the cube is based makes things easier I can definitely go that route.

>My relation table has two fields Department(int) and Employee Type(int)

How the fields are exposed in your UDM? Are they dimension attributes? What design has the dimension?

|||yes they are dimension attributes.

Distinct document map labels

Here is what my document map tree looks like know...
Plant 1
Product Type 1
Product Code 1
Plant 1
Product Type 2
Product Code 1
Plant 1
Product Type 2
Product Code 2
Plant 1
Product Type 2
Product Code 3
What I would like the tree to look like is...
Plant 1
Product Type 1
Product Code 1
Product Type 2
Product Code 1
Product Code 2
Product Code 3
My table is grouped by the plant, product type and product code with no
details group.
Thanks for your helpyou have to make the groups to display like you want the doc map...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Heather M" <HeatherM@.discussions.microsoft.com> wrote in message
news:13BCFDAD-96C4-44FB-8CB1-2A47D2441B45@.microsoft.com...
> Here is what my document map tree looks like know...
> Plant 1
> Product Type 1
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 2
> Plant 1
> Product Type 2
> Product Code 3
> What I would like the tree to look like is...
> Plant 1
> Product Type 1
> Product Code 1
> Product Type 2
> Product Code 1
> Product Code 2
> Product Code 3
> My table is grouped by the plant, product type and product code with no
> details group.
> Thanks for your help|||Thanks for your reply. I thought I had my groups setup correctly. The list
I had my tables in was the culprit.

DISTINCT COUNT - unhelpful error message

Hi,
I need to return a distinct count of customers who have ordered goods.
To do this I created a measure of type DISTINCT COUNT, over the CustomerID field in the orders fact table. Being a foreign key for the customers dimension table, the customerID is integer, not-null, and therefore ideal for the purpose.
The measue is created in a new measure group OK, and the cube processes through OK. However when I come to view the data by dragging the new measure onto the columns in the VS browser I get the wonderful message:

"
The query could not be processed: o Internal error: An unexpected exception occured.

"
Doing exactly the same creation process with the ProductID field works fine, and gives the expected results. I've looked at the tables and can see nothing obviously wrong with the data. The only difference is that there are about 3,800 products, and about 1,000,000 customers. However I did the same thing on the prototypes with no problem at all. Version is SQL 2005 Enterprise Ed.
Any thoughts?
Thanks as always,

Richard

Suggest you contact Analysis Services customer support for this situation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

DISTINCT

Hi,
I need to only pull distinct values from my database ie...
SELECT DISTINCT Type, ClickID, Email, FullApp
FROM tblApps
However I also want to get other fields that also are not distinct ie the
record ID number, but if I include the ID number then I get all the rows.
How can I apply DISTINCT on just a few fields, but still return every field
in the table?
--
Regards
Gary Howlett
Systems Developer
www.rainbowgrp.co.ukHi Gary,
The question you have to ask yourself is, when you return the distinct
values from some columns, and also columns with values that are not
distinct, how do you determine which values you are going to return? If you
have a 2 rows with the same Type, ClickID, Email and FullApp, the ID of
which row do you want to return? The highest ID, the lowest ID, a random ID?
If you want the highest or the lowest you can use MAX() or MIN(), a random
one is a bit more difficult.
hth
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||Maybe what you're looking for is to use the GROUP BY clause. If I
understood your question, you're looking to group by a few of the fields,
and still get the other fields. Since you're grouping by some of the
fields, the other fields will have to be returned in some sort of aggregate
function.
An example would be this (run in Query Analyzer):
use northwind
select CustomerID, min(OrderDate) FirstOrderDate
from Orders
group by CustomerID
You essentially get all the "distinct" CustomerIDs, but of course any other
fields would have to be aggregated (see the BOL for the other aggregate
operations available). Every non-grouped field will have to be aggregated
in some way.
HTH
"Gary Howlett" <gary@.rainbowgrp.co.uk> wrote in message
news:jP4%a.3830$z7.642629@.wards.force9.net...
> Hi,
> I need to only pull distinct values from my database ie...
> SELECT DISTINCT Type, ClickID, Email, FullApp
> FROM tblApps
> However I also want to get other fields that also are not distinct ie the
> record ID number, but if I include the ID number then I get all the rows.
> How can I apply DISTINCT on just a few fields, but still return every
field
> in the table?
> --
> Regards
> Gary Howlett
> Systems Developer
> www.rainbowgrp.co.uk
>|||You can't expect to select distinct and select the
record_id.
The record_id is unique, therefore, distinct.
You need to understand exactly what you want to retrieve
with the query.
Regards
>--Original Message--
>Hi,
>I need to only pull distinct values from my database ie...
>SELECT DISTINCT Type, ClickID, Email, FullApp
>FROM tblApps
>However I also want to get other fields that also are not
distinct ie the
>record ID number, but if I include the ID number then I
get all the rows.
>How can I apply DISTINCT on just a few fields, but still
return every field
>in the table?
>--
>Regards
>Gary Howlett
>Systems Developer
>www.rainbowgrp.co.uk
>
>.
>

Displays commas in type FLOAT

I'm writing a SQL script. I'd like to display the value I have in a
column of type FLOAT so that it appears with the commas in the correct
place. It displays now as 10000000.0; I'd like it to display as
10,000,000. Thanks for any help."Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
Your front-end application should really be doing this type of work, not the
database itself.
Rick Sawtell
MCT, MCSD, MCDBA|||Formatting should be done client side, not in SQL.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.|||Do this in the reporting tool / client application, or cast the value to
money (not good) and then use function "convert" to cast it to varchar with
style 1.
Example:
use northwind
go
select
convert(varchar(25), cast(cast(orderid as float) as money), 1)
from
dbo.orders
AMB
"Rick Charnes" wrote:

> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
>

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying rows by month

Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:

Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...

Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.For January, 2003:

SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 1 AND YEAR(DateColumn) = 2003

For February, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 2 AND YEAR(DateColumn) = 2003

and so on...|||you need to use just simple order by this datetime field
if I understand your question

displaying different reports based on a parameter

Hi,

I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.

Thanks a bunch.

add a subreport control and then set the sub report name using an expression

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Display RTF stored in image data type on SRS Report

In Microsoft Project Server, RTF notes are stored in the field
TASK_NOTES_RTF as a SQL Server image datatype. I am trying to display
the RTF note on a SQL 2000 Reporting Services report.
I need either assembly code or embed code examples.
Thanksunfortinuatly you cant
"SAM" <samb@.sambrooks.com> wrote in message
news:1172779991.405337.35550@.v33g2000cwv.googlegroups.com...
> In Microsoft Project Server, RTF notes are stored in the field
> TASK_NOTES_RTF as a SQL Server image datatype. I am trying to display
> the RTF note on a SQL 2000 Reporting Services report.
> I need either assembly code or embed code examples.
> Thanks
>|||Smokey,
I am currently displaying the ascii content of the TASK_NOTES_RTF field in a
SQL 2000 Reporting Services report, so fortunately, you can.
But, thanks for your input.
"Smokey Grindel" wrote:
> unfortinuatly you cant
> "SAM" <samb@.sambrooks.com> wrote in message
> news:1172779991.405337.35550@.v33g2000cwv.googlegroups.com...
> > In Microsoft Project Server, RTF notes are stored in the field
> > TASK_NOTES_RTF as a SQL Server image datatype. I am trying to display
> > the RTF note on a SQL 2000 Reporting Services report.
> >
> > I need either assembly code or embed code examples.
> >
> > Thanks
> >
>
>|||you cant display it formatted
"SamB" <SamB@.discussions.microsoft.com> wrote in message
news:C0DE0C34-359F-48AE-8127-1623D7D01857@.microsoft.com...
> Smokey,
> I am currently displaying the ascii content of the TASK_NOTES_RTF field in
> a
> SQL 2000 Reporting Services report, so fortunately, you can.
> But, thanks for your input.
> "Smokey Grindel" wrote:
>> unfortinuatly you cant
>> "SAM" <samb@.sambrooks.com> wrote in message
>> news:1172779991.405337.35550@.v33g2000cwv.googlegroups.com...
>> > In Microsoft Project Server, RTF notes are stored in the field
>> > TASK_NOTES_RTF as a SQL Server image datatype. I am trying to display
>> > the RTF note on a SQL 2000 Reporting Services report.
>> >
>> > I need either assembly code or embed code examples.
>> >
>> > Thanks
>> >
>>|||Smokiey,
Learn about instantiation, custom report items, the shift key, and the
apostrophe and you will be surprised at what the difference between "can" and
"can't" is, particlularly "formatted."
Thanks again for your timely reply!
"Smokey Grindel" wrote:
> you cant display it formatted
> "SamB" <SamB@.discussions.microsoft.com> wrote in message
> news:C0DE0C34-359F-48AE-8127-1623D7D01857@.microsoft.com...
> > Smokey,
> >
> > I am currently displaying the ascii content of the TASK_NOTES_RTF field in
> > a
> > SQL 2000 Reporting Services report, so fortunately, you can.
> >
> > But, thanks for your input.
> >
> > "Smokey Grindel" wrote:
> >
> >> unfortinuatly you cant
> >>
> >> "SAM" <samb@.sambrooks.com> wrote in message
> >> news:1172779991.405337.35550@.v33g2000cwv.googlegroups.com...
> >> > In Microsoft Project Server, RTF notes are stored in the field
> >> > TASK_NOTES_RTF as a SQL Server image datatype. I am trying to display
> >> > the RTF note on a SQL 2000 Reporting Services report.
> >> >
> >> > I need either assembly code or embed code examples.
> >> >
> >> > Thanks
> >> >
> >>
> >>
> >>
>
>

display result in one row

Hi,

I have two tables:

TableA has two columns: Type, ProductID
TableB has two columns: ProductID, ProductName

I need to get all the ProductName for a specific type and this is my SQL:
select TableB.ProductName
from TableA, TableB
where TableA.ProductID = TableB.ProductID
and TableA.Type = 2

The problem with the above SQL is that it returns one column with a few rows, like the following:

ProductA
ProductB
ProductC

Is there a way to have a single SQL that can return the results horizontal like:

ProductA, ProductB, ProductC

Thank you for help in advance.

SnoopyI don't think this is possible only through a single SQL.|||If not, what do you recommend to do? Thanks!|||Can you provide more info?
What DB, are you using a program to run this SQL??
Would it be possible for you load results into a data structure and format accordingly??|||This is called a cross-tab, and it really should be done on the client. They are much better suited to this kind of task.

There are a number of engine-specific ways to do cross-tabs. The problem is that they are engine specific, so one that works on Oracle won't work on DB2 or on SQL Server. We'll leave them out of this discussion for the moment.

There is a portable way to do it, as long as you have no "ties" (in your case that would be duplicate ProductName values) and a small, fixed maximum number of possible cross-tab values. That goes something like:SELECT a.Type
, Min(b1.ProductName)
+ Coalesce(', ' + Min(b2.ProductName), '')
+ Coalesce(', ' + Min(b3.ProductName), '')
FROM TableA AS a
INNER JOIN TableB AS b1
ON (b1.ProductID = a.ProductID)
LEFT OUTER JOIN TableB AS b2
ON (b2.productID = a.ProductID
AND b1.ProductName < b2.ProductName)
LEFT OUTER JOIN TableB AS b3
ON (b3.productID = a.ProductID
AND b2.ProductName < b3.ProductName)
WHERE 2 = a.Type
GROUP BY a.TypeYou can expand this to get more than three values if needed.

-PatP

Display of date time inforamtion - some columns are NULL some are not

I have a column in a table that is a datetime data type. Some columns
are NULL some are not.
So, a sampling of data could include:
NULL
2005-06-06 12:32:53.000
2005-04-12 11:32:53.000
NULL
NULL
2005-12-22 12:32:53.000
When I select from this column, if the value is NULL, I need to replace
NULL with the word 'No'. If the value is not NULL, then I need to
display the date - so my output needs to look like this:
No
06/06/2005
04/12/2005
No
No
12/22/2005
I know how to convert the date - the problem I am having is converting
the NULL datetime to characters and including the logic to account for
NULLs in the first place.
I suspect that I need a CASE statement, but I am not sure how to
accomplish this.
Thanks-SELECT COALESCE(CONVERT(CHAR(10), datecolumn, 101), 'No')
FROM table
However, I recommend against using ambiguous formats like m/d/y for display.
<wxbuff@.aol.com> wrote in message
news:1138713642.280962.50970@.g43g2000cwa.googlegroups.com...
>I have a column in a table that is a datetime data type. Some columns
> are NULL some are not.
> So, a sampling of data could include:
> NULL
> 2005-06-06 12:32:53.000
> 2005-04-12 11:32:53.000
> NULL
> NULL
> 2005-12-22 12:32:53.000
> When I select from this column, if the value is NULL, I need to replace
> NULL with the word 'No'. If the value is not NULL, then I need to
> display the date - so my output needs to look like this:
> No
> 06/06/2005
> 04/12/2005
> No
> No
> 12/22/2005
> I know how to convert the date - the problem I am having is converting
> the NULL datetime to characters and including the logic to account for
> NULLs in the first place.
> I suspect that I need a CASE statement, but I am not sure how to
> accomplish this.
> Thanks-
>|||
select coalesce(convert(varchar(10),thedatecolu
mn,101),'No')
However, you may be better off doing the formatting in your client|||Aaron -
Perfect! I am still pretty new at this and was unfamiliar with the
COALESCE function. I see from the BOL that it replaces more
complex CASE statements so I am gratified to know that I was thinking
down the right path. Thank you for helping - it will save
me a great deal of time.
Danielle

Display Negative in Brackets

Sorry for the accounting type question, how do you get a negative inside
brackets instead of with a "-"?
E.g. (200.25) instead of -200.25
Any help would be appreciated.What version of RS are you using? I currently have reports in RS 2005
that are formatted as Currency (c) and if the number is negative it
will render as (34.00). I can't remember if RS 2000 did this.|||You need to put formating like this
###0.00;(###0.00)
Look in help for more info on formating numbers..
Stjepan
"Are friends electric?" <Arefriendselectric@.discussions.microsoft.com> wrote
in message news:8BC96D0C-6B8B-4210-9F0A-1B48F945FD4A@.microsoft.com...
> Sorry for the accounting type question, how do you get a negative inside
> brackets instead of with a "-"?
> E.g. (200.25) instead of -200.25
> Any help would be appreciated.

Display Money Type using + and -

This may be an easy question but I've been reading for about a half
hour and experimenting without results.

I simply want the results of my query to display a specific field that
is typed "money" using + and -

The program that consumes the data expects + on positive numbers and -
on negative. I was hoping to do it in the view instead of processing
the results with the VB application that interogates the DB.

ThanksIt should be done in VB
but you can do it like this

select case field when < 0 then '-' + convert(varchar,field) else '+' +
convert(varchar,field) end MoneyField from TableA

I don't know whatt you want to display for 0.00

http://sqlservercode.blogspot.com/|||Why would you do this in the database? And why are you using a MONEY
column in your table?

--
David Portas
SQL Server MVP
--|||Yeah... I guess you're right - it should be done in VB.

I was trying to massage the output from the server rather than expect
each developer (who may be using different tool) from "rolling their
own" routines.

Thanks|||Hi,

MONEY is the type that was used in the database table - I have no
choice with that. I was attempting to do this in the database because
I wanted to store the methods used in "massaging" this data in a
central location. It's just in a view now and maybe I would have moved
it to a parameterized stored procedure later.

So now I'll query from VB, massage the data in VB. Then in 2 years
when somebody re-writes the application using C# for an internet
application they will have to re-write the code instead of just calling
a nicely centralized routine within the database.

I honestly don't mean to offend you but why would you respond to a post
with questions like that?|||No offence. I was trying to help but first I needed more information.
There could have been more than one reason why you'd want to do this -
for example you could have had a requirement to integrate the data with
an external application.

The reason I ask about MONEY in particular is that the problems with
using that datatype in calculations may sometimes be overlooked. Take a
look at the following example. Be aware of the rounding issue when you
develop calculations against the data and think carefully about the
implications before you use MONEY.

DECLA RE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4)

SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000

SET @.mon4 = @.mon1/@.mon2*@.mon3
SET @.num4 = @.num1/@.num2*@.num3

SELECT @.mon4 AS money_result,
@.num4 AS numeric_result

Result:

money_result numeric_result
------- -------
2949.0000 2949.8525

(1 row(s) affected)

--
David Portas
SQL Server MVP
--|||Thanks - that is good information. I would have never guessed that -
however I may be stuck with this data type since I haven't investigate
why it's being used or where I would break something if I changed the
type to decimal.

You've convinced me though - I will never use Money for a datatype.

Cheers|||Why is this the case? From the definition of MONEY in BOL I would expect
the same result.

Mike Reigler

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128520608.921999.41930@.z14g2000cwz.googlegro ups.com...
> No offence. I was trying to help but first I needed more information.
> There could have been more than one reason why you'd want to do this -
> for example you could have had a requirement to integrate the data with
> an external application.
> The reason I ask about MONEY in particular is that the problems with
> using that datatype in calculations may sometimes be overlooked. Take a
> look at the following example. Be aware of the rounding issue when you
> develop calculations against the data and think carefully about the
> implications before you use MONEY.
> DECLA RE
> @.mon1 MONEY,
> @.mon2 MONEY,
> @.mon3 MONEY,
> @.mon4 MONEY,
> @.num1 DECIMAL(19,4),
> @.num2 DECIMAL(19,4),
> @.num3 DECIMAL(19,4),
> @.num4 DECIMAL(19,4)
> SELECT
> @.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
> @.num1 = 100, @.num2 = 339, @.num3 = 10000
> SET @.mon4 = @.mon1/@.mon2*@.mon3
> SET @.num4 = @.num1/@.num2*@.num3
> SELECT @.mon4 AS money_result,
> @.num4 AS numeric_result
> Result:
> money_result numeric_result
> ------- -------
> 2949.0000 2949.8525
> (1 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --|||Nevermind. After some more digging I realized what was happening when MONEY
/ MONEY. Thanks.

Mike Reigler

"Mike Reigler" <mreigler@.melange-inc.com> wrote in message
news:11k800hit25ci21@.corp.supernews.com...
> Why is this the case? From the definition of MONEY in BOL I would expect
> the same result.
> Mike Reigler
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128520608.921999.41930@.z14g2000cwz.googlegro ups.com...
>> No offence. I was trying to help but first I needed more information.
>> There could have been more than one reason why you'd want to do this -
>> for example you could have had a requirement to integrate the data with
>> an external application.
>>
>> The reason I ask about MONEY in particular is that the problems with
>> using that datatype in calculations may sometimes be overlooked. Take a
>> look at the following example. Be aware of the rounding issue when you
>> develop calculations against the data and think carefully about the
>> implications before you use MONEY.
>>
>> DECLA RE
>> @.mon1 MONEY,
>> @.mon2 MONEY,
>> @.mon3 MONEY,
>> @.mon4 MONEY,
>> @.num1 DECIMAL(19,4),
>> @.num2 DECIMAL(19,4),
>> @.num3 DECIMAL(19,4),
>> @.num4 DECIMAL(19,4)
>>
>> SELECT
>> @.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
>> @.num1 = 100, @.num2 = 339, @.num3 = 10000
>>
>> SET @.mon4 = @.mon1/@.mon2*@.mon3
>> SET @.num4 = @.num1/@.num2*@.num3
>>
>> SELECT @.mon4 AS money_result,
>> @.num4 AS numeric_result
>>
>> Result:
>>
>> money_result numeric_result
>> ------- -------
>> 2949.0000 2949.8525
>>
>> (1 row(s) affected)
>>
>> --
>> David Portas
>> SQL Server MVP
>> --
>>|||Hi:

As others have replied, the formatting should probably stay on the
front end.
If you are looking at centralizing some of the process/format, look
into an object oriented solution. This is offtopic for this newsgroup,
but you could create a money class with a formatting function that can
do the + and - formatting for you. All the developers can then use this
class instead of having to roll out their own routines. This solution
will still encapsulate the logic in one place and you don't have to
polute the database with formatting.

HTH,
BZ|||1) Do not use the proprietary MONEY data type and its weird math in
your schema.;You do Google ALL proprietary data types before you decide
to destroy data integrity, portability, etc. in your schem?

2) Then you did wake up in the middle of a freshman class, so you woudl
know that display is NEVER done in the database? You do know that in a
tiered archtecture requires that display is done in the front end? Do
this in the front end, where it is supposed to be!!!

3) Let's get back to the basics of an RDBMS. Rows are not records;
fields are not columns; tables are not files; there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. YOu have not learend the most basic concepts of
RDBMS.

Your whole mental/logical model is **totally wrong** and you need to
stop programming because you are dangerously ignorant. My opinion is
based on 20+ years of teaching SQL, writing standards, six books, and
being paid to fix thing like this.|||CELKO, you need to save yourself some time and just setup an auto
routine that emails new users as they come into the SQL group and tells
them they are all retards.

--CELKO-- wrote:
> 1) Do not use the proprietary MONEY data type and its weird math in
> your schema.;You do Google ALL proprietary data types before you decide
> to destroy data integrity, portability, etc. in your schem?
> 2) Then you did wake up in the middle of a freshman class, so you woudl
> know that display is NEVER done in the database? You do know that in a
> tiered archtecture requires that display is done in the front end? Do
> this in the front end, where it is supposed to be!!!
> 3) Let's get back to the basics of an RDBMS. Rows are not records;
> fields are not columns; tables are not files; there is no sequential
> access or ordering in an RDBMS, so "first", "next" and "last" are
> totally meaningless. YOu have not learend the most basic concepts of
> RDBMS.
> Your whole mental/logical model is **totally wrong** and you need to
> stop programming because you are dangerously ignorant. My opinion is
> based on 20+ years of teaching SQL, writing standards, six books, and
> being paid to fix thing like this.

Display image in Crystal Report 10

I have to generate a report containing data and images in the database, e.g. product information + product image (kept as "image" type in MSSQL database). The information and images are kept in one table named "ProductInfo". The no. of images may vary depending on user input, e.g. on-shelf date. The expected output of the report is similar to below:

<<image1>>
Item: ABC Shampoo
Product No.: 1111

<<image2>>
Item: XXX Toothpaste
Product No.: 2222

<<image3>>
Item: YYY Shaver
Product No.: 3333

User will input the date range in a VB form and a Crystal Report Preview will show the output. I am currently using VB.NET 2003, Crystal Report 10, MSSQL 2000. Would you please help? Thanks very much.

Regards,Store image path in database field, then use picture box in detail section to display pictures.

U can use graphic location property in format editor of picture box...

Display Image Datatype from a table in Reporting Services report..!

I have a table that has an image datatype column and I also have a
contant type column in that same table to define which type of data is
stored in that table.

In this case a screen shot or may be a word document may be stored in
that table...!

I am trying to use that image datatype field in my report and when they
click some button I needed the reporting service report to open a
window based on the content type and display that image.

Has anyone done this? Any help will be much appreciated...

Thanks in advance...This article might help:

http://support.microsoft.com/defaul...kb;en-us;258038

If not, and you're specifically interested in Reporting Services, then
you might want to post in microsoft.public.sqlserver.reportingsvcs.

Simon

Display Dynamic Picture Stored in Database

I'm using CR 8.5 with RDC and ADO, and SQL Server 2005. The pictures are .jpg's and are stored in an Image type column in a Picture table. I'm using ADO within VB to pull back a list of pictures from the Picture table and then passing the ADO recordset to Crystal (using a Blob field for the Image). Crystal is correctly adding all 10 records to the report, but the Blob field is blank instead of showing the picture.

How can I get the pictures to display dynamically from the database?

*RESOLVED* - I didn't realize that my code was taking the ADO recordset and stuffing the data into a CDO Rowset and passing the CDO Rowset to Crystal instead of the ADO recordset. CDO Rowset doesn't support Blob fields.I am having the same issue but I am very new to Crystal. I don't understand all that you said. I am using Crystal XI. Can you tell me what a Blob field is and a CDO Rowset. If you could give me any information it would be a big help. Thanks

RESOLVED* - I didn't realize that my code was taking the ADO recordset and stuffing the data into a CDO Rowset and passing the CDO Rowset to Crystal instead of the ADO recordset. CDO Rowset doesn't support Blob fields