Crystal Reports- last receipt date

Here is the situation: I have 3 tables.
Table 1: part number, unit of measure, standard cost
Table 2: last price paid, and Date of last price paid (same as last receipt date)
Table 3: PO# of last price paid (same as last receipt date)

When I insert these fields into the details section in Crystal Reports, I get duplicate part numbers listed on the report. Each part number may have several vendors attached to it, so this is expected.

This is what I am getting:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.50 7/19/04 11111
88888 Each 1.00 1.80 7/22/04 22222
88888 Each 1.00 2.00 7/20/04 33333

What I want:

ItemNumber UM Std Cost LastCost LastReceiptDate PONumber
88888 Each 1.00 1.80 7/22/04 22222

I want the report to only show the most recent receipt date, price paid, and PO. Basically I need to do some sort of "comparison" between multiple vendors and its last receipt date within each part number.

Please help~~~~ Thank you!Hi,

I had faced same problem but I think if u used Command Object insted of tables
u can get solution.

write SQL query in Command Object and use MAX function on Receipt Date

-Yags

Crystal Reports join question

Hello all.

I am migrating an access report to crystal reports and having trouble. I have two tables that link each other, but they are different data types. This works fine in access because I can cast one of the fields using cstr(). See query below:

==============================================
SELECT Sum(Tax_Detail.Tax_Amt) AS SumOfTax_Amt, Taxer.Collection_Account

FROM Invoice_Header INNER JOIN (((Invoice_Detail LEFT JOIN Tax_Detail ON cstr(Invoice_Detail.Invoice_Detail) = Tax_Detail.Owner_ID) LEFT JOIN TaxCode ON Invoice_Detail.Tax_Code = TaxCode.TaxCode) LEFT JOIN Taxer ON Tax_Detail.Taxer = Taxer.Taxer) ON Invoice_Header.Document = Invoice_Detail.Document

WHERE (((Tax_Detail.Owner_Type)=0) AND ((Invoice_Header.Document_Date) Between '3/1/2007' And '3/31/2007'))

GROUP BY Taxer.Collection_Account;
==============================================

Does anyone know how I can create this join from Crystal?

Thanks,
bubutYou will need to use Crystals add command through the database expert. There you can paste your sql and it should work.
GJsql

Crystal Report locks up

ok, i am stumped, i don't know what to do with this problem. here is what's going on. i have the following tables in my report:

INCOMINGORDER: {IncomingOrderID, StudentID,....}
ORDERITEM: {OrderItemID, IncomingOrderID, CourseRecordID..}
COURSERECORD: {CourseRecordID, StudentID,...}
STUDENT: {StudentID, FName, LName, Address,..}

The fields with the same names in different tables are foreign keys, ex: ORDERITEM.IncomingOrderID is a foreign key pointing to INCOMINGORDER.IncomingOrderID, and so on.

So in my report i have the following sections:

............
Group Header ORDERITEM.IncomingOrderID
Group Header ORDERITEM.CourseRecordID
Details
Group Footer ORDERITEM.CourseRecordID
Group Footer ORDERITEM.IncomingOrderID
.................

In other words, first i group by the field ORDERITEM.IncomingOrderID and then by the field ORDERITEM.CourseRecordID.

In my record selection formula i have this:

{COURSERECORD.COURSERECORDID} = {ORDERITEM.COURSERECORDID} and
{INCOMINGORDER.BILLINGTYPEID} = 7.00

which works fine, but the problem is that when i try to add the line
{STUDENT.STUDENTID} = {INCOMINGORDER.STUDENTID} to link the tables STUDENT and INCOMINGORDER, my report just locks up. And if i try to add the above link in the visual link expert in Crystal Reports.NET i get error: "Query Engine Error", which is odd since INCOMINGORDER.STUDENTID is a foreign key pointing to STUDENT.STUDENTID.

