cancel
Showing results for 
Search instead for 
Did you mean: 

How to link a table using a command

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

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

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thank you both, that seems to have worked a treat! Unfortunately it's very slow fetching the data, but I can always make a cup of tea while I wait

Thank you very much for the quick responses.

Former Member
0 Kudos

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.

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks again for the quick response. The report has some confidential information so I've had to make some changes to it to remove some of the data, and I'd prefer to send it privately rather than post it publicly if that's ok? Can I PM it to you?

DellSC
Active Contributor
0 Kudos

I don't know if you can attach it to a PM.  If you can't, check my profile and send it to my email.

-Dell

Former Member
0 Kudos

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

DellSC
Active Contributor
0 Kudos

Hi Dave,

Sorry about the long response time - I was out of the office both Thursday and Friday.  I will take a look at the report this morning and let you know what I find.

-Dell

DellSC
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Wow, that's great, thank you for that! Thank you for taking the time to look into this for me, it's very much appreciated.

I'll give this a try this week, thank you for the indepth answer.

Answers (0)