Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.
Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>
|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>
|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegro ups.com...
|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegrou ps.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>
Showing posts with label convert. Show all posts
Showing posts with label convert. Show all posts
Monday, March 26, 2012
Image to Varchar
Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>sql
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:[vbcol=seagreen]
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>sql
Image to Varchar
Hi All,
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:
> > But, here the image is too lengthy.
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> > Hi All,
> > I have a small doubt.
> > How to convert an Imaage of about 110 character length to varchar.
> > The image is nothing but the text data stored in that format.
> > While using "convert" function for smaller data,Image to Varchar and
> > Varchar to Image conversion is successful.
> > But, here the image is too lengthy.
> > Kindly, anyone suggest a way.
> > Thanks,
> > Dutt.
> >|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>> > But, here the image is too lengthy.
>> What do you mean by 'too lengthy'?
>> Try:
>> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
>> FROM dbo.MyTable
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
>> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
>> > Hi All,
>> > I have a small doubt.
>> > How to convert an Imaage of about 110 character length to varchar.
>> > The image is nothing but the text data stored in that format.
>> > While using "convert" function for smaller data,Image to Varchar and
>> > Varchar to Image conversion is successful.
>> > But, here the image is too lengthy.
>> > Kindly, anyone suggest a way.
>> > Thanks,
>> > Dutt.
>> >
>
I have a small doubt.
How to convert an Imaage of about 110 character length to varchar.
The image is nothing but the text data stored in that format.
While using "convert" function for smaller data,Image to Varchar and
Varchar to Image conversion is successful.
But, here the image is too lengthy.
Kindly, anyone suggest a way.
Thanks,
Dutt.Dutt
declare @.b binary(4), @.str varchar(255)
select @.b = 3455643
exec master..xp_varbintohexstr @.b, @.str out
print @.str
select cast(cast(cast(0x41 as nvarchar) as varbinary)as varchar)
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||> But, here the image is too lengthy.
What do you mean by 'too lengthy'?
Try:
SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
FROM dbo.MyTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> Hi All,
> I have a small doubt.
> How to convert an Imaage of about 110 character length to varchar.
> The image is nothing but the text data stored in that format.
> While using "convert" function for smaller data,Image to Varchar and
> Varchar to Image conversion is successful.
> But, here the image is too lengthy.
> Kindly, anyone suggest a way.
> Thanks,
> Dutt.
>|||Hi Dan,
I'm getting some output with that.Even,I have tried it earlier.
But the problem is that the image is being converted into ANSII code,I
suppose.
I'm not getting the actual text.
Thanks
Dutt.
Dan Guzman wrote:
> > But, here the image is too lengthy.
> What do you mean by 'too lengthy'?
> Try:
> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
> FROM dbo.MyTable
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
> > Hi All,
> > I have a small doubt.
> > How to convert an Imaage of about 110 character length to varchar.
> > The image is nothing but the text data stored in that format.
> > While using "convert" function for smaller data,Image to Varchar and
> > Varchar to Image conversion is successful.
> > But, here the image is too lengthy.
> > Kindly, anyone suggest a way.
> > Thanks,
> > Dutt.
> >|||> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
How is the text encoded? I would expect that the data would be converted
according to your database default collation. Can you post a raw data
sample (e.g. 0x68656c6c6f) along with your expected results (e.g. 'hello')?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1167970260.405637.12700@.11g2000cwr.googlegroups.com...
> Hi Dan,
> I'm getting some output with that.Even,I have tried it earlier.
> But the problem is that the image is being converted into ANSII code,I
> suppose.
> I'm not getting the actual text.
> Thanks
> Dutt.
> Dan Guzman wrote:
>> > But, here the image is too lengthy.
>> What do you mean by 'too lengthy'?
>> Try:
>> SELECT CAST(SUBSTRING(MyImageColumn, 1, 110) AS varchar(110))
>> FROM dbo.MyTable
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
>> news:1167893950.543251.130570@.31g2000cwt.googlegroups.com...
>> > Hi All,
>> > I have a small doubt.
>> > How to convert an Imaage of about 110 character length to varchar.
>> > The image is nothing but the text data stored in that format.
>> > While using "convert" function for smaller data,Image to Varchar and
>> > Varchar to Image conversion is successful.
>> > But, here the image is too lengthy.
>> > Kindly, anyone suggest a way.
>> > Thanks,
>> > Dutt.
>> >
>
Friday, March 23, 2012
Image data type to character string
I have a table with an image datatype field.
When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?
Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..
If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.
Wednesday, March 21, 2012
Image data type to character string
I have a table with an image datatype field.
When I retrieve it it displays as a binary array. How do I convert that array back and forth to get the underlying text?
Post the SQL statement used in this regard, you need to use READTEXT statement, also http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp fyi..
If you are using any application to display that image column then refer to http://www.akadia.com/services/dotnet_load_blob.html link for more information.
Image Data Type
I have a column that has audit details stored as an image data type. How do i convert that into meaningful data that i can read.
Example:
0x78F992BD69BF23BE243134839506265892223083981A6104 A389A1B1918129B6303035B434C7748FAF63508887A33B99CB AE4C8C4C8D71B68DCC8CCC8C0D4D71350040B246C603309AEE 1810E0EAE8437046C3C440D7C48892414E007B1F8BED
I am guessing this is text with fomatting instructions. Because in the software that utilized this database, this field seems to be interpreted as a hierarachial table showing the audit details of an event.
please offer any suggestions you can.
thanks so muchActually this is binary data. The only way to access or read it is through an application. See www.asp.net for some examples of how to access the data.|||Is this a BLOB file that i can use a filestream object to import into a VB application? Sorry for my newbieness.
thanks
Mardi|||Yes, an IMAGE is a blob. Typically, it is at least an 8 kb blob in order to use the database space effectively, although it can be smaller.
-PatP
Example:
0x78F992BD69BF23BE243134839506265892223083981A6104 A389A1B1918129B6303035B434C7748FAF63508887A33B99CB AE4C8C4C8D71B68DCC8CCC8C0D4D71350040B246C603309AEE 1810E0EAE8437046C3C440D7C48892414E007B1F8BED
I am guessing this is text with fomatting instructions. Because in the software that utilized this database, this field seems to be interpreted as a hierarachial table showing the audit details of an event.
please offer any suggestions you can.
thanks so muchActually this is binary data. The only way to access or read it is through an application. See www.asp.net for some examples of how to access the data.|||Is this a BLOB file that i can use a filestream object to import into a VB application? Sorry for my newbieness.
thanks
Mardi|||Yes, an IMAGE is a blob. Typically, it is at least an 8 kb blob in order to use the database space effectively, although it can be smaller.
-PatP
image data type
I have documents stored in an image field. How can I mass
convert them to inividual disk files?You might want to try the TEXTCOPY.EXE utility that comes with SQL Server.
It is found in the \Binn folder with your SQL Server Installation location.
The utility copies a single text or image value into or out of SQL Server.
Navigate to that folder (in your DOS command prompt) and type TEXTCOPY.EXE
/? for more help.
Nathan H.O.
convert them to inividual disk files?You might want to try the TEXTCOPY.EXE utility that comes with SQL Server.
It is found in the \Binn folder with your SQL Server Installation location.
The utility copies a single text or image value into or out of SQL Server.
Navigate to that folder (in your DOS command prompt) and type TEXTCOPY.EXE
/? for more help.
Nathan H.O.
Friday, February 24, 2012
iif to case for sql server 2000
I am trying to convert this query to slq server 2000 and I cant figure
out how to get rid of the IIF statements and make them case statements.
out how to get rid of the IIF statements and make them case statements.
If anyone could help I would greatly appreciate it!
Thanks!
spafa
SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,
Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue Cross\",\"YES\",\"NO\"))))
AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,
Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null) AS
[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
AccountOwnership.CLASSIC, AccountOwnership.BROKER,
AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
Null,[DISPOSITION_month],Null) AS OC_MONTH
FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ON
AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
Jeopardy.DATE_UPDATED DESC; " );
--
Posted via http://dbforums.comBelow are 2 examples taken directly from the SQL 2000 Books Online
<"tsqlref.chm::/ts_ca-co_5t9v.htm">. The first example uses the simple
CASE form where the specified expression is compared with each entry in
the list. The second example uses the searched CASE form where each
entry in the list specifies a Boolean condition.
USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------
"SPAFA" <member44362@.dbforums.com> wrote in message
news:3487057.1066269043@.dbforums.com...
> I am trying to convert this query to slq server 2000 and I cant figure
> out how to get rid of the IIF statements and make them case
statements.
>
> If anyone could help I would greatly appreciate it!
>
> Thanks!
> spafa
>
>
> SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,
> Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
> Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
> Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL,
Jeopardy.COMP_DNTL_DISC,
> Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]=\"Mass Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Out of State Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"CT Blue
> Cross\",\"YES\",IIf([COMP_HLTH]=\"Empire Blue
Cross\",\"YES\",\"NO\"))))
> AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
> BC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE,
Jeopardy.DATE_NOTIFIED,
> Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
> Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
> IIf([DateDiff]<8,\"*\",Null) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null)
AS
> [ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAME
> AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
> AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
> AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,
> Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is
Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
> ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,
> AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
> AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
> AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
> AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
> AccountOwnership.CLASSIC, AccountOwnership.BROKER,
> AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
> AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
> Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
> Null,[DISPOSITION_month],Null) AS OC_MONTH
> FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
> AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
> ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES
ON
> AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
> Jeopardy.DATE_UPDATED DESC; " );
>
> --
> Posted via http://dbforums.com|||In addition to Dan's post, note that there may sometimes be neater
alternatives to CASE:
... COALESCE(NULLIF(Jeopardy.cycle,'N/A'),Jeopardy.dental_cycle) AS cycle2,
...
--
David Portas
----
Please reply only to the newsgroup
--
IIF statment in an SQL statment.
I am trying to convert a logical and date fields into number fields. I am
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke
using IIF() but I can't seem to get the syntax correct. Help Please.
SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
FROM Order2
GROUP BY MagazineName
The overall goal is to
1) count then number of records
2) count how many are renewal's
3) count how many are canceled
example:
CHILD 50 5 1
Scott BurkeHey Scott,
Use the CASE statement in SQL, not IIF. Like this:
SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag =false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
1 END as CLRFL
FROM Order2
GROUP BY MagazineName
Michael C
"Scott Burke" wrote:
> I am trying to convert a logical and date fields into number fields. I am
> using IIF() but I can't seem to get the syntax correct. Help Please.
> SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> FROM Order2
> GROUP BY MagazineName
> The overall goal is to
> 1) count then number of records
> 2) count how many are renewal's
> 3) count how many are canceled
> example:
> CHILD 50 5 1
> Scott Burke|||Hi Michael. Thanks for the suggestion. It worked exactly the way I wonted to.
The case statement looks a lot like an IIF() statement to me.
Time to do some research.
Thanks again.
Scott Burke
"Michael C" wrote:
> Hey Scott,
> Use the CASE statement in SQL, not IIF. Like this:
>
> SELECT MagazineName, COUNT(Quantity) AS QTY,CASE WHEN RenewalFlag => false THEN 0 ELSE 1 END AS REFL, CASE WHEN CancelDate is not null THEN 0 ELSE
> 1 END as CLRFL
> FROM Order2
> GROUP BY MagazineName
>
> Michael C
> "Scott Burke" wrote:
> > I am trying to convert a logical and date fields into number fields. I am
> > using IIF() but I can't seem to get the syntax correct. Help Please.
> >
> > SELECT MagazineName, COUNT(Quantity) AS QTY, iif(RenewalFlag = flase, 0,
> > 1) AS REFL, iif(CancelDate is not null,0,1) as CLRFL
> > FROM Order2
> > GROUP BY MagazineName
> >
> > The overall goal is to
> > 1) count then number of records
> > 2) count how many are renewal's
> > 3) count how many are canceled
> >
> > example:
> > CHILD 50 5 1
> >
> > Scott Burke
Subscribe to:
Posts (Atom)