Anybody has explanation for this?
Thanks.ok, a quick update on my post: i was apparently wrong about the report locking up. It did not lock up, it just took a really long time to load, 6 minutes to be exact. Clearly, that's unacceptable. Anyway, what i noticed was that the more conditions i added to my record selection formula, the longer it took for it to load. Initially my record selection was something like this:

{INCOMINGORDER.INCOMINGORDERID} = 202

In this case it loaded fairly quickly, almost no load time. Then i added one more condition:

{INCOMINGORDER.INCOMINGORDERID} = 202 and
{SHIPPABLEITEM.SHIPPABLEITEMID} = {ORDERITEM.SHIPPABLEITEMID}

Still, decent execution time. Then one more condition:

{INCOMINGORDER.INCOMINGORDERID} = 202 and
{SHIPPABLEITEM.SHIPPABLEITEMID} = {ORDERITEM.SHIPPABLEITEMID} and
{STUDENT.STUDENTID} = {INCOMINGORDER.STUDENTID}

A little slower this time, but still acceptable. And then i added the last condition:

{INCOMINGORDER.INCOMINGORDERID} = 202 and
{SHIPPABLEITEM.SHIPPABLEITEMID} = {ORDERITEM.SHIPPABLEITEMID} and
{STUDENT.STUDENTID} = {INCOMINGORDER.STUDENTID} and
{COURSERECORD.COURSERECORDID} = {ORDERITEM.COURSERECORDID}

So that took a really long time to load. As i inserted some of the fields i wanted displayed in the report, it got as long as 6 minutes.
Anybody know what the cause of this could be? I wonder if it's because i am using a DataSet, i don't seem to recall having similar problems with direct database access.

Thanks.|||Hi,

I think there is no need to give

{SHIPPABLEITEM.SHIPPABLEITEMID} = {ORDERITEM.SHIPPABLEITEMID} and
{STUDENT.STUDENTID} = {INCOMINGORDER.STUDENTID} and
{COURSERECORD.COURSERECORDID} = {ORDERITEM.COURSERECORDID}

these lines. They are automatically linked. Right?|||actually no, they are not linked since when i tried to do so in the Visual Linking Expert i got the following error: "Query Engine Error". i didn't know how to fix it so instead i set up the links in the record selection formula.

Crystal Report : Cross Tab Formatting(Urgent)

Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.Hi!I am new to cross tab in reports.I am using Seagate Crystal Report 7.Currently developing report with few tables in it. I am having problem grouping it into the fields that i want.I also wish to know how do i add formula in it..as well as writing queries in the access itself.I cant seems to do anything with the column/row on the report.I hope to have some guidelines.Thank You.

Hi!!!
I'm also new with crystal reports but i have already done a cross tab report using 8.5...anyway, u can use the wizard to guide u in creating ur crosstab...or u can explain further what u want to do so we could help each other in solving ur problem... :)|||I created a cross tab query in Ms Access..hoping to link to Crystal Report 7. But when i tried to arrange the columns n rows..i cant edit anything.eg: There's summarized field to be set in Crystal report 7. I dont want it to count my field..instead displaying just the data. How can i do it?

crosstable

Hi,
Could someone provide me the syntax or a procedure how to create a
alternative for a crosstable like in access.
For example i have a 2 tables, orders and orderdetails. What i want is to
display the data as a crosstable like view.
Here's a example in sheetmode in which i would like to view the data. The
productname comes from the orderdetails.
orderid | productname1 | productname2 | productname3
Is this possible?HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
Dynamic Crosstab Queries
[url]http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=15608&DisplayTab=Article[
/url]
AMB
"Jason" wrote:

