PDA

View Full Version : Multi-Part Identifier



wstansbury
1-Jul-2013, 11:12 AM
When I create the following command:
case when j3.source = 'DGI-II' then 'HDG' else 'Other' end

Then when I try to drill down on the rpoert to see this result or create a filter using this, I get the following error:
The multi-part identifier "j3.source" could not be bound.

Please see attached pdf.

Pepe
1-Jul-2013, 12:03 PM
.

Bo Andersen
1-Jul-2013, 03:13 PM
Can you show your Dynamic Join (or it's source)?

Best regards
Bo

wstansbury
1-Jul-2013, 03:38 PM
Is this what you need?

SELECT *
FROM dbo.SYSLOG DAI_
LEFT JOIN dbo.CASES J2 ON DAI_.CASE_NUMBER = J2.CASE_NUM
LEFT JOIN dbo.CLIENTS J3 ON J2.CLIENT = J3.SSN

Bob Cergol
1-Jul-2013, 03:52 PM
Hi Wayne,

When you make a report on a dyn-join, using "left" joins instead of "inner" then you at least need to use the column in details, totals, or as a grouper in order for the column to be "known" as an available column for use in an extra column.

If you then want to use that extra column as a filter -- make sure you save the report first -- as Dynamic AI in preview or "apply" mode won't recognize the column exists for use as a filter.

Bob

wstansbury
1-Jul-2013, 04:47 PM
I have the extra column in the Details tab and the file is saved. I add the extra column (ClientSource) as a filter and as soon as try to save I get the error message. Sometimes I am able to save and as soon as I select one of the filter parameters, I get the error message.

Bob Cergol
1-Jul-2013, 05:18 PM
No, not the extra column in the details -- the actual table.column(s) that you are using in the extra column formula.

Bad or missing data in the columns used to join might cause a problem. You could try narrowing the data with a fixed condition to produce a very small result set. If the filter then works, it proves the larger result set includes bad data.

Bob Cergol
1-Jul-2013, 05:28 PM
BTW, the script for the dyn-join you posted shows you're joing a table named Syslog.
SELECT *
FROM dbo.SYSLOG DAI_
LEFT JOIN dbo.CASES J2 ON DAI_.CASE_NUMBER = J2.CASE_NUM
LEFT JOIN dbo.CLIENTS J3 ON J2.CLIENT = J3.SSN

I'm guessing this table logs all manner of system activity -- not necessarily just transactions having to do with cases.
I'm guessing you have lots of rows in syslog where the case number = 0.
I'm guessing you have zero rows in the cases table where the case number = 0.
I think that's a problem.
Try adding a fixed condition to your report on Case_Number: >0

Or try changing the Join to this:
SELECT *
FROM dbo.SYSLOG DAI_
INNER JOIN dbo.CASES J2 ON DAI_.CASE_NUMBER = J2.CASE_NUM
LEFT JOIN dbo.CLIENTS J3 ON J2.CLIENT = J3.SSN



Bob

Bob Cergol
1-Jul-2013, 05:40 PM
Wayne,

I think you'll find it will work if you make both joins inner joins.

SELECT *
FROM dbo.SYSLOG DAI_
INNER JOIN dbo.CASES J2 ON DAI_.CASE_NUMBER = J2.CASE_NUM
INNER JOIN dbo.CLIENTS J3 ON J2.CLIENT = J3.SSN

wstansbury
2-Jul-2013, 10:07 AM
Bob, I tried the inner Join and it worked like a charm.
Thanks again for your help.