Database Discussion Boards

Re: SQL bit to bool conversion

5fingers17-Aug-11 0:57

5fingers

17-Aug-11 0:57

ASM

1. create table test(name varchar(20),status bit not null)

assume status has value 0 or 1. then fill to a dataset.

Bind a gridview directly to dataset.

Column status shows true or false on gridview which is ok.

To retrieve the status value from gridviewrow.cells[1].text return empty string.

Confirm.

I only read newbie introductory dummy books.

Re: SQL bit to bool conversion

chriselst16-Aug-11 0:40

chriselst

16-Aug-11 0:40

See here[^]

Every man can tell how many goats or sheep he possesses, but not how many friends.

Re: SQL bit to bool conversion

CPallini16-Aug-11 0:45

CPallini

16-Aug-11 0:45

See here[^].

If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.

This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke

[My articles]

Re: SQL bit to bool conversion

PompeyBoy316-Aug-11 0:45

PompeyBoy3

16-Aug-11 0:45

My middle one, to be exact.

Re: SQL bit to bool conversion

Richard MacCutchan16-Aug-11 1:15

Richard MacCutchan

16-Aug-11 1:15

Unrequited desire is character building. OriginalGriff

Re: SQL bit to bool conversion

5fingers17-Aug-11 0:56

5fingers

17-Aug-11 0:56

ASM

1. create table test(name varchar(20),status bit not null)

assume status has value 0 or 1. then fill to a dataset.

Bind a gridview directly to dataset.

Column status shows true or false on gridview which is ok.

To retrieve the status value from gridviewrow.cells[1].text return empty string.

Confirm.

I only read newbie introductory dummy books.

Re: SQL bit to bool conversion

Richard MacCutchan17-Aug-11 1:27

Richard MacCutchan

17-Aug-11 1:27

You need to spend some time with your debugger to figure out exactly where this is going wrong and why. Check all the possible values in your objects at each stage, in particular check whether gridviewrow.cells[1] actually has a text value that can be returned.

Unrequited desire is character building. OriginalGriff

Re: SQL bit to bool conversion

Smithers-Jones16-Aug-11 1:03

Smithers-Jones

16-Aug-11 1:03

Wow, you are long enough here on CP to know the rules, but no, you have to misbehave again and again, and then you complain why you got constantly downvoted? Thumbs Down | :thumbsdown:

"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)

Re: SQL bit to bool conversion

5fingers17-Aug-11 0:56

5fingers

17-Aug-11 0:56

1. create table test(name varchar(20),status bit not null)

assume status has value 0 or 1. then fill to a dataset.

Bind a gridview directly to dataset.

Column status shows true or false on gridview which is ok.

To retrieve the status value from gridviewrow.cells[1].text return empty string.

Confirm.

I only read newbie introductory dummy books.

Local Variable Ntext Probelm sql 2005

md_refay14-Aug-11 1:10

md_refay

14-Aug-11 1:10

iam having ntext column in my database
that store uniqueidentifier value seprated with ,
so i create function to split this ntext value in local table which each row have 1 uniqueidentifer value
but i have error when i try to create local variable of type ntext

The text, ntext, and image data types are invalid for local variables
i need any help to solve my problem

md_refay

cross-post

Wendelius14-Aug-11 1:45

Wendelius

14-Aug-11 1:45

You already asked this question over here: Local Variable Ntext Probelm[^]. Don't post the same question multiple times on different forums.

The need to optimize rises from a bad design.My articles[^]

Check Different Select query result

Naunt11-Aug-11 18:17

Naunt

11-Aug-11 18:17

Dear all,
Please suggest me to write shortest and effective query in Storeprocedure.
To assign value to @Color, there will be different select queries with different conditions, My query sample will be as the following,

SQL

Select @result=count(*) from table where condition1
IF @result = 20
    Begin
        @Color='Red Color'
    End
Else
    Begin
        select @result=count(*) from table where condition2
        IF @result=20
        Begin
            @Color='Blue Color'
        End
        Else
        Begin
            select @result=count(*) from table where condition3
            IF @result>0
            Begin
                @Color='Blue Color'
            End
            Else
            Begin
                Select  @result=count(*) from table where Condition4
                IF @result>0
                Begin
                    @Color='Blue Color'
                End
                Else
                Begin
                    Select @result=count(*) from where Condition5
                    IF @result=20
                    Begin
                        @Color='Green Color'
                    End
                    Else
                    Begin
                        Select @result=count(*) from where Condition6
                        IF @result>0
                        Begin
                            @Color='Yellow Color'
                        End
                        Else
                        Begin
                            @Color='Orange Color'
                        End
                    End
                End
            End
        End
    End

Thanks and best regards

Re: Check Different Select query result

Mycroft Holmes11-Aug-11 19:59

Mycroft Holmes

11-Aug-11 19:59

You should let us know what database you are using.

Look into CASE if you are using SQL Server

Never underestimate the power of human stupidity
RAH

Re: Check Different Select query result

Naunt11-Aug-11 21:55

Naunt

11-Aug-11 21:55

Thank you for your reply.

So far what I've found CASE are use like below

SQL

SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END

SQL

UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
         ELSE (VacationHours + 20.00)
       END
    )

But, I have no idea how to use in my query.
Could you please give me some samples by using my scenarios?

Thanks and best regards

Re: Check Different Select query result

Mycroft Holmes11-Aug-11 22:54

Mycroft Holmes

11-Aug-11 22:54

Using your second format you basically take your where clauses and place them between WHEN and THEN

UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN whereclause1 THEN VacationHours + 40
         WHEN whereclause2 THEN VacationHours + 10
         WHEN whereclause3 THEN VacationHours + 19
         WHEN whereclause4 THEN VacationHours + 11
       END
    )

The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible. Good luck

Never underestimate the power of human stupidity
RAH

Re: Check Different Select query result

Wendelius11-Aug-11 23:17

Wendelius

11-Aug-11 23:17

Mycroft Holmes wrote:
The complexity of the where clauses it going to drive you nuts, sequencing them correctly will be a pain and debugging is going to be bloody horrible

This is very true if the where clauses are even a bit complex...

The need to optimize rises from a bad design.My articles[^]

Re: Check Different Select query result

Naunt12-Aug-11 0:46

Naunt

12-Aug-11 0:46

Absolutely right Thumbs Up | :thumbsup:

I'm at that situation now
Cry | :((

Re: Check Different Select query result

Wendelius12-Aug-11 0:55

Wendelius

12-Aug-11 0:55

Instead of trying to combine everything to a giant statement, could you go the other way: brake it into pieces.

Don't know the whole situation, but could you for example create small, separate functions that fetch the desired info and in the 'main' logic use these functions (or procedures if you like). When you create the parameterized functions I would guess that at some point you notice the similarities between conditions if there are any so perhaps the amount of functions won't be as many as you now have different selects.

Also during the process you may find different kind of options to build the desired output that are now hard to see.

In any case, best of luck!

The need to optimize rises from a bad design.My articles[^]

Re: Check Different Select query result

Naunt12-Aug-11 1:09

Naunt

12-Aug-11 1:09

I hope this is a great idea.
But to be honest I've never been used function and also not familiar.
I'm gonna learn it on my Weekend.
Any article or site you want to recommend? Smile | :)

Thanks again and have a nice weekend.

Re: Check Different Select query result

Wendelius12-Aug-11 2:38

Wendelius

12-Aug-11 2:38

naunt wrote:
Any article or site you want to recommend?

I think there are lots of articles and sites about both so I have no specific suggestions. With a quick look I found these:
- http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm[^]
- http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server[^]

And of course go through msdn pages:
- Stored procedures[^]
- Returning Data from a Stored Procedure[^]
- Create procedure[^]
- Create function[^]

naunt wrote:
Thanks again and have a nice weekend.

You too

The need to optimize rises from a bad design.My articles[^]

Re: Check Different Select query result

Naunt12-Aug-11 0:50

Naunt

12-Aug-11 0:50

Many thanks
Will try with yours suggestion also.

Re: Check Different Select query result

Wendelius11-Aug-11 21:04

Wendelius

11-Aug-11 21:04

What's the best way to combine these statements depends totally on the conditions you're going to use. For example if the conditions fetch different subsets from the tables and you have written the if statements in such order where they most likely are true, then combining the different selects could lower the performance.

The need to optimize rises from a bad design.My articles[^]

Re: Check Different Select query result

Naunt11-Aug-11 22:24

Naunt

11-Aug-11 22:24

Thank you for you reply

Sorry, I didn't get what you mentioned. This are some of my many different conditions

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND  isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0)   AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0)  OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0)  > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))

Re: Check Different Select query result [modified]

Wendelius11-Aug-11 23:04

Wendelius

11-Aug-11 23:04

Ok,

If I take an example. Perhaps the easiest way to combine different statements would be to use union. For example if you have following logic (pseudo):

SQL

select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
  select @result = count(*) from table where column1 > 100
  if @result = 20 then color = yellow

Now you could combine these to something like:

SQL

cursor = select count(*) from table where column1 < 100
         union all
         select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then  color = yellow

However, as you see, both select statements would get the count from different rows. In example 1 the second query isn't executed at all if count > 10. In the second example all the selects are executed even if the results in different portions aren't needed. This can be very time (and resource) consuming.

This was a trivial case and most likely isn't exactly the same as in your situation but the point I'm trying to make is that is it really beneficial to combine all the statements. Another point of view is that the statement may become very large and hard to maintain if it contains too much logic.

Another (perhaps an easy ) option is to use scalar queries. This might work more easily for your case. The previous example could be something like:

SQL

select
      @result1 = (select count(*) from table where column1 < 100)
      @ersult2 = (select count(*) from table where column1 > 100);

if @result1 > 10 then color = blue
else if @result2 = 20 then  color = yellow

However the same performance problem as described earlier may arise.

The need to optimize rises from a bad design.My articles[^]

modified on Friday, August 12, 2011 6:43 AM

Re: Check Different Select query result

Naunt12-Aug-11 0:56

Naunt

12-Aug-11 0:56

Thanks again.

Like your last example, it's look clear and easy Smile | :)

But as you have told all selects will execute Frown | :(

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.