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 batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--