I'm hoping to get some help on a query that is more complex than my limited SQL Query knowledge.
We have an accounting system that I need to extract data from and as the fields containing the data I need are user defined fields, there is not a stock report/query for this. The vendor has a hefty minimum fee charge for any custom query work, so I'm hoping to get it done myself.
VEN.Code AS Vend_Code,
UDT.Label AS Ins_Type,
CONVERT(varchar, UDE.Entry_Date, 01) AS Exp_Date,
VEN.Name as Vendor_Name,
(SELECT UDE.Entry_Character WHERE (UDE.Data_Type = 'CHAR30' AND UDE.User_Defined_Type_Serial = 3)) AS Notes_1,
(SELECT UDE.Entry_Character WHERE (UDE.Data_Type = 'CHAR30' AND UDE.User_Defined_Type_Serial = 4)) AS Notes_2,
(SELECT UDE.Entry_Character WHERE (UDE.Data_Type = 'CHAR30' AND UDE.User_Defined_Type_Serial = 5)) AS Notes_3
FROM [VENDOR] VEN
LEFT OUTER JOIN Address_Translation ATX_US ON
ATX_US.Entity_Type = 'V'
JOIN User_Defined_Entry UDE ON
ATX_US.Entity_Code = UDE.Entity_Code AND
ATX_US.Entity_Code = VEN.Code
JOIN User_Defined_Type UDT ON
UDE.User_Defined_Type_Serial = UDT.Serial
WHERE UDE.Data_Type = 'DATE'
AND (Label = 'GL Expire' OR Label = 'AL Expire' OR Label = 'WC Expire')
AND Entry_Date < current_timestamp
ORDER BY UDE.Entry_Date;
I've also attached a Word doc that contains:
- Screen print of the desired output
- Screen print with structure of the tables I'm working with
- The Query posted above (the highlighted statements are my incorrect attempts)
- Description using screen prints of what I am trying to accomplish
What I am trying to accomplish - see attached for more details.
For all Vendors (Vend_Code) in first query, I would like to display the Entry Character (Notes for that Vendor).
So, for Vendor #6059, I would like to display the
• 1st Entry_Character as Notes 1,
• 2nd Entry_Character as Notes 2
• 3rd Entry_Character as Notes 3
Where now the query is pulling in null values.
I hope this makes sense.
Thanks in advance for any help. I really appreciate it!