Showing posts with label item. Show all posts
Showing posts with label item. Show all posts

Thursday, March 22, 2012

Changing a null to another value in select?

Hi all,
I have a result set coming back with one item that is null, because I am
trying to sort by this column this result is always at the top of the result
set, however I need them to be at the bottom of the result set, I cant
change the ordering because then the other results will no longer be
alphabetically sorted. Is there a way to change a 'null' to something else
if it is returned in a result set? My hope was perhaps to set it to be 10
Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
happens) be at the bottom?
Possible?
Regards
RobSelect isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hope this helps
Chris
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:4BbJd.17892$GG1.5654@.text.news.blueyonder.co.uk...
> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the
> result
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something
> else
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>|||"Chris, Master of All Things Insignificant" wrote ...

> Select isnull(mycolumn, 'zzzzzzzzzz') from MyTable
Hi Chris,
Thanks for the reply. I managed something similar myself, here's my view:
SELECT '1' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '0'
UNION ALL
SELECT '1' AS ViewOrder1, '2' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, c.SectionName, c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE b.SectionID IS NOT NULL AND b.SectionID <> '0' AND a.NGID <> '0'
AND b.NGIsNoGroup = '1'
UNION ALL
SELECT '3' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID <> '0'
UNION ALL
SELECT '4' AS ViewOrder1, '1' AS ViewOrder2, a.PageID, a.NGID,
a.PageName, a.PageKeywords, a.PageDisplayOrder, a.PageFooterDisplayOrder,
a.PageContent, a.PageIsLive, a.PageIsDefault,
a.PageIsStatic, a.PageIsExternal, a.PageIsKB, a.PageIsSearchResults,
a.PageIsNews,
a.PageIsPromoted, a.PageIsExcludedFromNavigation,
a.PageIsIncludedInFooter, a.PageURL, a.DisplayPrint, a.DisplayEmail,
a.DateCreated,
a.DateLastUpdated, b.NGName, b.NGDescription,
b.NGDisplayOrder, b.SectionID, 'ZZZZZZZZZZ', c.SectionDesc,
c.SectionDisplayOrder,
c.SectionPlacement, d .WebsiteID, d .WebsiteName,
e.Forename + ' ' + e.Surname AS PageCreatedBy,
f.Forename + ' ' + f.Surname AS PageLastUpdatedBy
FROM tblPages AS a LEFT JOIN
tblNavigationGroups AS b ON a.NGID = b.NGID LEFT JOIN
tblSections AS c ON b.SectionID = c.SectionID LEFT
JOIN
tblWebsites AS d ON c.WebsiteID = d .WebsiteID LEFT
JOIN
tblUsers AS e ON a.CreatedBy = e.UserID LEFT JOIN
tblUsers AS f ON a.LastUpdatedBy = f.UserID
WHERE (b.SectionID IS NULL OR
b.SectionID = '0') AND a.NGID = '0'
As you can see the last 2 sections of this (where viewOrder1 is either 3 or
4 etc) I have inserted the 'ZZZZZZZZZZ' as the section name...
This does work, but feels a bit flakey...if someone was to actually create a
'section' in my application called 'ZZZZZZZZZZZ' it would obviously cause
problems...
I originally had in my ASP code 4 statements which went off and got the
respective results for each case, but I thought making just one hit would be
better, now I face a different problem...
Any ideas anyone?
Regards
Rob|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
customerid,
companyname,
region
from
customers
order by
case when region is null then 1 else 0 end asc,
customerid
go
AMB
"Rob Meade" wrote:

> Hi all,
> I have a result set coming back with one item that is null, because I am
> trying to sort by this column this result is always at the top of the resu
lt
> set, however I need them to be at the bottom of the result set, I cant
> change the ordering because then the other results will no longer be
> alphabetically sorted. Is there a way to change a 'null' to something els
e
> if it is returned in a result set? My hope was perhaps to set it to be 10
> Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
> happens) be at the bottom?
> Possible?
> Regards
> Rob
>
>|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.

> use northwind
(wishing now I'd not deleted that recently)

> order by
> case when region is null then 1 else 0 end asc,
> customerid
That looks like what I want, I think, I cant quite work out the 1 / 0 thing
though - whats that actually going to do - in words etc...
ie, does that make region = 1 if it = null, and = 0 if its not null?
Thanks for the reply.
Regards
Rob|||"Alejandro Mesa" wrote ...

> Use a CASE expression in the ORDER BY clause.
>
[snip]
Hi Alejandro,
I was unable to get it to work in the ORDER BY clause, but your reply did
make me go and investigate the use of CASE expressions, I've now popped it
in the SELECT statement and its working perfectly.
Thank you.
Regards
Rob|||Rob,
ORDER BY
CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
itemColumn
Steve Kass
Drew University
Rob Meade wrote:

>Hi all,
>I have a result set coming back with one item that is null, because I am
>trying to sort by this column this result is always at the top of the resul
t
>set, however I need them to be at the bottom of the result set, I cant
>change the ordering because then the other results will no longer be
>alphabetically sorted. Is there a way to change a 'null' to something else
>if it is returned in a result set? My hope was perhaps to set it to be 10
>Z's (ie, zzzzzzzzzz) so that this would always (unless a REAL oddity
>happens) be at the bottom?
>Possible?
>Regards
>Rob
>
>|||"Steve Kass" wrote ...

> ORDER BY
> CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END,
> itemColumn
Hi Steve,
Could you explain to me what the 1 and 0 are/do please?
Regards
Rob|||Imagine that you put this in the SELECT list instead:
SELECT ... CASE WHEN itemColumn IS NULL THEN 1 ELSE 0 END AS sortcol
For a row with NULL in itemColumn, the expression will return the value 1. I
f there is a non-null
value in itemColumn, the expression will return the value 0. So, say you hav
e some rows (sortcol is
the one produced by above expression)
ItemColumn SortCol
23 0
NULL 1
1 0
NULL 1
Now, imagine that you do:
ORDER BY SortCol, ItemColumn
Get it?
The "short cut" Steve did is to put the expression in the ORDER BY clause in
stead of in the SELECT
clause. Logically, the columns didn't "materialize" before the sort operatio
n...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Rob Meade" <robb.meade@.NO-SPAM.kingswoodweb.net> wrote in message
news:8roJd.18310$GG1.8683@.text.news.blueyonder.co.uk...
> "Steve Kass" wrote ...
>
> Hi Steve,
> Could you explain to me what the 1 and 0 are/do please?
> Regards
> Rob
>|||"Tibor Karaszi" wrote ...

> ItemColumn SortCol
> 23 0
> NULL 1
> 1 0
> NULL 1
> Now, imagine that you do:
> ORDER BY SortCol, ItemColumn
> Get it?
> The "short cut" Steve did is to put the expression in the ORDER BY clause
instead of in the SELECT
> clause. Logically, the columns didn't "materialize" before the sort
operation...
Hi Tibor,
Thanks for your reply...
I get it now, the 1 / 0 were for the ordering etc, as where I was using
1,2,3,4 previously...When I first looked at it I was wondering whether they
could only 'ever' be those values or something - its not something I've come
across/used before..
Thanks for the explanation.
Regards
Rob

Monday, March 19, 2012

changes log

I want to keep a simple table that logs each time an item in inventory is changed.

Here is the table I came up with thoe track this:

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
CHANGES_MADE

I would like to capture the name of each field which is changed. I'm sure other people have implemented something like this before, can anyone please give me some advice on how to capture this, when and where?I have found it more helpful to log the specific field name, the old value and the new value, as opposed to a "CHANGES_MADE" column which would probably have a bunch of stuff jumbled together.

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
FIELDNAME
OLD_VALUE
NEW_VALUE

