Query help

I have a project almost (or so I thought!) completed but now I need to add a column to a table which is throwing everything off. Right now I have TableMain which queries TableA. I need to add a column (bit) to TableMain and then run a query that is basicallyif (bitColumn) query TableB, else query TableA.for each row.

TableA contains a listing, TableB is groups where each group contains multiple items from TableA. The columns I want to pull from each table have the same names (ie. regardless of bitColumn, I want TableA.Name or TableB.Name)

I'm not sure how I can go about doing this, I greatly appreciate any help.

Can you please give following details.

Primary key and Fkey in each table

Example input and output data

|||

Paper (id(pkey), name, code, description, clipID(fkey), etc) <= Think of these as a sheet of paper.

Clip(id(pkey), name, description) <= Think of these as a group of papers paperclipped together.

Cart(id(pkey),user,paperID(fkey),qty)

if someone tries to order a paper that has a clipID, they are forced to buy the entire paperclip. My shopping cart works for regular papers, and I have it notifying the user that they are really ordering the paperclip. What I'm stuck on is displaying the shopping cart, etc.

Shopping Cart: Paper1 + Paper2 + Clip1, where Clip1 has Paper3 and Paper4.

I don't know how to make the cart show Clip1 or whether I should just force add each individual item to the shopping cart (making editing qtys difficult), etc. I was thinking about adding an isClip column to the cart and then the stored procudure returns Paper.name or Clip.name depending on whether isClip is 0 or 1... so that it would be

Cart(id(pkey),user,paperID(fkey),qty,isClip)

I don't know how I would write that query, and I'm not sure its even the best design

|||

I would design like this.

Category table

Products Table

Product details table

In category table : categories are paper and clip

In products table: Different papers(only papers not associated with clips) and clips

In product Details table: Papers that goes with clips

Papers that are associated to clips are only sold as a bunch

When user click on paper, show paper details and when user clicks on clip, show all the papers associated to clip by querying from product details table.

|||A better design would be to have ALL papers clipped, even if it is only clipping a single paper. Then the user can only buy clips.|||

With all of the code already written, I'd like to avoid a complete design restructuring.

Motley: this is what I started working on last night until I ran into a problem. Each paper belongs to a certain topic, and in the catalog are listed under topic headings as the sp returns with order by topic. The papers in a clip do not need to (and rarely will) belong to the same topic. I could have a Clips topic that I display first and then the individual topics, but it would involve checking each clip and seeing how many papers reference it; or perhaps adding a Count field to the Clips table? I would still then need to figure out a way to return the correct ordering...I'm open to hearing other suggestions...

|||

edit...yeah, that doesn't work, never mind...

SELECT

clip.id, clip.name, clip.description,

ISNULL(paper.topic,'CLIP')

FROM

clips clip

LEFT JOIN

papers paper

ON

clip.id = paper.kitID

ORDER BY

topic

|||

Basic query:

SELECT c.id as ClipID,c.Name as ClipName,c.Description as ClipDescription,p.id as PaperID,p.name as PaperName, p.code as PaperCode, p.Description as PaperDescription, etc

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

Return clips and how many papers are attached:

SELECT c.id AS ClipID,c.name,c.description,COUNT(*)

FROM clip c

JOIN paper p ON (c.PaperID=p.id)

GROUP BY c.id,c.name,c.description

Of course this assumes that each paper has a clip associated with it.

With your original structure:

SELECT s.*,name,description

FROM cart s

JOIN clip c ON (s.ID=c.id and s.isClip=1)

UNION

SELECT s.*,name,description

FROM cart s

JOIN paper p ON (s.ID=p.id and s.isClip=0)

OR

SELECT s.*,CASE WHEN c.id IS NOT NULL THEN c.name ELSE p.name END as Name, CASE WHEN c.id IS NOT NULL THEN c.description ELSE p.description END as Description

FROM cart s

LEFT JOIN clip c ON (c.id=s.id and s.isClip=1)

LEFT JOIN paper p ON (p.id=s.id and s.isClip=0)

sql

Query Help

