0

Support Alias with SQL tables?

Jet Reports Historic Posts

January 23, 2012 14:14

None

Follow

Hi All,

I have been playing around with an SQL database and found out that a value is retrieved with the use of alias on a table. Can I do this with Jet (besides writting the SQL query)?

Thanks

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts January 24, 2012 00:10

  Hi Jackies,
  
  I don't understand exactly what you mean. Can you explain it more and perhaps show us the SQL query that would do what you're referring to?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 24, 2012 06:46

  Hi fhilton,
  
  I use universal (SQL server) to retrieve information from an SQL based database. I have asked the developer to point the tables used to create a report in this system and he told me that besides 2-3 tables, there is a field retrieved from a table using alias. Find below his response:
  
  
  

  I've investigated this and it seems that system takes this value from dbo.tblAssgPeriodMove, using specifict T-SQL queries as below:
  
  dcTimeAddedLastPeriod = ROUND(ISNULL(tblAssgPeriodMoveLast.dcAddedBudgetAmt,0),0),
  dcTimeAddedThisPeriod = ROUND(ISNULL(tblAssgPeriodMoveCurr.dcAddedBudgetAmt,0),0),
  
  Where tblAssgPeriodMoveLast and tblAssgPeriodMoveCurr is alias to the Table I mentioned.

  
  In other words, tblAssgPeriodMoveLast and tblAssgPeriodMoveCurr are alias in dbo.tblAssgPeriodMove table and I can't find these using NL(What,Table,) function.
• 0
  
  Jet Reports Historic Posts January 24, 2012 17:33

  It sounds like he's just saying that it's using the dcAddedBudgetAmt field from the tblAssgPeriodMove table (assuming that is the table he is referring to). It's doing a rounding operation on the field which you could do in Jet in the table builder using an Excel formula in a formula column.
  
  A SQL alias is just a reference to the table (or possibly in this case a particular record in the table). The 2 lines you listed are not an actual SQL query, but just a piece of a much larger SQL statement. I would guess the aliases are being used because it's referring to 2 different records in the same table (the current record and the previous record), so in SQL this means you have to use aliases rather than the table name so that it's clear which record you're referring to.
  
  The aliases are just a part of this particular SQL statement; they're not a part of your database itself and you won't find them in Jet. You should be able to find and use the table and field being referenced, assuming it is a real field and not a different sort of entity existing on the table.
  
  Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 26, 2012 07:20

  Thank you Hughes for your input!
  
  I will check and let you know about the results.