Currently, we have a trigger doing the insert into the log table. This seemed to be the logical place to do it, since every change would be captured regardless of what method would be used to make the change.

However, we are considering moving the logging code into a stored procedure and then calling this stored procedure from the different stored procedures which update the inventory table. One of the reasons among others is the needed ability to capture the user ID.

Terri|||Thanks a lot for the response.

I am still wondering how specifically you determined which field was changed? How does the trigger get the values?

I have not used triggers much because in my classes (I am not working at a co-op), they always told me that triggers were buggy and undependable, therefore use stored procedures whenever possible.|||


if update(<columnname>)

tells you if a particular column has been modified..

HTH|||Wow I never knew you could do that, that is awesome!|||::I have not used triggers much because in my classes (I am not working at a co-op), they
::always told me that triggers were buggy and undependable, therefore use stored
::procedures whenever possible.

i have designed an inventory maanegment system at my work palce. we have 9-10 branch offices at various locations within 10 mi frm our main office. every time the branch offices make a sale, their inventory goes down (obviously)...i have used triggers to create purchase orders anytime their inventory goes down a certain level ( as set in the Re Order Level) and it automatically creates a purchase order in another table ( for the Re-Order Quantity)..so the manager (or admin) from the main branch can always know the xact inventory level at each branch offices and ship them the inventory they need..the branch offices need not even call the main office "hey we need some inventory"..'coz its already on the way.

all the triggers work perfectly in my case..
the disadv with triggers is tht they are not under your control..they are fired automatically..so you should be pretty careful with the code on when to fire them..
stored procs on the other hand, have to ( can) be called manually...they are not really a replacement for each other..each has its own situations..

HTH|||Okay - sorry about the late reply, I was out sick.

I have decided to use a trigger but I am still not understanding how to get all the data from the trigger such as . .

CHANGES_LOG
-------
ITEM_NAME
SERIAL_NUM
_DATE
USER_ID
FIELDNAME
OLD_VALUE
NEW_VALUE

I know how to get FIELDNAME using IF UPDATE as above and I can get _DATE using GETDATE() but how to I determine the rest of it from the trigger?
Can someone help?|||This is what I am suggesting:

CHANGES_LOG
-------
ITEM_NAME --> this should be the primary key --> inserted.item_name
SERIAL_NUM --> not sure what your intention is here
_DATE --> GETDATE()
USER_ID --> ay, there's the rub
FIELDNAME --> if the test for IF UPDATE('fieldname') is true, that's the fieldname
OLD_VALUE --> deleted.fieldname
NEW_VALUE --> inserted.fieldname

If you are looking for the User ID from your application's users credentials, you will not be able to get to this information. The best you can do is to have a USER_ID column in your table and make sure to update this with your calling stored procedure. You would then use inserted.USER_ID to capture the current USER_ID on the record, which may or may not be the user who made the update.

You can also use SUSER_SNAME(), which will give you the Windows authenticated user's name.

So, what I am saying is you might consider having an "SUSER" column as well as a "USER_ID" column. The SUSER column will contain Windows user name, and the USER_ID column will contain the USER_ID that was in the record at the time it was updated.

Terri|||OK that helped a lot but I am wondering. .

inserted.item_name gives you the item_name of the item which caused the trigger right? if the trigger therefore was called by UPDATE then you would use updated.item_name?

You brought up a good point, I probably don't need to store the serial num because I could look it up from the other table, with the primary key - unless that item was deleted. But I guess that is a whole other issue. I just know that the users want to be able to view the serial number of an item that was deleted or updated.

I am using windows authentication for my application anyway so wouldn't SUSER_SNAME() be what I need? I think it will. Because I was thinking that USER_ID would capture some kind of SQL user ID and that is not what I want, that does identify individuals.|||OK I think I have got it. Should this work?


