How to SELECT the MAX of a column in a virtual table?

This forum contains ProfileStage posts and now focuses at newer versions Infosphere Information Analyzer.

Moderators: chulett, rschirm

Post Reply
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

How to SELECT the MAX of a column in a virtual table?

Post by truenorth »

I am trying to create a virtual table that would only select the MAX of a column. I cannot find a way to use a function, e.g., MAX, on the columns that are selected in creating a virtual table.

For example:

EP_ID
300
400
150
277

I only want the row where EP_ID is 400.

Thanks in advance.
Last edited by truenorth on Fri Nov 11, 2016 9:07 pm, edited 1 time in total.
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Not possible in version 8, in which the virtual table is driven only via a WHERE clause. Once you get version 11.5 you will have the full capabilities of SQL for virtual tables in Information Analyzer.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

Post by truenorth »

My apologies, Ray. Forgot to update the version. I think we are on 11.3.1.2. Not quite it?
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

IBM just had a webinar on this topic--SQL-based virtual tables--a few weeks ago; it is a new feature in version 11.5. It appeared to be command-line driven, with the idea of making the feature available in the IA GUI in a future release. We are also on version 11.3.1.2, so... not quite there yet.
Choose a job you love, and you will never have to work a day in your life. - Confucius
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

Post by truenorth »

Thanks, guys. We'll be eagerly waiting for the GUI update. Meanwhile, would you have any material on the command line method?
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Ah, here it is...

IBM Tech Talk: Virtual Table Feature in Information Analyzer
https://www.youtube.com/watch?v=wBlg2tEB1bA
Choose a job you love, and you will never have to work a day in your life. - Confucius
truenorth
Participant
Posts: 139
Joined: Mon Jan 18, 2010 4:59 pm
Location: San Antonio

Post by truenorth »

Many thanks!
Todd Ramirez
Sr Consultant, Data Quality
San Antonio TX
Post Reply