Skip to main content
Solved

Is it possible to UNION two tables in Generic Inquiry?

  • 11 July 2024
  • 3 replies
  • 81 views

There are two tables with same colomns. Is there anyway to get the union result using Generic Inquiry?

3 replies

nhatnghetinh
Rank
Userlevel 7
Badge +5

Hi @MPushpakumara48 

You can use Cross 2 DACs in Generic Inquiry but it will create many duplicate lines of data.

Another way you can create a new DAC is by creating a View in SQL and Union 2 tables in SQL.

 

Best Regards,

NNT

Hi Everyone. If a member helped you, please *Like* their post, and if a member provided an answer to your question, please mark it as *Best Answer*
M
Rank

Hi @MPushpakumara48 

You can use Cross 2 DACs in Generic Inquiry but it will create many duplicate lines of data.

Another way you can create a new DAC is by creating a View in SQL and Union 2 tables in SQL.

 

Best Regards,

NNT

Thank You!

lbarker
Rank
Userlevel 5
Badge

I use an attribute called MYPIVOT that is a combo, and I add values 1 to 10 as values with sort orders.

Then I use that in my joins.  Add CSAttributeDetail to your inquiry.

The one link to the main table of data has a join of attributeID = ‘MYPIVOT’ and SortOrder is greater than or equal to  2 so it creates 2 lines per main table data row.

Then u join the sub table of data to main but in your join u set the CSAttribututeDetail.sortorder=1. 

The second sub table has a join where the sort order =2

And in your inquiry fields you can do formula’s if you only want data on the first row for instance like

=Iif([CSAttributeDetail.SortOrder]=1,[ARTran.Qty],0)

 

 

Reply


Terms & ConditionsCookie settings