CREATE TRIGGER ROUTERS_Trigger1
ON dbo.ROUTERS
FOR UPDATE
AS
DECLARE @.TEMP AS nvarchar(50)
IF UPDATE (ROUTER_NAME)
BEGIN
SET @.TEMP = 'ROUTER_NAME'
END
IF UPDATE (SERIAL_NUM)
BEGIN
SET @.TEMP = 'SERIAL_NUM'
END
IF UPDATE (MODEL)
BEGIN
SET @.TEMP = 'MODEL'
END
IF UPDATE (IOS_VER)
BEGIN
SET @.TEMP = 'IOS_VER'
END
IF UPDATE (IOS_BASED)
BEGIN
SET @.TEMP = 'IOS_BASED'
END

INSERT INTO CHANGES_LOG (ITEM_NAME, _DATE, [USER_ID], FIELDNAME, OLD_VALUE, NEW_VALUE)
VALUES (inserted.ITEM_NAME, GETDATE(), SUSER_SNAME(), @.TEMP, deleted.@.TEMP, inserted.@.TEMP)

Also what happens if several fields are changed? If there a row in the Changes_log for each one? Do I need to use a different trigger for logging INSERTS and UPDATES to Routers table?|||You should read up on triggers in Books Online. This should help clarify a few things for you.

In a trigger, the "deleted" table holds the values of the data as they were BEFORE the update happened. The "inserted" table holds the values of the data AFTER the updated. There is no "updated" table.

It sounds like SUSER_SNAME() will do the trick for you.

I am not certain of the purpose of your ITEM_NAME and SERIAL_NUM columns so I can't really comment on that. In our inventory management system, we do not physically delete inventory records; we mark them as "inactive". So we do not have the problem of disappearing items.

Terri|||When I try to use the code I posted above, I get an error for trying to insert the value inserter.ROUTER_NAME (which is the primary key of ROUTERS table) I am using visual studio and when I try to save the trigger it says:

ADO error: The Name 'ROUTER_NAME' is not permitted in this context. Only constants, expressions, or variables are allowed here. Column Names are not permitted.

Thanks!!|||you prbly might have to take those values into some variables...
ex:


select @.instemp=inserted.<colname>from inserted
select @.deltemp=deleted.<colname> from deleted

and use these variables in your insert stmt..

HTH|||Okay sorry I haven't been working on this lately. I was wrong, the SUSER_SNAME is not working for me because I am connecting using a SQL user_id and password and it always gives me that User ID, not the windows one. When I tryed to connected to SQL using windows auth. I think it only connects with one generic ASPNET user?!?

So I guess I will not be able to capture the user ID that way. . . .

Friday, February 24, 2012

Change Sort_Order up/down SP problem

Hi,

I'm having a problem with some stored procedures which is supposed to change the position of an item in a list to move it up or down. I've written two stored procedures - one for up or down - but they work intermittenly or not at all and I'm not sure why. Could someone help me out? I'm sure my logic is messed up somewhere and probably more complicated than it needs to be.

Thanks!

My table structure:

SpecialNeedID (PK, int)
SpecialNeed (varchar)
Sort_Order (int)

My stored procedures:

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order-10 FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order+10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
END

==================================================

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsDown]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order+10 FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order-10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
ENDWell, that is messy. For one thing, you've got some TOP statements in there without ORDER BYs, making them meaningless or even misleading.

Explain what you are trying to do. It looks like you are trying to swap the places of two entries 10 records apart, which doesn't make sense to me. I'd think you would want to be swapping the places of neighboring records.

Maybe some sample data would help clarify. Regardless, I think the final solution which be much simpler than what you have come up with so far.|||Thanks for the reply. Here's some sample data:

SpecialNeedID SpecialNeed Sort_Order
---------------
1 Blind 10
2 Deaf 20
3 Autistic 30

