Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 19th, 2003, 01:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Problem

I have data like this
Table Name’s DATA
data type is int

FromNode ToNode Volume
10011 10010 1353
10011 10012 47
10012 10018 243
10011 10012 50
10015 10014 377
10011 10016 976
10011 10018 0
10012 2755 1586
………… and so on……….. thousand of records in sql server table

How can retrieve the data from the above table
if last character of FROMNODE column is 1 and last character of TONODE column is 0 to display value 1353.

If last character of FROMNODE column is 1 and last character of TONODE column is 2 to display 47

If last character of FROMNODE column is 2 and last character of TONODE column is 8 to display 243

If last character of FROMNODE column is 1 and last character of TONODE column is 2 to display 50 and so on……

What programming query, function will use to retrieve the data from the above table ?
Last value of FROMNODE and TONODE column always between 1,2,3,4,5,6,7 or 8.

Please help

Mateen
Reply With Quote
  #2 (permalink)  
Old October 19th, 2003, 08:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm not sure I understand what you are looking for.

If what you are trying to do is to simply extract the last character from the 'FromNode' and 'ToNode' integer columns, then simply CAST the column to a varchar type and use the RIGHT function to return the rightmost resultant character, as:
Code:
SELECT RIGHT(CAST(FromNode as varchar),1),RIGHT(CAST(ToNode as varchar),1), Volume
FROM DATA 
WHERE ...
Or, equivalently, if you prefer to keep everything as an integer type, use the modulus operator, %, to get the remainder when dividing by 10 as
Code:
SELECT FromNode % 10, ToNode % 10, Volume ...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old October 20th, 2003, 03:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I can use any datatype, I want to Generate Report by the SQL Query.
By your query

select right(cast(fromnode as varchar),1) fromnode,right(cast(tonode as varchar),1) tonode,volume from emme

it retrieve last value of each column, it is ok, I want that if last value like this.

1 – 2 it means it is SBU
1 – 4 it means it is SBL
1 – 6 it means it is SBT
3 – 2 it means it is WBR
and so on…….

I try like this.

select FromNode, ToNode,
case
when FromNode%10=1 and ToNode%10=2 then SBU
when FromNode%10=1 and ToNode%10=4 then SBL
when FromNode%10=1 and ToNode%10=6 then SBT
when FromNode%10=3 and ToNode%10=2 then WBR
and so on………

end
from emme

but it give error
“ Invalid column name 'SBU' “

How can use the character string (SBU) in above condition ?

I have data like this (only three column)

FromNode ToNode Volume
10[u]01</u>1 10012 1353
10[u]12</u>1 10014 47
10[u]13</u>1 10016 243
10[u]14</u>3 10012 50
12[u]51</u>5 10014 377
10[u]81</u>2 10016 976

And third and fourth value of fromnode ie. which is bold and underline
01 it means it is IP-1
12 it means it is IP-9
13 it means it is IP-11
14 it means it is IP-12
51 it means it is IP-13
81 it means if is IP-14
and so on……

select substring(fromnode,3,2) IP
case
when IP=01 then IP-1
when IP=02 then IP-9
when IP=13 then IP-11
when IP=14 then IP-12
when IP=51 then IP-13
when IP=81 then IP-14
........
end case

from emme

how can use (display) IP in case condition ?

By the help of three column I manually prepare report like following. It is time consuming. I want to prepare query, so that
by the help of query I will prepare the report quickly. I have problem in generating Query.

I want to prepare report like following.

Intersection No. EAST BOUND WEST BOUND NORTH BOUND SOUTH BOUND
    EBU EBL EBT EBR WBU WBL WBT WBR NBU NBL NBT NBR SBU SBL SBT SBR
