You’re offline. This is a read only version of the page.
955
HI I have been given a test machine with Great Plains for the purpose of writing some Crytal Reports. In particular, I am taking a currently manual process (which runs some Crystal Reports and Excels, and then someone keys in some data into an Excel...) and creating 1 Crystal Report to do this.
Upon some investigation, I noticed that the query was referencing
SOP10200 (& its corresponding header table). This table though
needs to reference both the unposted and history table related to this data. That would include SOP30200 (& its corresponding header
table). When we changed the table to which it is pointing – we got
data for the census and billed amount, but the data regarding expenses or “quoted sales” was empty – this is because it was now looking at the
history table.
So, for the quotes I need the SOP10200. For the invoiced amount
(which is used for billed amount)and qty (which we use for census) I
need SOP30200.
I wrote a Crystal Report that is based on a SQL Union looking at the SOP10200 and SOP10100, as well as going to historical data SOP302 and SOP303 this is where the Census information is kept.
This report has 30,000 records from the current data and only 9 records from the History data (where we get the census).
The History data is only July of 2007. Does that make sense to you?
*This post is locked for comments
We finally got it to make the join. The problem we had is that most history records, especially the ones we want, don't have true item numbers in them. So when it tries to link to the ITEMS table it doesn't find a match. Because we used an inner join it has to skip any history record without a match in the ITEMS table. That is why we only got a handful. In this version I changed the joins to left outer joins, so that those tables are optional. There is still a little issue with some of the data, some of the price records are at 0...
The correct SQL is this:
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source
FROM ((((PBS.dbo.SOP10200
SOP10200 INNER JOIN PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))
INNER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR
UNION ALL
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source
FROM ((((PBS.dbo.SOP30300
SOP30300 LEFT OUTER JOIN PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)
INNER JOIN
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR
The records I need appear to be in SOP 30200.
It gives an error message: Incorrect Syntax near the keyword ON. I think that problem is Crystal has a SQL syntax a little different than SQL Server maybe. BTW, I was told that the history data is def. in SOP30200
I ran this on the SQL Server Query and got this error:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'ON'.
I took a more-thorough look at your initial SQL, and realized a couple of things:
1) CATS is an alias - I'm sorry, I should have realized this. When you call a table more than once in a query (in this case , IV40600), you need to give the table a different name in the query.
2) IV40600 should also be left-outer joined. In your query, I know that the fields you reference for RM00101, IV00101 and the SOP tables you have will be populated, because of the logic internal to GP. However, I'm not so sure about the UserCatLongDescr field; as it's not an 8-digit cryptic field name, it is obviously a newer field, and so may not exist for your historical records - an inner join would result in no record returned. The left outer join gives you, at least, the records for those fields that are populated.
3) The use of parenthesis, (), in your FROM clause, I believe, enforces the priority of the links so, for example, your SOP tables are being linked after the IV40600 data; if there's no IV40600 data, there's no further look for the SOP data.
I've restructured your query, removing the () and putting the order of the tables in a manner that should return the data you want. NOTE: You have the right fields and tables; the problem will be the joins between them.
If you are running this in MS Query Manager (or SQL Server Management Studio) then just paste the code, below, into a query window. If you're working in Crystal, re-arrange the links in the linking window so that the first table of the FROM clause (SOP10100) is the left-most table in the window and, as you go down the FROM clause, those are the tables that should link to the right of SOP10100.
Try this SQL:
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source
FROM
PBS.dbo.SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE)
INNER JOIN
PBS.dbo.IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL
UNION
SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'History' as source
FROM
PBS.dbo.SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE)
INNER JOIN
PBS.dbo.IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR
INNER JOIN
PBS.dbo.RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR
LEFT OUTER JOIN
PBS.dbo.IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL
I tried the Left outer change but it looks the same. One suggestion is to use Customer Number as filter instead of Customer Name - as that is the unique index. Crystal does not enforce consistency of names and customer may possibly have different names on different sales transactions.
This did not work. I have to explore how I can locate where the history records are kept.
Any thoughts how to do this?
Oh. Sorry; I thought you wrote the SQL script you posted. My bad.
If you're writing the SQL, you would change:
INNER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
to
LEFT OUTER JOIN
PBS.dbo.IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)
If you're making the change in Crystal, just click on the "links" button, right-click the link between CATS and IV00101, and in the left-most section there are radio buttons. The top one is clicked (INNER or EQUAL JOIN); choose the next one down (LEFT JOIN, I think it's called). Then save your changes & rerun your data.
Richard, I am not having success implementing this left join. I do not know enough on how to approach this solution.
I'm pretty confused now. You say the interface your Crystal Developer made provides complete data, but Crystal doesn't. What is the other interface? Maybe look into its logic to understand the missing piece.
Second, SOP10200 does not have any reference to SOP30200 - 10200 holds OPEN data, and 30200 holds HISTORY data. Joining the two will result in errors. SOP10200 is the detail table for SOP10100; SOP30200 is the header table for SOP30300. Records are either in 10100/10200 OR they're in 30200/30300.
Third, CATS is not a native GP table (at least, as of GP10.0). It may be an add-on, a customization, or third-party piece. Accordingly, it's possible that it relates to the quote/order/invoice data (SOP) in a way that is causing your problems. Again, I'd try a LEFT OUTER JOIN between SOP & CATS to bring in your data.
the CATS table was in the original SQL the client sent me. It looks like the catalogue master list but I don't know what that means.
Running the report in the interface that our Crystal Developer has
created yields results that are complete. When I ran the same report
in Crystal itself, we saw that the
census number and billed amount were 0. Upon some investigation, we
noticed that the query was referencing SOP10200 (& its
corresponding header table). This table though needs to reference both the unposted and history table related to this data. That would include SOP30200 (& its corresponding header table). When we changed the table to which it is pointing – we got data for the census and billed amount, but the data regarding expenses or “quoted sales” was empty – this is because it was now looking at the history table. Now I am wondering if the SOP 303 is the correct table!
So, for the quotes I need the SOP10200. For the invoiced amount (which we used for billed amount)and qty (which we use for census) I need SOP30200. Now I am wondering if the SOP 303 is the correct table!
Regarding the customers, it is a sub-entity of another customer and is billed as a whole. Therefore, I would like to start billing this customer separately as its own entity. For the meantime, I have save data from another customer. I based my SQL on the exisitng Crystal Reports (unioned for the history and unposted tables).
The SQL looks fine. I'm not familiar with the "CATS" table; is that a third-party add on? Only thing I can think of is that your INNER JOIN with CATS might be limiting the result set in the History query.
From your earlier post, I thought the UNION was between 10100 and 10200 (that is, 10100 was on one side of the UNION, and 10200 was on the other). Clearly, that's not the case!
SOPTYPE = 1 is Quote; sounds like you only have "quotes" in your data. That sounds odd as well, since neither Quotes nor Orders can be posted to the GL, and I imagine you have invoices posted to your GL...
I'd start with a LEFT OUTER JOIN on the CATS table - see if that helps you get other data.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (