distinct key word is not accepting in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

distinct key word is not accepting in query

Author

Topic

harshitakohli
Starting Member

2 Posts

Posted - 2010-03-25 : 02:04:37

my query is not executing.
i am unable to use distinct key word while executing the command find error

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-25 : 02:15:15

Can you post the query.

Regards,
Bohra

harshitakohli
Starting Member

2 Posts

Posted - 2010-03-26 : 01:45:59

SELECT distinct OINV.CardCode, OCRD.CardName, OINV.DocDate, OINV.U_POQTY, OITM.ItemCode, OITM.ItemName, INV1.Quantity, INV1.AssblValue, INV1.U_CVD, INV1.U_EdCess,
INV1.U_SHCess, INV1.U_SAD, INV1.U_BED, INV1.U_SED, INV1.U_MRPAssble, CRD1.Street, CRD1.Block, CRD1.ZipCode, CRD1.City, CRD1.Country, CRD1.State,
CRD1.StreetNo, CRD1.Address, CRD1.Building, OINV.DocNum, OINV.U_BillEntryNo, OINV.U_BillEntryDate, OINV.NumAtCard, INV12.TaxId1, INV12.TaxId11,
OINV.U_Modeofdispatch, OINV.DocTotal, INV4.StaCode, INV4.TaxRate, INV4.TaxSum, INV4.BaseSum, OIEI.VatSum, OPCH.CardName AS 'Vendor Name',
CRD1_1.Street AS 'VStreet', CRD1_1.Block AS 'VBlock', CRD1_1.ZipCode AS 'VZipCode', CRD1_1.City AS 'VCity', CRD1_1.Country AS 'VCountry',
CRD1_1.State AS 'VState', CRD1_1.Building AS 'VBuilding', CRD7.CERange, CRD7.CEDivis, CRD7.CEComRate
FROM INV1 INNER JOIN
OINV ON INV1.DocEntry = OINV.DocEntry INNER JOIN
OITM ON INV1.ItemCode = OITM.ItemCode INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode INNER JOIN
INV12 ON INV1.DocEntry = INV12.DocEntry INNER JOIN
INV4 ON INV1.DocEntry = INV4.DocEntry INNER JOIN
OIEI ON OINV.U_IEI = OIEI.DocNum INNER JOIN
OPCH ON OINV.U_PONo = OPCH.DocNum INNER JOIN
PCH1 ON OPCH.DocEntry = PCH1.DocEntry INNER JOIN
OCRD AS OCRD_1 ON OPCH.CardCode = OCRD_1.CardCode INNER JOIN
CRD1 AS CRD1_1 ON OCRD_1.CardCode = CRD1_1.CardCode INNER JOIN
CRD7 ON OCRD.CardCode = CRD7.CardCode

this query return same row twice because of table crd7

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-26 : 05:50:10

quote:
Originally posted by harshitakohli

SELECT distinct OINV.CardCode, OCRD.CardName, OINV.DocDate, OINV.U_POQTY, OITM.ItemCode, OITM.ItemName, INV1.Quantity, INV1.AssblValue, INV1.U_CVD, INV1.U_EdCess,
INV1.U_SHCess, INV1.U_SAD, INV1.U_BED, INV1.U_SED, INV1.U_MRPAssble, CRD1.Street, CRD1.Block, CRD1.ZipCode, CRD1.City, CRD1.Country, CRD1.State,
CRD1.StreetNo, CRD1.Address, CRD1.Building, OINV.DocNum, OINV.U_BillEntryNo, OINV.U_BillEntryDate, OINV.NumAtCard, INV12.TaxId1, INV12.TaxId11,
OINV.U_Modeofdispatch, OINV.DocTotal, INV4.StaCode, INV4.TaxRate, INV4.TaxSum, INV4.BaseSum, OIEI.VatSum, OPCH.CardName AS 'Vendor Name',
CRD1_1.Street AS 'VStreet', CRD1_1.Block AS 'VBlock', CRD1_1.ZipCode AS 'VZipCode', CRD1_1.City AS 'VCity', CRD1_1.Country AS 'VCountry',
CRD1_1.State AS 'VState', CRD1_1.Building AS 'VBuilding', CRD7.CERange, CRD7.CEDivis, CRD7.CEComRate
FROM INV1 INNER JOIN
OINV ON INV1.DocEntry = OINV.DocEntry INNER JOIN
OITM ON INV1.ItemCode = OITM.ItemCode INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode INNER JOIN
INV12 ON INV1.DocEntry = INV12.DocEntry INNER JOIN
INV4 ON INV1.DocEntry = INV4.DocEntry INNER JOIN
OIEI ON OINV.U_IEI = OIEI.DocNum INNER JOIN
OPCH ON OINV.U_PONo = OPCH.DocNum INNER JOIN
PCH1 ON OPCH.DocEntry = PCH1.DocEntry
INNER JOIN
OCRD AS OCRD_1 ON OPCH.CardCode = OCRD_1.CardCode
INNER JOIN
CRD1 AS CRD1_1 ON OCRD_1.CardCode = CRD1_1.CardCode
and OPCH.CardCode = OCRD.CardCode
INNER JOIN
CRD7 ON OCRD.CardCode = CRD7.CardCode

this query return same row twice because of table crd7

can u post the error message ur getting

and in the marked join are not necessary in the select query

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 06:04:36

With object names like INV1, OINV, OITM, OCRD, CRD1, etc, I'm sure glad I'm not working on that database.

Anyway, we're going to need to see an error message. What exactly happens when you run the query?

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources