Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:

SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

Joining MS SqlServer data with Oracle data

OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed.

SqlServer
Group Name ID# Item Condition
AAA123 1 a 1
AAA123 2 a 1
AAA123 3 a 1
AAA123 4 a 2
AAA123 5 a 2
AAA123 1 b 3
AAA123 2 b 4
AAA123 3 b 3
AAA123 4 b 4
AAA123 5 b 3
BBB123 1 a 1
BBB123 2 a 1
BBB123 3 a 2
BBB123 4 a 2
BBB123 5 a 2

Oracle
Group Name ID# Value
AAA123 1 50%
AAA123 2 55%
AAA123 3 60%
AAA123 4 80%
AAA123 5 70%
BBB123 1 35%
BBB123 2 45%
BBB123 3 50%
BBB123 4 50%
BBB123 5 80%

I need to be able to get this:
Group Name Item Condition Value
AAA123 a 1 55%
AAA123 a 2 75%
AAA123 b 3 60%
AAA123 b 4 67.5%
BBB123 a 1 40%
BBB123 a 2 60%

Any idea how I can get the data from these two DBs to talk to each other? Thanks.

You should either import the data from Oracle into some temp table in SQL Server and work from SQL Server or the otherway. You need to choose the host first.

|||

That sounds great!

Can anyone help me understand how to import data from an Oracle DB into a temp table in SqlServer?

Thanks.

|||

Check out the DTS Import/Export wizard via Tools -> Data Transformation Services in Enterprise Manager.

|||

Unfortunately, I don't have enterprise... I tried using the linkedtable stored procedure but I don't have permissions on the Oracle Table, and I will not be granted them. Is there another way to import the data which does not require anything but SELECT priviledges (read-only account) where I can accomplish this using SqlServer Mgmt Studio Express? Thanks again.

-steve

|||

Check out OPENQUERY operator in books online.

|||

OPENQUERY operates on linked tables. Unfortunately, I cannot use this as a linked table because my account does not have permissions to access the sp_addlinkedtable stored procedure.

Does anyone know how I can do this without using linked tables? I can pull the data by connecting directly to the oracle DB (I have an account I can use SELECT with) but I need to use information stored in the SQL Server Table.

Is there some way I can use datatables in asp.net to do this, rather than transact SQL wihin the actual database? Thanks.

Joining and grouping using SQL

I have two tables... Table1 and table2 and I need to reconcile them
with each other.

Table1 has the fields Product number, invoice number, price, vat
amount and total.

Table2 has the same data but in a slightly different format...

It has Product Number, invoice number, Price and type.
Type will say Vat or sale and amount will be the vat amount or sale
amount

What is on one row in Table1, will be spread accross 2 rows in Table2.

It means that Invoice number is not unique in Table2.

How do I either group the data in Table2, so I can join it with Table1
or make Table2 the same format as Table1.

If there is something else you can think of to help me, by all means
suggest away.

Regards,
Ciarn[posted and mailed, please reply in news]

Ciar?n (chudson007@.hotmail.com) writes:
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.

SELECT ...
FROM Table1 t1
JOIN (SELECT ProductNumber, InvoiceNumber, Price = SUM(Price)
FROM Table2
GROUP BY ProductNumber, InvoiceNumber) AS t2
ON t1.ProductNumber = t2.ProductNumber
AND t1.InvoiceNumber = t2.InvoiceNumber

This may not be exactly what you need; your request is a bit vague. If
you want more help, I suggest that you include:

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'd suggest looking at the design of your tables first.

chudson007@.hotmail.com (Ciar?n) wrote in message news:<7f9b6870.0411040804.3e32e925@.posting.google.com>...
> I have two tables... Table1 and table2 and I need to reconcile them
> with each other.
> Table1 has the fields Product number, invoice number, price, vat
> amount and total.
> Table2 has the same data but in a slightly different format...
> It has Product Number, invoice number, Price and type.
> Type will say Vat or sale and amount will be the vat amount or sale
> amount
> What is on one row in Table1, will be spread accross 2 rows in Table2.
> It means that Invoice number is not unique in Table2.
> How do I either group the data in Table2, so I can join it with Table1
> or make Table2 the same format as Table1.
> If there is something else you can think of to help me, by all means
> suggest away.
> Regards,
> Ciarn