Jump to content

I need to stack data from 2 tables into one single table the...

robert fuschetto

Recommended Posts

I need to stack data from 2 tables into one single table then aggregate do a chart. We have done this many times using the MORE command. To use MORE, all column names in the resulting table must have the same NAME and TYPE.

Further, up till now, the column names in both tables were always the same anyway. But in this case the department the user will be prompted for is known as DEPT in table1 and DEPARTMENT in table2.

Since we will prompt via auto creating controls on an HTML page, we need them to be also be similarly named so excess controls are not created. Hope this makes sense.

Anyway, as mentioned, in the first table the column is called DEPT (A5). In the second table it is called DEPARTMENT (A5),

I created a hold file for for table1 with DEPT. It is also used in a WHERE as a Dynamic Multi Select.

For table2 to I first defined DEPT / A5 = table2.DEPARTMENT.

When I go to use table2.DEPARTMENT in my WHERE condition, when I say Equals/Paramterthe DYMAMIC option is not there so I cannot make it MultiSelect.

Can you not use Multi select on a DEFINED column in a WHERE statement

Link to comment
Share on other sites

You can use the APP HOLD to save the master file to an app folder in your APP PATH one time and update it with a DEFINE. I tried it and it allowed me to use that DEFINE with a Dyanamic parameter.

One other idea is to use SET ASNAMES = ON and when you reference the DEPARTMENT field in creating your hold file make the AS DEPT and then you should just be able to reference that field directly in the filter.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...