on 01-24-2016 11:06 PM
Hey,
This is probably a pretty basic answer for most of you, but it has me stumped! I have two tables which I'd like to link, but the data I'd like to use to link them is split over two fields in one table, but contained in a single field in the other.
In one table (called PLINV) there's a single field called INV_NO_LINE - the first 9 numbers contain the invoice number, and the following 3 characters refer to each line of the invoice. So it'll be in the format 123456789001, 123456789002 etc.
The other table (called ORDLIN) has two separate fields, one with the invoice number (INV_NO, 9 characters) and one with the line number (INV_LINE, 3 characters). From the ORDLIN table I'd like to fetch a field called NARRATIVE.
I'm a bit stuck with what to put as the command. I put the following, but I was a bit confused about what goes in each section (as you can probably see!)
SELECT
ORDLIN.NARRATIVE
FROM
PLINV LEFT OUTER JOIN ORDLIN ON
substr(PLINV.INV_NO_LINE,1,9) = ORDLIN.INV_NO AND
substr(PLINV.INV_NO_LINE,10,3) = ORDLIN.INV_LINE
WHERE
substr(PLINV.INV_NO_LINE,1,9) = ORDLIN.INV_NO AND
substr(PLINV.INV_NO_LINE,10,3) = ORDLIN.INV_LINE
Can anyone tell me exactly what I should put in the Command box for this to work? Thank you for any help you can give me.
Hi David,
Please see if this works:
SELECT
ORDLIN.NARRATIVE
FROM
PLINV LEFT OUTER JOIN ORDLIN ON
PLINV.INV_NO_LINE = ORDLIN.INV_NO + ORDLIN.INV_LINE
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Abhilash's response assumes two things: ORDLIN.INV_NO is a string (varchar) field type and that you're connecting to a MS SQL database (or other database that allows "+" to append two strings together.) (From your post, it appears that ORDLIN.INV_LINE is a string, so we're not going to worry about it.)
If ORDLIN.INV_NO is a number field type, you'll have to convert it to a string before you can append the two fields together. The syntax for this is very dependent on what type of database you're connecting to.
Also, be sure that you use a single command to pull ALL of the data for your report. When you combine either multiple commands or tables and a command, it will force Crystal to do the join in memory instead of pushing it to the database. This can significantly negatively impact the speed of your report. If you're interested in more information about using commands, see my blog post here:
-Dell
Hi,
Sorry, I knew I'd have more problems! The field which the command is fetching is working perfectly when added to the report, however I'm trying to include it in a formula field - it doesn't seem to be working correctly.
A bit of background - there's a Narrative field on the PLINV table but it isn't always completed, and in case where it's blank on the PLINV table then I'd like to get it from the Narrative field on the ORDLIN table - hence the command to fetch that field.
I've added this formula: IF isnull({PLINV.NARR}) THEN {Command.NARR} ELSE {PLINV.NARR}
Using this formula, the report is only returning 69 lines - most of which are duplicates of each other.
If I change the formula to: IF isnull({PLINV.NARR}) THEN "TEST" ELSE {PLINV.NARR}
(ie the same, but returning the word TEST rather than the Command)
The report now returns 508 lines, and there doesn't appear to be any duplicates.
Any idea why this would happen, or if there's an easier way to do what I'm doing?
Thank you for any help you can offer.
Could you please save your report "with data" and attach it here? To do that, change the file extension of the report from .rpt to .txt and use the advanced editor so you can attach it. I'll take a look at it. I think it has to do with how you're joins are working, but I won't know for sure without checking it.
Thanks!
-Dell
Hi Dell,
Just wanted to check whether you got my email? Your email address was hidden in your profile so I got it from your website which was listed in your profile, it was your Gmail address. I don't want to seem pushy, I know that sending a strange file type (ie .rpt) could be seen as spam/malicious so I wanted to check that you received it.
Thanks,
Dave
Hi Dave,
The slowness in the report is due to the fact that you're combining a command with tables. Crystal will pull all of the data into memory and then do the join there in this situation, which can significantly slow down a report.
I would modify the report to use a single command without any tables. That command might look something like this:
SELECT
PRLREGLIN.COMP_NO,
PRLREGLIN.REG_REF,
PRLREGLIN.VAT_EXCLUSIVE,
PRLREGLIN.GL_CODE,
IsNull(PRLREGLIN.GL_CODE, 'TEST') as GLCODE,
Reputils.f_numbertodate(PRLREGHED.DATE_SETUP),
RSSGLGRNORD.GL_CODE,
Case IsNull(PRLREGLIN.TY_PE, 'N/A')
when 'CRN' THEN 0-PRLREGLIN.VAT_EXCLUSIVE
else PRLREGLIN.VAT_EXCLUSIVE
end VATEXCL,
whosetup.FULLNAME as SETUP_USER,
RSSCENSUP.SUPP_NAME
FROM PRLREGLIN
LEFT JOIN PRLREGHED
ON PRLREGLIN.COMP_NO=PRLREGHED.COMP
AND PRLREGLIN.REG_REF=PRLREGHED.REF)
Left Join RSSGLGRNORD
PRLREGLIN.GRN_LINE = RSSGLGRNORD.GRN_NO + RSSGLGRNORD.GRN_LINE_NO
Left Join RSSCENSUP
on PRLREGHED.SUPPLIER = RSSCENSUP.SUPPLIER
Left Join SYSINTUSERS as whosetup
on PRLREGHED.WHO_SETUP = whosetup.USERNAME
WHERE PRLREGLIN.REG_REF>='0010000003'
AND PRLREGLIN.REG_REF<='0010000064'
ORDER BY PRLREGLIN.COMP_NO, PRLREGLIN.REG_REF
To get this, I went to "Show SQL Query" on the database menu and got the main query for the report which I simplified - Crystal adds a bunch of syntax stuff to the query that's not really needed and which, IMHO, makes it more difficult to read. I then added in the table from the command and the other two tables from the Database Expert which weren't used under "Show SQL Query" because none of the fields from them were in the report. The last thing that I did was convert both of your formulas to SQL so that the command will bring back the values you're looking for from the database instead of having Crystal do the calculation.
If you need additional fields on the report, you'll add them to the command so that they're available in the report. DO NOT add tables to the report - you'll do it ALL in the command. Once you have the command set up, delete the tables from the Database Expert.
I also see that you have three parameters in the report that are not being used anywhere. The correct way to use this will be in the Where clause of the command, NOT in the Select Expert. However, the Command Editor will not be able to "see" the parameters that you've configured in the report. So, here's what I recommend that you do:
1. Open the Company Codes param, click on the "Actions" drop-down and export the list of values that you've added for it. This way you won't have to re-type them when you recreate the parameter.
2. Delete all three parameters.
3. Open the Command Editor (in the Database Expert, right-click on the command and edit it.)
4. Create the parameters in the Command Editor - you won't be able to add the list of values here, but we'll get to that in the next step. In order for the parameters to actually be saved, they have to be used in the command. Also, you'll have to create two parameters for the Payment Dates - the command won't let you create a range parameter, so you'll have something like "Payment Date Start" and "Payment Date End" and you'll use them like this in the Where clause:
table.DateField >= {?Payment Date Start} and table.DateField <= {?Payment Date End}
Save the command when you're done. You could even add one parameter at a time, save the command and test it just to make sure that you're getting the results you expect.
5. The parameters you added will now appear in the Parameter Fields section of the Field Explorer. Edit the Company Codes parameter to import the list of data that was exported in step 1 above.
Please let me know if you have any questions.
-Dell
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.