on 05-23-2021 2:30 PM
Hi,
We are using prefixes in numbering series while generating sales and purchasing documents, while I try to use the query generator to get some reports, how can I combine prefixes with a purchase order number???
Hi
Hope it will be helpful
Select CONCAT(TO_VARCHAR(IFNULL(T2."BeginStr",'')),TO_VARCHAR(T1."DocNum")) from OPOR T1 INNER JOIN NNM1 T2 ON T1."Series"=T2."Series" AND T1."ObjType"="ObjectCode" Where "DocEntry"=2
OR you can also use
Select TO_VARCHAR(IFNULL(T2."BeginStr",'')) ||TO_VARCHAR(T1."DocNum") from OPOR T1 INNER JOIN NNM1 T2 ON T1."Series"=T2."Series" AND T1."ObjType"="ObjectCode" Where "DocEntry"=2
Thanks,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT T0.[DocNum], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
Please suggest how to combine it in the above query...
Regards
Hi
please use this one . Although I have used '-' between series Name and DocNum. if not required you can remove
SELECT (T2.[SeriesName]+'-'+CAST(T0.[DocNum] as varchar)) as [Document Number],T0.[DocNum], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN NNM1 T2 ON T2.[Series]=T0.[Series] AND T2.[ObjectCode]=T0.[ObjType]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You need the NNM1 table.
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
SELECT T0.[DocNum], T0.[CANCELED], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
Please suggest how to combine it in the above query...
Regards
User | Count |
---|---|
100 | |
11 | |
10 | |
6 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.