> Hi,
> Could someone provide me the syntax or a procedure how to create a
> alternative for a crosstable like in access.
> For example i have a 2 tables, orders and orderdetails. What i want is to
> display the data as a crosstable like view.
> Here's a example in sheetmode in which i would like to view the data. The
> productname comes from the orderdetails.
> orderid | productname1 | productname2 | productname3
> Is this possible?
>
>|||The RAC utility is the closest thing to the Access crosstab
query in S2k.Like Access it's easy to use, no complex coding required for
dynamic crosstabs, and it's much more powerful with many
features/options.
www.rac4sql.net|||For your problem check out the RAC @.rank parameter
in the Help file.
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:OLOCGy$IFHA.3196@.TK2MSFTNGP15.phx.gbl...
> The RAC utility is the closest thing to the Access crosstab
> query in S2k.Like Access it's easy to use, no complex coding required for
> dynamic crosstabs, and it's much more powerful with many
> features/options.
> www.rac4sql.net
>

Crosstab Query

I have two tables Bill and Location.
Bill
(
location_id int,
prod_period datetime,
consumption float,
demand float
)

Location
(
location_id int,
location_name varchar(45)
)

I want to create a stored procedure that takes a parameter of @.year. I
basically want the procedure to return results that show locations
where consumption and/or demand IS NULL or 0 for each month throughout
a given year. I would like my results to look something like this:

location_id year Jan Feb Mar Apr May Jun Jul Aug Sept Oct
Nov Dec
1 2005 0 0 0
2 2005 0 0 0 0
3 2005 0 0

If data does exist for consumption or demand, I would like to show it,
however I am really interested in the locations that have no data
associated with them.

Any ideas of how I can accomplish this?Hi

There are many posts on Crosstab queries, usually the fastest solution is to
use the client side tools to produce this. You may also want to check out
http://www.windowsitpro.com/SQLServ...5608/15608.html.

John

<burke.david@.gmail.com> wrote in message
news:1126090825.869267.236710@.g47g2000cwa.googlegr oups.com...
>I have two tables Bill and Location.
> Bill
> (
> location_id int,
> prod_period datetime,
> consumption float,
> demand float
> )
> Location
> (
> location_id int,
> location_name varchar(45)
> )
> I want to create a stored procedure that takes a parameter of @.year. I
> basically want the procedure to return results that show locations
> where consumption and/or demand IS NULL or 0 for each month throughout
> a given year. I would like my results to look something like this:
> location_id year Jan Feb Mar Apr May Jun Jul Aug Sept Oct
> Nov Dec
> 1 2005 0 0 0
> 2 2005 0 0 0 0
> 3 2005 0 0
> If data does exist for consumption or demand, I would like to show it,
> however I am really interested in the locations that have no data
> associated with them.
> Any ideas of how I can accomplish this?

CROSSTAB ::works in access but not in MS SQL 2000

I am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::

Table 1 --> Info
InfoID
Name

Table 2 --> Item
InfoID
Num
Value

TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num

in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all

does someone knows how to translate it for MS SQL (the table structures are exactly the same)?

thank youI have tried

SELECT Info.Name
,(CASE WHEN Item.Num = 10 THEN Item.value END) AS A
,(CASE WHEN Item.Num = 12 THEN Item.value END) AS B
,(CASE WHEN Item.Num = 15 THEN Item.value END) AS C
,(CASE WHEN Item.Num = 100 THEN Item.value END) AS D
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID

i must get one line and i get > 1000 lines !

with GROUP BY Info.Name it doesn't work

what is wrong ?

thank you|||Info

InfoID | Name
............
1 | jean
2| Pierre

------------

Item

InfoID | Num | Value
...............
1 | 400 | 1_400
1 | 10 | 1_10
1 | 15 | 1_15
1 | 12 | 1_12
1 | 100 | 1_100
1 | 40 | 1_40
2 | 400 | 2_400
2 | 10 | 2_10
2 | 15 | 2_15
2 | 12 | 2_12
2 | 100 | 2_100
2 | 40 | 2_40

I must get ::

Name | A | B | C | D
................
Jean | 1_10 | 1_12 | 1_15 | 1_100
Pierre | 2_10 | 2_12 | 2_15 | 2_100|||Dunno if you have gotten a response yet. Crosstab is a native Access function that is not available in SQL 2000. You can google ags crosstab or RAC or SQL for a 3rd party add-on to SQL.