IP-1 0 454 1563 358 33 881 1310 53 431 679 1032 6 47 377 976 243
IP-9 17 846 764 14 15 645 686 334 58 487 1250 578 95 460 873 680
IP-10 4 696 974 339 14 666 886 437 10 638 749 319 49 530 801 471
IP-11 11 666 863 18 21 310 614 265 9 875 589 255 74 252 1017 279
IP-12 76 2002 162 61 1187 1276 211 1842 359
IP-12 From SB Into Tunnel 431
IP-13 68 124 926 446 9 209 271 2 114 1018 407 86 41 463 553 23
IP-14 44 435 505 97 2 581 337 365 8 615 741 152 278 656 474 87
IP-15 19 381 805 0 144 214 479 31 236 44 66 5 555 93 182
IP-16 0 297 345 221 7 484 255 79 0 322 472 481 99 276 298 132
IP-17 107 739 1488 463 13 809 849 521 54 1135 421 225 46 1128 388 5

I copy paste the sample report, it is Scatter, hope that you
will understand the idea of sample report.

How can Generate SQL Query for above report ?

Pleae help, (guide)?

Mateen



Quote:
quote:Originally posted by Jeff Mason
 I'm not sure I understand what you are looking for.

If what you are trying to do is to simply extract the last character from the 'FromNode' and 'ToNode' integer columns, then simply CAST the column to a varchar type and use the RIGHT function to return the rightmost resultant character, as:
Code:
SELECT RIGHT(CAST(FromNode as varchar),1),RIGHT(CAST(ToNode as varchar),1), Volume
FROM DATA 
WHERE ...
Or, equivalently, if you prefer to keep everything as an integer type, use the modulus operator, %, to get the remainder when dividing by 10 as
Code:
SELECT FromNode % 10, ToNode % 10, Volume ...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #4 (permalink)  
Old October 20th, 2003, 01:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I answered your specific original question, but you then changed the rules. :(

Your table design leaves a lot to be desired. You need to read up on database normalization, as your encoding of information within character positions of the table columns violates so-called first normal form. In addition, you seem intent on representing a lot of information procedurally, rather than expressing it explicitly in the data itself. For example, apparently character positions 3 and 4 of the 'fromnode' actually means an intersection number with a specific string representation depending on some unknown mapping between those character values and the intersection value itself (e.g. - '13' means 'IP-11'?? How did you know that?) You should represent that relationship explicitly somewhere.

In my opinion you need to stop what you are doing, put down your coding pencil and do some research, get some education and learn about some of these design principles, or you are going to get in a lot of trouble.

That having been said, what you are trying to do looks like a cross-tab query. SQL really isn't very good at doing this sort of thing, as this is really a reporting issue best handled at the client. You can do it in SQL, but I don't think you're going to like what needs to be done. I know I don't. :D

You will have to use repeated CASE expressions to get what you want (assuming I understand what you want). First, you'll have a CASE expression to provide the value for the 'Intersection No' column according to your mysterious mapping:
Code:
SELECT CASE SUBSTRING(fromnode,3,2)
    WHEN '01' THEN 'IP-1'
    WHEN '12' THEN 'IP-9'
...
then you'll need a CASE expression for each of the 8 columns. I can't figure out what your EASTBOUND, WESTBOUND, etc. mapping is so I'll just make something up. I'll assume EBU corresponds a value of 8 in the low order digits of both nodes, and I'll assume a 6 in the 'from' and 2 in the 'to' nodes means EBL, etc:
Code:
...
--EBU column
SUM(CASE WHEN fromnode%10=8 AND tonode%10=8 THEN volume ELSE 0 END) as EBU,
--EBT column
SUM(CASE WHEN fromnode%10=6 AND tonode%10=2 THEN volume ELSE 0 END) as EBT
...(continue on for the other 6 column definitions
GROUP BY SUBSTRING(fromnode,3,2)
the idea is that you sum either the volume column value or a 0 depending on whether the from and to node values correspond to the column value being generated.

You really should consider redesigning your database to explicitly state the intersection value relationships, and then you should also consider doing this sort of reporting via an appropriate client front-end.

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old October 21st, 2003, 07:32 AM
Authorized User
 
Join Date: Oct 2003
Location: Delhi, Delhi, India.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to SubodhKumar Send a message via Yahoo to SubodhKumar
Default

Hi mateen,
Try this query,


select FromNode,ToNode,volume,'value'=
    Case
        When right(fromNode,1)='1' and right(toNode,1)='0' Then '1353'
        When right(fromNode,1)='1' and right(toNode,1)='2' Then '47'
        When right(fromNode,1)='2' and right(toNode,1)='8' Then '243'
        --add more condition here
        --.........................
        else 'Last Character is not beetween 1 & 8'
    End
from Data --The Table name

Quote:
quote:Originally posted by mateenmohd
 I have data like this
Table Name’s DATA
data type is int

FromNode ToNode Volume
10011 10010 1353
10011 10012 47
10012 10018 243
10011 10012 50
10015 10014 377
10011 10016 976
10011 10018 0
10012 2755 1586
………… and so on……….. thousand of records in sql server table

How can retrieve the data from the above table
if last character of FROMNODE column is 1 and last character of TONODE column is 0 to display value 1353.

If last character of FROMNODE column is 1 and last character of TONODE column is 2 to display 47

If last character of FROMNODE column is 2 and last character of TONODE column is 8 to display 243

If last character of FROMNODE column is 1 and last character of TONODE column is 2 to display 50 and so on……

What programming query, function will use to retrieve the data from the above table ?
Last value of FROMNODE and TONODE column always between 1,2,3,4,5,6,7 or 8.

Please help

Mateen


Enjoy!
Subodh
Reply With Quote
  #6 (permalink)  
Old October 22nd, 2003, 02:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your nice comments.

by your help I success to generate the query.
to display separate records from the three colums.
like this.

select fromnode,tonode,volume,IP,fromnode2,tonode2,
case BOND when 'SBU' then 'SBU' else Null end as SBU,
case BOND when 'SBL' then 'SBL' else Null end as SBL,
case BOND when 'SBT' then 'SBT' else Null end as SBT,
case BOND when 'SBR' then 'SBR' else Null end as SBR,
case BOND when 'WBR' then 'WBR' else Null end as WBR,
case BOND when 'WBU' then 'WBU' else Null end as WBU,
case BOND when 'WBL' then 'WBL' else Null end as WBL,
case BOND when 'WBT' then 'WBT' else Null end as WBT,
case BOND when 'NBT' then 'NBT' else Null end as NBT,
case BOND when 'NBR' then 'NBR' else Null end as NBR,
case BOND when 'NBU' then 'NBU' else Null end as NBU,
case BOND when 'NBL' then 'NBL' else Null end as NBL,
case BOND when 'NBL' then 'NBL' else Null end as NBL,
case BOND when 'EBL' then 'EBL' else Null end as EBL,
case BOND when 'EBT' then 'EBT' else Null end as EBT,
case BOND when 'EBR' then 'EBR' else Null end as EBR,
case BOND when 'EBU' then 'EBU' else Null end as EBU,
case BOND when 'IP-1' then 'IP-1' else Null end as [IP-1],
case BOND when 'IP-41' then 'IP-41' else Null end as [IP-41],
case BOND when 'IP-56' then 'IP-56' else Null end as [IP-56],
case BOND when 'IP-12' then 'IP-12' else Null end as [IP-12],
case BOND when 'IP-9' then 'IP-9' else Null end as [IP-9],
case BOND when 'EXITSOUTHBOND' then 'EXITSOUTHBOND'
else Null end
from
(select fromnode,tonode,volume,substring(fromnode,3,2) IP,
fromnode % 10 fromnode2, tonode % 10 tonode2,
case
when FromNode%10=1 and ToNode%10=2 then 'SBU'
when FromNode%10=1 and ToNode%10=4 then 'SBL'
when FromNode%10=1 and ToNode%10=6 then 'SBT'
when FromNode%10=1 and ToNode%10=8 then 'SBR'
when FromNode%10=3 and ToNode%10=2 then 'WBR'
when FromNode%10=3 and ToNode%10=4 then 'WBU'
when FromNode%10=3 and ToNode%10=6 then 'WBL'
when FromNode%10=3 and ToNode%10=8 then 'WBT'
when FromNode%10=5 and ToNode%10=2 then 'NBT'
when FromNode%10=5 and ToNode%10=4 then 'NBR'
when FromNode%10=5 and ToNode%10=6 then 'NBU'
when FromNode%10=5 and ToNode%10=8 then 'NBL'
when FromNode%10=7 and ToNode%10=2 then 'EBL'
when FromNode%10=7 and ToNode%10=4 then 'EBT'
when FromNode%10=7 and ToNode%10=6 then 'EBR'
when FromNode%10=7 and ToNode%10=8 then 'EBU'
when substring(fromnode,3,2)=01 then 'IP-1'
when substring(fromnode,3,2)=41 then 'IP-41'
when substring(fromnode,3,2)=56 then 'IP-56'
when substring(fromnode,3,2)=12 then 'IP-12'
when substring(fromnode,3,2)=09 then 'IP-9'
when FromNode%10=7 then 'Eastbound in Excluding Right'
end BOND
from emme) emme2
ORDER BY IP

I will follow your tips.
regards.

Mateen





Quote:
quote:Originally posted by Jeff Mason
 I answered your specific original question, but you then changed the rules. :(

Your table design leaves a lot to be desired. You need to read up on database normalization, as your encoding of information within character positions of the table columns violates so-called first normal form. In addition, you seem intent on representing a lot of information procedurally, rather than expressing it explicitly in the data itself. For example, apparently character positions 3 and 4 of the 'fromnode' actually means an intersection number with a specific string representation depending on some unknown mapping between those character values and the intersection value itself (e.g. - '13' means 'IP-11'?? How did you know that?) You should represent that relationship explicitly somewhere.

In my opinion you need to stop what you are doing, put down your coding pencil and do some research, get some education and learn about some of these design principles, or you are going to get in a lot of trouble.

That having been said, what you are trying to do looks like a cross-tab query. SQL really isn't very good at doing this sort of thing, as this is really a reporting issue best handled at the client. You can do it in SQL, but I don't think you're going to like what needs to be done. I know I don't. :D

You will have to use repeated CASE expressions to get what you want (assuming I understand what you want). First, you'll have a CASE expression to provide the value for the 'Intersection No' column according to your mysterious mapping:
Code:
SELECT CASE SUBSTRING(fromnode,3,2)
    WHEN '01' THEN 'IP-1'
    WHEN '12' THEN 'IP-9'
...
then you'll need a CASE expression for each of the 8 columns. I can't figure out what your EASTBOUND, WESTBOUND, etc. mapping is so I'll just make something up. I'll assume EBU corresponds a value of 8 in the low order digits of both nodes, and I'll assume a 6 in the 'from' and 2 in the 'to' nodes means EBL, etc:
Code:
...
--EBU column
SUM(CASE WHEN fromnode%10=8 AND tonode%10=8 THEN volume ELSE 0 END) as EBU,
--EBT column
SUM(CASE WHEN fromnode%10=6 AND tonode%10=2 THEN volume ELSE 0 END) as EBT
...(continue on for the other 6 column definitions
GROUP BY SUBSTRING(fromnode,3,2)
the idea is that you sum either the volume column value or a 0 depending on whether the from and to node values correspond to the column value being generated.

You really should consider redesigning your database to explicitly state the intersection value relationships, and then you should also consider doing this sort of reporting via an appropriate client front-end.

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM



All times are GMT -4. The time now is 06:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.