a month ago
Hello,
I couldn't find any solution to this on this forum. If there is one, apologies in advance and could anyone please link it!
I'm trying to query for a specific project basically its stages and if there are attached marketing documents to them, with a link to that document. The problem is that no matter what I try I always get as a result a permutation of all the document lines with all the stages, so the information on what stage they are specifically assigned to is lost.
Here's an example of a starter version of the query:
SELECT T4."DocEntry" AS "Link", T3."DocEntry" AS "Link", T2."NAME" AS "Project Name", T1."UniqueID" AS "UniqueID", T1."StageID" AS "Stage ID", T0."DOCNUM" AS "Doc number", T0."Total" AS "Total" FROM PMG4 T0 INNER JOIN PMG1 T1 ON T1."AbsEntry" = T0."AbsEntry" INNER JOIN OPMG T2 ON T2."AbsEntry" = T1."AbsEntry" LEFT OUTER JOIN OINV T3 ON T3."DocNum" = T0."DOCNUM" LEFT OUTER JOIN OPCH T4 ON T4."DocNum" = T0."DOCNUM" WHERE T2."NAME" Like '[%0]'
I've tried:
- all manner of LEFT, RIGHT, FULL joins,
- switching the positions of the tables (starting from the invoices first),
- simplifying by dumping purchase invoice and OPMG altogether and focusing on stageID alone
- using StageID as a link instead of AbsEntry
The document lines must be linked in some way to the stages specifically and not just generally to the project, because when a stage is selected the table PMG4 updates to only those that are attached to that stage. Worth noting that I am not an expert in SQL, just a user of SBO. Any guidance is appreciated.
Request clarification before answering.
User | Count |
---|---|
114 | |
10 | |
7 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.