Regards,

hmscott

Cross-Database View Permissions

I have a view in database1 that uses views in database2.
My DBA says I must give users DATA READER permissions to
the tables referenced by the database2 views. I am trying
to keep customers OUT of the tables, only allowing access
thru views.
Do I really have to give DATA READER to the tables in
database2 to get my cross-database view references to work?It depends on whether you allow cross database ownership chaining. This is a
configurable option post SP3 wheras prior to that it was implicit. Basically
if all the views and tables have the same owner e.g. dbo and the databases
themselves are owned by the same login e.g. sa then users with permissions
to select from a view in database1 don't need any explicit permissions on
objects in database2 - they do still require access though.Post SP3 this is
configured using sp_configure 'Cross DB Ownership Chaining' and sp_dboption
with the 'db chaining' option. See BOL for more on cross database ownership
chaining.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?|||To add to Jasper's response, both databases need to be owned by the same
login in order for the dbo-owned object ownership chain to be unbroken.
Your users still need a security context in database2 but no permissions or
role memberships need to be granted if objects are only accessed indirectly
via referencing database1 objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?

cross-database relationship

How can I create a relationship between tables from two
different databases in SQL 2000?
MadhuriThis is not possible with declarative referential integrity (foreign keys).
You need to use triggers to enforce the relationship.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:03c001c3d709$9c920a80$a401280a@.phx.gbl...
> How can I create a relationship between tables from two
> different databases in SQL 2000?
> Madhuri

cross-database relationship

How can I create a relationship between tables from two
different databases in SQL 2000?
MadhuriThis is not possible with declarative referential integrity (foreign keys).
You need to use triggers to enforce the relationship.
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:03c001c3d709$9c920a80$a401280a@.phx.gbl...

quote:

---

> How can I create a relationship between tables from two
> different databases in SQL 2000?
> Madhuri

---

|||Dan,
Thanks a lot- could you point me to some good documentation and books
that explain how to create such triggers? Thanks again
Madhuri
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Here's a link to a list of many of the more popular SQL Server books.
http://www.sql-server-performance.com/reviews.asp
Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhuri Mittal" <madhurimittal@.yahoo.com> wrote in message
news:uxTyEB51DHA.4064@.tk2msftngp13.phx.gbl...

quote:

---

> Dan,
> Thanks a lot- could you point me to some good documentation and books
> that explain how to create such triggers? Thanks again
> Madhuri
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

---

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advance
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>
|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegr oups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]

the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0

and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.

Thanks in advanceDarsin (darsin@.gmail.com) writes:

Quote:

Originally Posted by

I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
>
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
>
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.

SELECT U.userName,
Division1 = coalesce(MAX(CASE do.divisionID WHEN 1 THEN 1 END), 0),
Division2 = coalesce(MAX(CASE do.divisionID WHEN 2 THEN 1 END), 0),
Division3 = coalesce(MAX(CASE do.divisionID WHEN 3 THEN 1 END), 0)
FROM Users U
LEFT JOIN DivisionOfUsers do ON U.userId = do.userId
GROUP BY U.userName

The MAX in this query is somewhat of a trick. Each CASE expression returns
a non-NULL value for at most one row. So whether we use MIN - or even AVG -
does not matter. But by using MAX and GROUP BY, we don't need to left-join
for each division.

As you might understand from the query, it only handles a known set of
divisions. There is no way to write a query that handles an unknown
number of divisions. That would be a fundamental breach of the relational
foundations: a query returns a table, and a table has a finite number of
columns.

The only way to get an output if the possible columns are not known
beforehand is to use dynamic SQL to build a query like the one above.
Rather than endulging in dynamic SQL yourself, you may want to take a
look at the third-party tool RAC, http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Tab Missing Column

Hi,

Have a bit of a strange problem here.

Am working with a colleague on a system which generates "simple" tables and cross tables, both using the same SQL views.

