Thread: Query Problem
View Single Post
  #3 (permalink)  
Old October 20th, 2003, 03:12 AM
mateenmohd mateenmohd is offline
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