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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.