help with view 1

[itasueb]

created a view from great plains data to be used in crystal 8.5

what is happening is that some of my invoices (SOPNUMBE) are doubling itemnumber, accounts, description and amounts

but only occassionaly

Rest are pulling correctly.

Please let me know if I've missed something

Here's my view:

CREATE VIEW dbo.crystal_test
AS
SELECT dbo.IV00101.ITEMDESC, dbo.IV00101.ITMCLSCD, dbo.RM00101.CUSTNMBR, dbo.RM00101.CUSTNAME, dbo.SOP30200.DOCDATE,
dbo.SOP30200.GLPOSTDT, dbo.SOP30200.MSTRNUMB, dbo.SOP30200.SLPRSNID, dbo.SOP30200.VOIDSTTS, dbo.SOP30300.SOPTYPE,
dbo.SOP30300.SOPNUMBE, dbo.SOP30300.ITEMNMBR, dbo.SOP30300.UNITPRCE, dbo.SOP30300.XTNDPRCE, dbo.SOP30300.QUANTITY,
dbo.GL00100.ACTNUMBR_1, dbo.GL00100.ACTNUMBR_2, dbo.GL00100.ACTNUMBR_3, dbo.GL00100.ACTNUMBR_4, DB_NAME(13) AS dbname,
dbo.GL00100.ACTINDX
FROM dbo.SOP10102 INNER JOIN
dbo.SOP30300 ON dbo.SOP10102.SOPTYPE = dbo.SOP30300.SOPTYPE AND
dbo.SOP10102.SOPNUMBE = dbo.SOP30300.SOPNUMBE RIGHT OUTER JOIN
dbo.GL00100 ON dbo.SOP10102.ACTINDX = dbo.GL00100.ACTINDX LEFT OUTER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR LEFT OUTER JOIN
dbo.SOP30200 INNER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR ON dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE AND
dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
WHERE (dbo.GL00100.ACTNUMBR_1 = '4100')

thanks
Sue

 

[Pattycake245]

When you say doubling, do you mean two records are showing up and all fields are exactly the same, or are, say, the amounts different? Either way, it sounds like you may need to group by those columns in order to eliminate "doubles"

Tim

 

[SQLBill]

I don't have an answer for you, but I would like to point out that there are two forums for the Great Plains product.

Go to Find A Forum and search on Great Plains.

As for your question....what do you mean by doubling? Are you getting two rows when there should be one? Or are the values doubling (i.e. 1 becomes 2, 8 becomes 16)??

-SQLBill

Posting advice: FAQ481-4875

 

[itasueb]

an invoice might have

brush 4.00
comb 1.50

and on some invoices is comes across

brush 4.00
brush 4.00
comb 1.50
comb 1.50

Even though its really in there once.

Thought maybe my joins were off

 

[itasueb]

I thought since I was creating the view in SQL Enterprise manager I would post here.

Sorry

 

[SQLBill]

itasueb,

No problem....I understood. I just wanted to point out that there were forums specifically for Great Plains product. Sometimes errors/problems are due to a specific product's version.

We will help you here if we can, but it might be a GP specific 'bug', which is why I mentioned the other forums.

-SQLBill

Posting advice: FAQ481-4875

 

[itasueb]

thanks

Sue

 

[SQLBill]

I believe your joins are messed up....this is what you have:

CREATE VIEW dbo.crystal_test
AS
SELECT dbo.IV00101.ITEMDESC,
       dbo.IV00101.ITMCLSCD,
       dbo.RM00101.CUSTNMBR,
       dbo.RM00101.CUSTNAME,
       dbo.SOP30200.DOCDATE, 
       dbo.SOP30200.GLPOSTDT,
       dbo.SOP30200.MSTRNUMB,
       dbo.SOP30200.SLPRSNID,
       dbo.SOP30200.VOIDSTTS,
       dbo.SOP30300.SOPTYPE, 
       dbo.SOP30300.SOPNUMBE,
       dbo.SOP30300.ITEMNMBR,
       dbo.SOP30300.UNITPRCE,
       dbo.SOP30300.XTNDPRCE,
       dbo.SOP30300.QUANTITY, 
       dbo.GL00100.ACTNUMBR_1,
       dbo.GL00100.ACTNUMBR_2,
       dbo.GL00100.ACTNUMBR_3,
       dbo.GL00100.ACTNUMBR_4,
       DB_NAME(13) AS dbname, 
       dbo.GL00100.ACTINDX
FROM dbo.SOP10102
   INNER JOIN dbo.SOP30300
     ON dbo.SOP10102.SOPTYPE = dbo.SOP30300.SOPTYPE
     AND dbo.SOP10102.SOPNUMBE = dbo.SOP30300.SOPNUMBE
   RIGHT OUTER JOIN dbo.GL00100
    ON dbo.SOP10102.ACTINDX = dbo.GL00100.ACTINDX
   LEFT OUTER JOIN dbo.IV00101
    ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
   LEFT OUTER JOIN dbo.SOP30200
   INNER JOIN dbo.RM00101
    ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR
    ON dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
      AND dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
WHERE (dbo.GL00100.ACTNUMBR_1 = '4100')

Notice this portion:

   LEFT OUTER JOIN dbo.SOP30200
   INNER JOIN dbo.RM00101
    ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR
    ON dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE

One of the ONs is in the wrong place.

Next, I believe that ONs must be for the JOINs they combine...look at this portion and you do this almost every time.

INNER JOIN dbo.SOP30300 
     ON dbo.SOP10102.SOPTYPE = dbo.SOP30300.SOPTYPE
     AND dbo.SOP10102.SOPNUMBE = dbo.SOP30300.SOPNUMBE
   RIGHT OUTER JOIN dbo.GL00100
    ON dbo.SOP10102.ACTINDX = dbo.GL00100.ACTINDX

You are joining table SOP30300 to table GL00100 but your ON is for table SOP10102 to table GL00100.

-SQLBill

Posting advice: FAQ481-4875

 

[itasueb]

Thanks so much, you were right, my joins were a mess.

sue