473,700 Members | 2,884 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------------------------------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.
Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

Muhd
Jul 20 '05 #1
5 2363
Muhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy
"Muhd" <ea*@joes.com > wrote in message news:7MZ4d.9108 4$%S.84951@pd7t w2no...
Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------------------------------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.
Now what i'm trying to do during my select statement is select all the
rows that belong to company_id 5 but if any of the rows round value
contains the text "WERT" convert that text into just a "--" for
presentation purposes, but still select that row. I can't seem to figure
out how i would transform the text in the select statement? My immediate
thought was substring / replace but i would need to combine it with an if
else statement which i've no idea how to make work in a select (sub-query
maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
the returned data within the client application before presenting?

Any help, as always, would be greatly appreciated.

Muhd

Jul 20 '05 #2
Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <ea*@joes.com > wrote in message news:7MZ4d.9108 4$%S.84951@pd7t w2no...
Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------------------------------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.
Now what i'm trying to do during my select statement is select all the
rows that belong to company_id 5 but if any of the rows round value
contains the text "WERT" convert that text into just a "--" for
presentation purposes, but still select that row. I can't seem to figure
out how i would transform the text in the select statement? My immediate
thought was substring / replace but i would need to combine it with an if
else statement which i've no idea how to make work in a select (sub-query
maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
the returned data within the client application before presenting?

Any help, as always, would be greatly appreciated.

Muhd

Jul 20 '05 #3
While the front end is a very good place for presentation or value decoding
....
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <ea*@joes.com > wrote in message

news:7MZ4d.9108 4$%S.84951@pd7t w2no...
Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------------------------------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah. 3 5 WERT More generic blah blah.
Now what i'm trying to do during my select statement is select all the
rows that belong to company_id 5 but if any of the rows round value
contains the text "WERT" convert that text into just a "--" for
presentation purposes, but still select that row. I can't seem to figure out how i would transform the text in the select statement? My immediate thought was substring / replace but i would need to combine it with an if else statement which i've no idea how to make work in a select (sub-query maybe?) statement. Is this possible? Perhaps i'm stuck iterating through the returned data within the client application before presenting?

Any help, as always, would be greatly appreciated.

Muhd


Jul 20 '05 #4
Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotm ail.com> wrote in message
news:4C******** ************@bg tnsc04-news.ops.worldn et.att.net...
While the front end is a very good place for presentation or value
decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <ea*@joes.com > wrote in message

news:7MZ4d.9108 4$%S.84951@pd7t w2no...
> Hey all,
> I have a basic table that looks something like this.
>
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
>
> It has a few rows of data that look like this:
>
> Identity Company_ID Round Details
> --------------------------------------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah. > 3 5 WERT More generic blah blah.
>
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure > out how i would transform the text in the select statement? My immediate > thought was substring / replace but i would need to combine it with an if > else statement which i've no idea how to make work in a select (sub-query > maybe?) statement. Is this possible? Perhaps i'm stuck iterating through > the returned data within the client application before presenting?
>
> Any help, as always, would be greatly appreciated.
>
> Muhd
>



Jul 20 '05 #5
Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_ 1_9@y_a_h_o_o_. _c_o_m> wrote in message
news:c1******** **********@news svr33.news.prod igy.com...
Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <ra*******@hotm ail.com> wrote in message
news:4C******** ************@bg tnsc04-news.ops.worldn et.att.net...
While the front end is a very good place for presentation or value
decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to
the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message
news:pf******** *********@newss vr12.news.prodi gy.com...
Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally
best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <ea*@joes.com > wrote in message

news:7MZ4d.9108 4$%S.84951@pd7t w2no...
> Hey all,
> I have a basic table that looks something like this.
>
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
>
> It has a few rows of data that look like this:
>
> Identity Company_ID Round Details
> --------------------------------------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah

blah.
> 3 5 WERT More generic blah blah.
>
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to

figure
> out how i would transform the text in the select statement? My

immediate
> thought was substring / replace but i would need to combine it with an

if
> else statement which i've no idea how to make work in a select

(sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating

through
> the returned data within the client application before presenting?
>
> Any help, as always, would be greatly appreciated.
>
> Muhd
>



Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2265
by: lawrence | last post by:
I had some code that worked fine for several weeks, and then yesterday it stopped working. I'm not sure what I did. Nor can I make out why it isn't working. I'm running a query that should return 3 items from the database. I then get a count of the return, which correctly tells me that I've got 3 items. I then go into a for loop which I expect to loop 3 times and print out the 3 items. Here is where things get strange: it loops 3 times and...
2
2507
by: Paolo Pignatelli | last post by:
I am trying to get an output/file like this (below) in an XML file (MyXmlFile.xml) (which I will use for a slide show) -- <gallery timer="3" order="sequential" fadetime="2" looping="yes" xpos="0" ypos="0"> <image path="images2/Test1.jpg" />
10
15373
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have identical field names. The queries select a subset of the fields, so "Select *" is not really an option. Is there an easy way to change the source of a query, either in the design grid or SQL display? I suppose I could copy the SQL into WordPad...
7
2966
by: Sakharam Phapale | last post by:
Hi All, How to preserve the old font properties while changing new one? I posted same question 2 months back, but I had very small time then. eg. "Shopping for" is a text in RichTextBox and already formatted as "Shopping" ---Bold "for" -----Regular Now I want to underline whole text by preserving old style i.e. Bold and
7
14807
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I want to look at that data and filter it based on what is in it. I know that this could have been done with data sets and data views in asp.net 1.1 but how is this done now in asp.net 2.0?
32
3680
by: deko | last post by:
I have a popup form with a textbox that is bound to a memo field. I've been warned about memo fields so I'm wondering if I should use this code. Is there any risk with changing the form's RecordSource like this? Am I asking for trouble doing this with a memo field? Thanks in advance. Private Sub cmdNextNote_Click() Dim lngNid As Long If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
6
8161
Cintury
by: Cintury | last post by:
Hi all, I've developed a mobile application for windows mobile 5.0 that has been in use for a while (1 year and a couple of months). It was developed in visual studios 2005 with a back-end sql server mobile ce database. Until recently I was synching everything thru a com port serial cable. The devices would connect to the computer thru activesync and are able to acquire an internet connection. The sync for the program occurs thru a website...
15
4625
by: cephal0n | last post by:
I have a technical Date problem that's really difficult for me, I have a "custom made" Date format MM.DD.YY this is actually extracted from SAP and theirs no other format option offered such as ShortDate, LongDate etc. so now I making a sql query that must convert SAP date to a date Format that is recognizable to MS Access and I choose the ShortDate MM/DD/YY using ADO. I read about the Format() command using ADO online and experiment on it and...
6
9036
by: and1 | last post by:
hello .. hmm currently i'm working on vba in excel, apparently i use ADO to extract a table data from access to excel and it works fine. the problem is when i use the extracted data to create a chart using vba and it doesn't seem to display the data properly.. it is due to the text format when the data is extracted to excel.. how can i change it to number format when the table of data is extracted .. Here's the code: Dim cnt As New...
0
8709
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8952
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8909
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7791
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5894
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4395
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4649
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2018
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.