I have a query below that returns a table with some client information along
with the last project manager that met with them and the date they met.
Nothing is included in the table if there are no meetings for that client.
Is there a way that I can still have the client information included even if
there are no meetings?

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T INNER JOIN
dbo.ContactView ON T.ContactIDNum =
dbo.ContactView.ContactIDNum
WHERE (T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799Karl A. Homburg (k-n-o-s-p-a-m-homburg@.upea.com) writes:
> I have a query below that returns a table with some client information
> along with the last project manager that met with them and the date they
> met. Nothing is included in the table if there are no meetings for that
> client. Is there a way that I can still have the client information
> included even if there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

For this type of query, it is always helpful to include CREATE TABLE
statements of your tables, and INSERT statements with sample data
and finally the desired output from the sample. Failing to provide
that increases the risk that you answer is based on a fair amount of
guesswork, like this suggestion:

SELECT T.ContactIDNum, C.Organization, C.Name,
C.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.ContactView C
LEFT JOIN dbo.MeetingView T
ON T.ContactIDNum = C.ContactIDNum
AND (T.[Date] = (SELECT MAX([Date])
FROM Meeting M
WHERE M.ContactIDNum = T.ContactIDNum))

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

>I have a query below that returns a table with some client information along
>with the last project manager that met with them and the date they met.
>Nothing is included in the table if there are no meetings for that client.
>Is there a way that I can still have the client information included even if
>there are no meetings?
>SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
>dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
>dbo.ContactView.ContactIDNum
>WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

Hi Karl,

SELECT T.ContactIDNum, dbo.ContactView.Organization,
dbo.ContactView.Name, dbo.ContactView.UPEAPM,
T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView
ON T.ContactIDNum = dbo.ContactView.ContactIDNum
AND T.[Date] = (SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

> I have a query below that returns a table with some client information along
> with the last project manager that met with them and the date they met.
> Nothing is included in the table if there are no meetings for that client.
> Is there a way that I can still have the client information included even if
> there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

try

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView ON T.ContactIDNum = dbo.ContactView.ContactIDNum
WHERE (T.[Date] IS NULL
OR T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))|||This one almost works. The only problem is that the ClientIDNum for the
rows that do not have any meetings shows up as null.

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:xw2983mmhlyy.1liq73j8yi7i1$.dlg@.40tude.net...
> On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
>> I have a query below that returns a table with some client information
>> along
>> with the last project manager that met with them and the date they met.
>> Nothing is included in the table if there are no meetings for that
>> client.
>> Is there a way that I can still have the client information included even
>> if
>> there are no meetings?
>>
>> SELECT T.ContactIDNum, dbo.ContactView.Organization,
>> dbo.ContactView.Name,
>> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>> FROM dbo.MeetingView T INNER JOIN
>> dbo.ContactView ON T.ContactIDNum =
>> dbo.ContactView.ContactIDNum
>> WHERE (T.[Date] =
>> (SELECT MAX([Date])
>> FROM Meeting
>> WHERE ContactIDNum = T.ContactIDNum))
> try
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T
> RIGHT JOIN dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] IS NULL
> OR T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))|||On Wed, 25 Aug 2004 18:55:46 -0400, Karl A. Homburg wrote:

>This one almost works. The only problem is that the ClientIDNum for the
>rows that do not have any meetings shows up as null.

Hi Karl,

Do the suggestions of Erland and me "almost work" as well?

Displaying the ClientIDNum for the rows without any meeting can be
achieved by replacing T.ContactIDNum (in the SELECT list) with
dbo.ContactView.ContactIDNum.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

query from 3 tables

hi,

I have 3 tables
[Credit Memo - Expense Details] - which i refer to CMED
[Credit Memo - Project Change Request] - which i refer to CMPCR
[Credit Memo Table] - which i refer to CM

CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])

CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]

CMED and CMPCR are not related

My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )

So if there is only a match with CMPCR the CMED fields should be blank.

I am thinking of a union but just couldn't get it.

Below is the code to get all the records where CM invoice matches AND pcr matches
i need it to be OR

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];

