Alias table and filter in IDT universe

[system]

HI All,

I have created one alias table and trying to created filter and when i do validate i am getting table or view doesn’t exit

i an using below formual but it working fine with table not on alias table
D_POSITION.DATE IN (SELECT MAX(D_POSITION.DATE) FROM D_POSITION)

i am using IDT 4.0

Any suggestions please

Thanks,
Rave

---

ravek (BOB member since 2006-08-03)

[system]

Hi,

What is D_POSITION? It looks that you are using it in the outer query as well as in the inner query.

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Yes, D_POSITION is the alias table

I did same for table it is working fine.

Thanks,
Rav

---

ravek (BOB member since 2006-08-03)

[system]

@ravek

Yes, D_POSITION is the alias table

I did same for table it is working fine.

Thanks,
Rav

I cant see how the filter you defined would actually work? It looks like you are trying to do a correlated subquery. If you search for that term on here, you will see how to do it.

---

Mak 1 (BOB member since 2005-01-06)

[system]

Hi Mak,

Ok, How i would get max date rows for that table

I am trying to fut the filter on that table or alias

Thank you,
Rave

---

ravek (BOB member since 2006-08-03)

[system]

I think you probably mean that you want the rows with the latest date for a given (something) in the table. For example, if the table had account, date, and amount and you wanted to get the latest amount for each account based on date? could you provide more details on what you’re trying to do and could help with the SQL.

---

erik.stenson (BOB member since 2012-07-30)

[system]

120×120 10.4 KB

Hello all- Here is my quandary. Due to forces beyond my control, people are able to have multiple addresses of the same type active at the same time in our database (don’t shoot me, not my call). If you see the example image attached, the person with ID number 220139 has 2 active addresses (2112884 and 1882629). Each address has a different “Last Changed Date.” I was trying to cull this down to one address per person by choosing the most recent change date, but I seem to have failed so…

By the time you’ve waded through that lot you’ll be an expert…

Debbie

---

Debbie (BOB member since 2005-03-01)

[system]

You can’t reference an alias table because it doesn’t exist, but you already knew that. SQL against a regular table looks like this:

select table.column from table

SQL against an alias table looks like this:

select a.column from table a

The alias name is used with the column but set up in the FROM clause. You need to do the same thing in your own SQL in order for it to work.

---

Dave Rathbun (BOB member since 2002-06-06)