The "simple" tables show all the data correctly... When we come to the cross tabs however, something very weird happens. The x axis is for the section in the factory - for 14 out of the 15 sections the data appears perfectly, but for one column we get nothing, no data, no column heading, nothing. Looking at the info pulled back from the SQL views all the info is there as well.

If we change the data to point to a different section code, it quite happily appears on the report...

Anyone had anything similar, or any ideas as to what could be happening.

I have googled this and come up with nothing, have asked on another forum with no response - am rapidly pulling out what little hair I have left!Problem Solved...

Spelling error in the Select string....

Sorry !!!

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:
>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
> >Is there a cross reference of User Name to Login Name in SQL Server
> >somewhere? In its system tables? It doesn't appear so? The user
> >table for a database doesn't contain the login name.
> >Does anybody know if this cross reference is accessible via system
> >tables?
> >Scott
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
> > How you get the login names for database users depends on
> > what version of SQL Server.
> > If this is just to check a particular database user, you can
> > execute sp_help_user 'YourUser' and it will return the user
> > name in the database as well as the login name at the server
> > level.
> > If you just want to know how the tables are related, for SQL
> > Server 2000 the syslogins and sysusers tables are related by
> > SID.
> > If you are on 2005, look at sys.server_principals and
> > sys.database_principals.
> > -Sue
> > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > wrote:
> > >Is there a cross reference of User Name to Login Name in SQL Server
> > >somewhere? In its system tables? It doesn't appear so? The user
> > >table for a database doesn't contain the login name.
> > >Does anybody know if this cross reference is accessible via system
> > >tables?
> > >Scott
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> > On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> > > How you get the login names for database users depends on
> > > what version of SQL Server.
> > > If this is just to check a particular database user, you can
> > > execute sp_help_user 'YourUser' and it will return the user
> > > name in the database as well as the login name at the server
> > > level.
> > > If you just want to know how the tables are related, for SQL
> > > Server 2000 the syslogins and sysusers tables are related by
> > > SID.
> > > If you are on 2005, look at sys.server_principals and
> > > sys.database_principals.
> > > -Sue
> > > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > > wrote:
> > > >Is there a cross reference of User Name to Login Name in SQL Server
> > > >somewhere? In its system tables? It doesn't appear so? The user
> > > >table for a database doesn't contain the login name.
> > > >Does anybody know if this cross reference is accessible via system
> > > >tables?
> > > >Scott
> > sp_help_user info is just what I need, but I'm having trouble
> > executing it from .aspx page. I'll keep trying.- Hide quoted text -
> > - Show quoted text -
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
Scott
Hello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegr oups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>
|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:

>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott
|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
>
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.
|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
>
>
>
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -
|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:[vbcol=seagreen]
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
>
>
>
>
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:

>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
>
>
>
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/li...properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
>
>
>
>
>
>
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:[vbcol=seagreen]
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/li...nt_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
>
>
>
>
>
>
>
>
>
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross owner table replication

Hello,
We have a number of tables for which we need to setup a push type
replication. In the source db, the owner of those tables is dbo, but in the
subscriber dbs, all tables must belong to a different owner (our
application requirement). As I followed replication wizard, I could not
find any option to set it up this way (I got dbo owned tables in the
subscriber dbs).
TIA,
Vitaliy
When you get to the specify article dialog box, click on the browse button
to the right of the tables and in the general tab enter the name of the user
you wish to own the tables on the subscriber in the destination owner text
box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vitalik" <address@.domain.com> wrote in message
news:%23NbuNnu7FHA.4012@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We have a number of tables for which we need to setup a push type
> replication. In the source db, the owner of those tables is dbo, but in
> the
> subscriber dbs, all tables must belong to a different owner (our
> application requirement). As I followed replication wizard, I could not
> find any option to set it up this way (I got dbo owned tables in the
> subscriber dbs).
> TIA,
> Vitaliy
>

Cross Join without Table?

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk

I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end

But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown

Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.

Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(

I think this will work...

Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.

It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.