Hi,

I need to create a SQL query.

I have two tables where I need to retreive data and create a NEW field within the QUERY and then export.

Table 1 and 2 are linked by the same ID reference

The first OUTPUT is 'Model' from Table 1 (LDDBService.Table1).

The second OUTPUT (a NEW field - Let's call it 'NewAdd') depends on the existing value from an 'Address' field in Table 2. I'm using a SubString, but I'm not sure this is correct.

Where, for Example
If Table2.Address is 'xxx.xxx.104.xxx'
I need to return "AA"

Can/should I use an IF statement similar to this?

If not, what should I be doing?

Then I need to Count on the number of occurences of "AA", "BB", "CC" etc.

Please can someone show me how I:
1. Create these values in the NEWADD field
2. Count the Occurence of each NEWADD value
3. Export the data (Crystal Report or .csv)

Thanks in advance,


Neal

A Northern Soul

__________________
Neal

A Northern Soul

Are you using SQL Server or Oracle?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

I am not understanding what your asking can you give us some sample of the data that explains your issue.
basically showing what you have and what you want to see? With various examples that demonstrate the various
possiblities.

Firstly its SQL server.

The data currently is held in two tables. Both tables are linked by a common field named Computer_Idn (Primary key).

The first Table contains a field called 'Model' which
holds various values:

[u]Model</u>
1 Epson
2 H P
3 Canon
4 Canon
5 Epson
6 H P
7 Epson

The second table has a field 'Address' that contains the (IP) Address values:

[u]Address</u>
1 xxx.xxx.104.xxx
2 xxx.xxx.105.xxx
3 xxx.xxx.106.xxx
4 xxx.xxx.104.xxx
5 xxx.xxx.104.xxx
6 xxx.xxx.105.xxx
7 xxx.xxx.106.xxx

I need to display the 'Model' column. I also need to search on the ninth, tenth and eleventh characters in the 'Address' column.

If the 3 characters in this field = 104, I need to display this as 'AA' (105 = 'BB' and 106 = 'CC'). I also need to count the number of occurences of each 'AA' per model (The numerics in the above table examples will be the linked 'Computer_Idn')

So, my output should be something like:

[u]Address</u> [u]Model</u> [u]Model_Count</u>
AA Epson 2
AA Canon 1
BB H P 2
CC Canon 1
CC Epson 1


Then export the results (to Crystal or csv formats).

Hope this explains...

Thanks,


Neal

A Northern Soul

Try this query:

SELECT A.Model, CASE SUBSTRING(B.Address, 9, 3) WHEN '104' THEN 'AA'
WHEN '105' THEN 'BB' WHEN '106' THEN 'CC' END AS Address, COUNT(*)
FROM Table1 A INNER JOIN Table2 B
ON A.Computer_Idn = B.Computer_Idn
GROUP BY A.Model, CASE SUBSTRING(B.Address, 9, 3) WHEN '104' THEN 'AA'
WHEN '105' THEN 'BB' WHEN '106' THEN 'CC' END AS Address,

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

Many thanks for the tip.

Neal

A Northern Soul