These items are arranged in a list - the last column on the list are two buttons: up and down. Using these, the admins should be able to move items up and down to dictate the order they appear in drop-down lists.|||OK, why are you incrementing the sort order by 10? That certainly leaves open a lot of opportunities for bad data leading to unpredictable results from your resorting procedure. Why not just increment by 1 with integers?|||try this... assuming Sort_Order is numeric... need a check for the top one

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
( @.CurID int )
AS

declare @.PrevId int

set @.PrevId = (select top 1 SpecialNeedID from lkpSpecialNeeds where Sort_Order < (select Sort_Order from lkpSpecialNeeds where SpecialNeedID=@.CurId) order by Sort_Order desc)
update lkpSpecialNeeds set Sort_Order=Sort_Order-10 where SpecialNeedID = @.CurId
update lkpSpecialNeeds set Sort_Order=Sort_Order+10 where SpecialNeedID = @.PrevId|||try this... assuming Sort_Order is numeric... need a check for the top one

This worked perfectly, thanks so much!

Sunday, February 12, 2012

change of value at the time of insertion

Hai,
Here is a query that i am creating a table

create table test10 (item varchar(10),state bit)

now at the time of insertion i will enter like this

insert into test10 values ('book' ,1)

but at the time of display in the table i want

item state
book true

from above i want to ask that when i am going to insert 1 i want to
get true and 0 as false.I never enter true or false in the insert
command. please help me in coding.
thankyou,
lucky.

Replyis this homework, or a real world question? if real world, come up
with better examples.

thx.|||Hai doug,
This is baba what i have askd is not for fun really i
want to know wether i am going to get 1 as true in the output table it
is a request from my side i am new to the SQL i know u r having valuble
time i dont want to waste it if my query is good please give me the
answer. thankyou,
baba.|||Hi Lucky

A column of type bit can hold the values 0 or 1. Depending on the client
side programming language it may interpret 0 as false and 1 as true. For
example in VB.Net you could bind this to a boolean (logical) field in a
ADO.Net datatable.

Within T-sql you could say:

select item, case when state = 0 then 'false' else 'true' end as state1
from test10

But in this case, from an application's (such as one written in VB.Net)
point of view the 'true' or 'false' returned are character strings not
boolean (logical) values.

--
-Dick Christoph
"lucky" <j.v.s.s.baba@.gmail.com> wrote in message
news:1142488231.981434.27360@.p10g2000cwp.googlegro ups.com...
> Hai,
> Here is a query that i am creating a table
> create table test10 (item varchar(10),state bit)
>
> now at the time of insertion i will enter like this
>
> insert into test10 values ('book' ,1)
>
> but at the time of display in the table i want
>
> item state
> book true
>
> from above i want to ask that when i am going to insert 1 i want to
> get true and 0 as false.I never enter true or false in the insert
> command. please help me in coding.
> thankyou,
> lucky.
>
> Reply

change of value at the time of insertion

Hai,
Here is a query that i am creating a table
create table test10 (item varchar(10),state bit)
now at the time of insertion i will enter like this
insert into test10 values ('book' ,1)
but at the time of display in the table i want
item state
book true
from above i want to ask that when i am going to insert 1 i want to
get true and 0 as false.I never enter true or false in the insert
command. please help me in coding.
thankyou,
lucky.Hi
create table test10 (item varchar(10),state bit)
insert into test10 values ('book' ,1)
select item,case when state=1 then 'true' else 'false' end as state
from test10
drop table test10
"lucky" <j.v.s.s.baba@.gmail.com> wrote in message
news:1142488029.691844.64920@.j52g2000cwj.googlegroups.com...
> Hai,
> Here is a query that i am creating a table
> create table test10 (item varchar(10),state bit)
> now at the time of insertion i will enter like this
> insert into test10 values ('book' ,1)
>
> but at the time of display in the table i want
> item state
> book true
> from above i want to ask that when i am going to insert 1 i want to
> get true and 0 as false.I never enter true or false in the insert
> command. please help me in coding.
> thankyou,
> lucky.
>