Thread: Query Problem
View Single Post
  #6 (permalink)  
Old October 22nd, 2003, 02:36 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

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