MS access 2003 - i hate it but i jsut came on board and this is already setup/

Thankshow about to use outer join?

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.|||you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.

by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.|||i disagree, madafaka

peter's union suggestion works perfectly, you should try it

it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins|||maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.|||why? because it might be way faster, that's why :)|||why? because it might be way faster, that's why :)
Now you're wrong. The performance is the reason I avoid using UNION.

"When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."

"Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"

SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value

"This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"

SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value|||dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN|||ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.|||Just a reply to some of the topics mentioned in previous posts:

- Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).

- There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...

- Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.

- The equivalent of the querySELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2where a.c2 is a foreign key and b.c2 is the corresponding primary key, isSELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
UNION ALL
SELECT a.c1, a.c2, NULL, NULL
FROM tablea AS a WHERE a.c2 IS NULLWhen a.c2 is not a foreign key, the condition in the second query becomesWHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)

- A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.|||Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thankssql

Query Engine Error again

I have crystal reports in my VS2005 project. We have wrapped dll for report printing, but using version 9.2.3300.0. So I got Error "Query Engine Error" when printing. If I use Crystal Reports 10.2.3600.0, It works fine.
How can I print report (10.2.3600.0) by wrapped dll (9.2.3300.0)?Did you miss any dlls?
Do very database and check

Query designer toggle button not present

When I create a Report Server Project Using Visual Studio 2005 with SQL Server 2005 I can create a data source with no problem and the test shows it is good good.

When I next create a report and go to the query builder using that same data source and click on the query builder button I see the Query Builder screen, but there is no toggle button in the top left of the screen so I cannot go into the graphical mode to see the tables.

I have uninstalled and reinstalled both Visual Studio and SQL Server but I still have the same problem. What should I do to get the button visible on the screen?

Can anyone help?

Terry,

Are you setting this report up from a shared data source or from making a new datasource. You may want to set up a shared datasource first then make your report from the shared datasource and see if that works. It works that way for me.

|||

It makes no difference either way, I can have new data source or a shared data source.

I have a server and my computer both of which appear to have a nearly identical setup of Studio and SQL Server but I can perform identical steps on both and on the server I get the toggle button but on my computer I get nothing. This is what makes be think it may be a bad installation but I have reinstalled both Studio and SQL Server on my machine and I still get the same thing!

Thanks for responding though.

Terry

|||

Terry,

You may have already figured it out, but you may just want to use stored procedures. When we create a new report we just go through the wizard, pick our datasource, and then tell it what stored proc we want it to use. I believe the syntax is

exec rptsp_MyStoredProcedure @.MyParameter1,@.MyParameter2

Do you have a lot of experience with SQL queries and stored procs? If not maybe I can help.

Query and process performace with incremental update

hello all,

we are working on a project with a large scale of data (around 1000 rows per second).
we built a cube on this fact table.

this table will hold at most 90M rows.

we need the data in the cube to be "real time", that mean, up to date.

we are doing it by proactive caching- incremental update.

we also need a very good query performance.

that's why the storage mode is set to MOLAP.

we still get a low performace from the cube process and and the querys.

any suggestions how to solve this issues?

Thanks in advance,

Shy Engelberg - Certagon.

You might be seeing the results of the meta data locking (see http://geekswithblogs.net/darrengosbell/archive/2007/04/24/SSAS-Processing-ForceCommitTimeout-and-quotthe-operation-has-been-cancelledquot.aspx) If you are processing the cube very frequently. You probably need to profile the server to gather as much information as you can to figure out where the issues are.

Is it on the source system - selected only new records?

Is the system CPU, IO or memory bound?

Are you using partitions to isolate the processing to a smaller subset of the data?

|||

The SSAS 2005 Performance Guide is a good reference for these kinds of issues ( http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc).

I agree with Darren's idea of identifying whether the problem is occuring in retreiving source data records or in assembling the MOLAP structures. And partitioning may also be beneficial if you can isolate updates to a smaller partition.

You may also want to consider using HOLAP. HOLAP will give you excellent query performance for most queries with shorter processing times.

Bryan