Extract BOM from MacolaES via SQL ?

[IDrum4U]

I'm trying to show MacolaES BOMs on our company Intranet (not the entire BOM, but just the contents of a single assembly based on the Assy# the user enters).

This worked fine for me in Progression, but with MacolaES the IMREFDES_SQL table is making life difficult for this beginner-to-intermediate SQL "wannabe" programmer. I'm stumped on getting imrefdes_note to concatenate.

Does anyone have any SQL code they would be willing to share? Or leads to who can help me with this?

Thank you in advance for the help!

- Adrian

--------------------------------------

Note: BOM output columns are...

BMPRDSTR_SQL.comp_item_no
IMITMIDX_SQL.item_desc_1
IMITMIDX_SQL.item_desc_2
BMPRDSTR_SQL.qty_per_par
IMREFDES_SQL.imrefdes_note

 

[ChrisP520]

Hello Adrian:

What is the issue with concatenating the note field? Are you getting NULLS? If so, have you tried using:

SET CONCAT_NULL_YIELDS_NULL OFF

at the beginning of your query? SQL Server, by default, has this set to ON. Therefore, if you try to concatenate a non-NULL field with a NULL field, you will get a NULL.

I hope this helps.

Chris Poulin

 

[dgillz]

You are going to need to write a nested select statement to do this. I have done this before and I am trying to find the code, but it is going to look something like this:

SELECT bmprdstr_sql.item_no, bmprdstr_sql.seq_no, bmprdstr_sql.comp_item_no, bmprdstr_sql.qty_per_par, imrefdes_sql.imrefdes_note
FROM bmprdstr_sql
  (SELECT imrefdes_note
   FROM imrefdes_sql
   WHERE bmprdstr_sql.seq_no = imrefdes_sql.imrefdes_seq_no AND bmprdstr_sql.item_no = imrefdes_sql.imrefdes_parent_item AND bmprdstr_sql.comp_item_no = imrefdes_sql.imrefdes_item_no)

If I find the code I will post it, but this may get you started.

You may want to post this in one of the SQL forums.

Unlike in progression, the reference designators cannot be null, so I don't think you'll run into the problem Chris mentioned.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry