convert to stored proc

Prev: Retrieve unique values based on multiple columns
Next: Tracking of changes

From: Bob Barrows on 23 Jun 2010 09:06

---

Your CASE syntax is all right, but the statement is not doing the same
thing as the Iif statement.

The Iif statement seems to be checking to see if the POSOType field
contains anything other than 0 - there is an implicit conversion to
Boolean (true/false) occurring there and if POSOType contains anything
other than 0 (or null), it evaluates to True and Iif returns the value
in POSOType; otherwise, Iif returns'Standard' .

In order for your CASE statement to do the same thing, you would have to
say:

CASE WHEN COALESCE(POSOType,0) <> 0 THEN POSOType ELSE 'Standard' END AS
POSOTypeFull

The COALESCE function returns the first non-null value, so if POSOType
contains Null, it returns 0.

John wrote:
> On Jun 22, 8:30 pm, John <jevans10...(a)yahoo.com> wrote:
>> Hello. I have been given a conversion assignment to convert Access db
>> (frontend) with sql server 2005 backend to be included in a new
>> webapp that is being constructed. Unfortunately, my stored proc
>> creating is a bit weak and I'm not afraid to admit it. With that
>> said, I came across an Access query that is puzzling me. Here it is;
>>
>> SELECT Purchase Orders *,
>> POSO Relationships.SONumber AS RelatedSONumber,
>> IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull,
>> POSORelationships.MTXRMA,
>> POSORelationships.SupplierRMA,
>> POSORelationships.POSOType
>>
>> FROM PurchaseOrders
>> LEFT JOIN POSORelationships ON PurchaseOrders.PONumber =
>> POSORelationships.PONumber
>>
>> ORDER BY PurchaseOrders.PONumber DESC;
>>
>> Now, a good portion of the above I can understand and convert. It is
>> the IIF that is confusing me. I'm pretty certain sql does not have
>> IIF. As I mentioned, I'm a bit weak in the stored proc department.
>> So I seek help from the group. Can anyone translate the IIF?
>>
>> Thanks...John
>
> Hey, I think I figured it out. Since sql does not have IIF then CASE
> has to be used. So the IIF line would be;
>
> CASE WHEN POSOType = POSOType THEN POSOType ELSE 'Standard' END AS
> POSOTypeFull,
>
> Would this be correct?
>
> Thanks.
> John

--
HTH,
Bob Barrows

From: Bob McClellan on 23 Jun 2010 09:26

---

Bob...
Does this not do the same?
>>IsNull(POSOType,'Standard') AS POSOTypeFull would also work.

"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:hvt0su$ad0$1(a)news.eternal-september.org...
> Your CASE syntax is all right, but the statement is not doing the same
> thing as the Iif statement.
>
> The Iif statement seems to be checking to see if the POSOType field
> contains anything other than 0 - there is an implicit conversion to
> Boolean (true/false) occurring there and if POSOType contains anything
> other than 0 (or null), it evaluates to True and Iif returns the value
> in POSOType; otherwise, Iif returns'Standard' .
>
> In order for your CASE statement to do the same thing, you would have to
> say:
>
> CASE WHEN COALESCE(POSOType,0) <> 0 THEN POSOType ELSE 'Standard' END AS
> POSOTypeFull
>
> The COALESCE function returns the first non-null value, so if POSOType
> contains Null, it returns 0.
>
> John wrote:
>> On Jun 22, 8:30 pm, John <jevans10...(a)yahoo.com> wrote:
>>> Hello. I have been given a conversion assignment to convert Access db
>>> (frontend) with sql server 2005 backend to be included in a new
>>> webapp that is being constructed. Unfortunately, my stored proc
>>> creating is a bit weak and I'm not afraid to admit it. With that
>>> said, I came across an Access query that is puzzling me. Here it is;
>>>
>>> SELECT Purchase Orders *,
>>> POSO Relationships.SONumber AS RelatedSONumber,
>>> IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull,
>>> POSORelationships.MTXRMA,
>>> POSORelationships.SupplierRMA,
>>> POSORelationships.POSOType
>>>
>>> FROM PurchaseOrders
>>> LEFT JOIN POSORelationships ON PurchaseOrders.PONumber =
>>> POSORelationships.PONumber
>>>
>>> ORDER BY PurchaseOrders.PONumber DESC;
>>>
>>> Now, a good portion of the above I can understand and convert. It is
>>> the IIF that is confusing me. I'm pretty certain sql does not have
>>> IIF. As I mentioned, I'm a bit weak in the stored proc department.
>>> So I seek help from the group. Can anyone translate the IIF?
>>>
>>> Thanks...John
>>
>> Hey, I think I figured it out. Since sql does not have IIF then CASE
>> has to be used. So the IIF line would be;
>>
>> CASE WHEN POSOType = POSOType THEN POSOType ELSE 'Standard' END AS
>> POSOTypeFull,
>>
>> Would this be correct?
>>
>> Thanks.
>> John
>
> --
> HTH,
> Bob Barrows
>
>

From: Bob Barrows on 23 Jun 2010 09:41

---

No, I don't think so. The original Iif() statement returns 'Standard' if
POSOType contains _either_ 0 or Null (remember the implicit conversion
to Boolean which does not occur in T-SQL, given that there is no Boolean
datatype in T-SQL - in JetSQL, anything except 0 or Null evaluates to
True). The ISNULL (or COALESCE) statement only returns 'Standard' if the
POSOType contains Null.

Bob McClellan wrote:
> Bob...
> Does this not do the same?
>>> IsNull(POSOType,'Standard') AS POSOTypeFull would also work.
>
> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
> news:hvt0su$ad0$1(a)news.eternal-september.org...
>> Your CASE syntax is all right, but the statement is not doing the
>> same thing as the Iif statement.
>>
>> The Iif statement seems to be checking to see if the POSOType field
>> contains anything other than 0 - there is an implicit conversion to
>> Boolean (true/false) occurring there and if POSOType contains
>> anything other than 0 (or null), it evaluates to True and Iif
>> returns the value in POSOType; otherwise, Iif returns'Standard' .
>>
>> In order for your CASE statement to do the same thing, you would
>> have to say:
>>
>> CASE WHEN COALESCE(POSOType,0) <> 0 THEN POSOType ELSE 'Standard'
>> END AS POSOTypeFull
>>
>> The COALESCE function returns the first non-null value, so if
>> POSOType contains Null, it returns 0.
>>
>> John wrote:
>>> On Jun 22, 8:30 pm, John <jevans10...(a)yahoo.com> wrote:
>>>> Hello. I have been given a conversion assignment to convert Access
>>>> db (frontend) with sql server 2005 backend to be included in a new
>>>> webapp that is being constructed. Unfortunately, my stored proc
>>>> creating is a bit weak and I'm not afraid to admit it. With that
>>>> said, I came across an Access query that is puzzling me. Here it
>>>> is;
>>>>
>>>> SELECT Purchase Orders *,
>>>> POSO Relationships.SONumber AS RelatedSONumber,
>>>> IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull,
>>>> POSORelationships.MTXRMA,
>>>> POSORelationships.SupplierRMA,
>>>> POSORelationships.POSOType
>>>>
>>>> FROM PurchaseOrders
>>>> LEFT JOIN POSORelationships ON PurchaseOrders.PONumber =
>>>> POSORelationships.PONumber
>>>>
>>>> ORDER BY PurchaseOrders.PONumber DESC;
>>>>
>>>> Now, a good portion of the above I can understand and convert. It
>>>> is the IIF that is confusing me. I'm pretty certain sql does not
>>>> have IIF. As I mentioned, I'm a bit weak in the stored proc
>>>> department. So I seek help from the group. Can anyone translate
>>>> the IIF?
>>>>
>>>> Thanks...John
>>>
>>> Hey, I think I figured it out. Since sql does not have IIF then
>>> CASE has to be used. So the IIF line would be;
>>>
>>> CASE WHEN POSOType = POSOType THEN POSOType ELSE 'Standard' END AS
>>> POSOTypeFull,
>>>
>>> Would this be correct?
>>>
>>> Thanks.
>>> John
>>
>> --
>> HTH,
>> Bob Barrows

--
HTH,
Bob Barrows

From: Bob McClellan on 23 Jun 2010 10:00

---

Got it.
Thanks for the clarification Bob.
...much appreciated.

"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
news:hvt38t$js7$1(a)news.eternal-september.org...
> No, I don't think so. The original Iif() statement returns 'Standard' if
> POSOType contains _either_ 0 or Null (remember the implicit conversion
> to Boolean which does not occur in T-SQL, given that there is no Boolean
> datatype in T-SQL - in JetSQL, anything except 0 or Null evaluates to
> True). The ISNULL (or COALESCE) statement only returns 'Standard' if the
> POSOType contains Null.
>
> Bob McClellan wrote:
>> Bob...
>> Does this not do the same?
>>>> IsNull(POSOType,'Standard') AS POSOTypeFull would also work.
>>
>> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
>> news:hvt0su$ad0$1(a)news.eternal-september.org...
>>> Your CASE syntax is all right, but the statement is not doing the
>>> same thing as the Iif statement.
>>>
>>> The Iif statement seems to be checking to see if the POSOType field
>>> contains anything other than 0 - there is an implicit conversion to
>>> Boolean (true/false) occurring there and if POSOType contains
>>> anything other than 0 (or null), it evaluates to True and Iif
>>> returns the value in POSOType; otherwise, Iif returns'Standard' .
>>>
>>> In order for your CASE statement to do the same thing, you would
>>> have to say:
>>>
>>> CASE WHEN COALESCE(POSOType,0) <> 0 THEN POSOType ELSE 'Standard'
>>> END AS POSOTypeFull
>>>
>>> The COALESCE function returns the first non-null value, so if
>>> POSOType contains Null, it returns 0.
>>>
>>> John wrote:
>>>> On Jun 22, 8:30 pm, John <jevans10...(a)yahoo.com> wrote:
>>>>> Hello. I have been given a conversion assignment to convert Access
>>>>> db (frontend) with sql server 2005 backend to be included in a new
>>>>> webapp that is being constructed. Unfortunately, my stored proc
>>>>> creating is a bit weak and I'm not afraid to admit it. With that
>>>>> said, I came across an Access query that is puzzling me. Here it
>>>>> is;
>>>>>
>>>>> SELECT Purchase Orders *,
>>>>> POSO Relationships.SONumber AS RelatedSONumber,
>>>>> IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull,
>>>>> POSORelationships.MTXRMA,
>>>>> POSORelationships.SupplierRMA,
>>>>> POSORelationships.POSOType
>>>>>
>>>>> FROM PurchaseOrders
>>>>> LEFT JOIN POSORelationships ON PurchaseOrders.PONumber =
>>>>> POSORelationships.PONumber
>>>>>
>>>>> ORDER BY PurchaseOrders.PONumber DESC;
>>>>>
>>>>> Now, a good portion of the above I can understand and convert. It
>>>>> is the IIF that is confusing me. I'm pretty certain sql does not
>>>>> have IIF. As I mentioned, I'm a bit weak in the stored proc
>>>>> department. So I seek help from the group. Can anyone translate
>>>>> the IIF?
>>>>>
>>>>> Thanks...John
>>>>
>>>> Hey, I think I figured it out. Since sql does not have IIF then
>>>> CASE has to be used. So the IIF line would be;
>>>>
>>>> CASE WHEN POSOType = POSOType THEN POSOType ELSE 'Standard' END AS
>>>> POSOTypeFull,
>>>>
>>>> Would this be correct?
>>>>
>>>> Thanks.
>>>> John
>>>
>>> --
>>> HTH,
>>> Bob Barrows
>
> --
> HTH,
> Bob Barrows
>
>

From: Bob Barrows on 23 Jun 2010 10:14

---

And, of course, I had a brain fart myself: this could be more easily
written as:
CASE WHEN POSOType<> 0 THEN CAST(POSOType AS VARCHAR(10)) ELSE
'Standard' END AS
POSOTypeFull

Note that since the POSOType is returned first with this version, we
have to explicitly convert it to varchar so that no attempt is made to
convert 'Standard' to integer.

Bob McClellan wrote:
> Bob...
> Does this not do the same?
>>> IsNull(POSOType,'Standard') AS POSOTypeFull would also work.
>
> "Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in message
> news:hvt0su$ad0$1(a)news.eternal-september.org...
>> Your CASE syntax is all right, but the statement is not doing the
>> same thing as the Iif statement.
>>
>> The Iif statement seems to be checking to see if the POSOType field
>> contains anything other than 0 - there is an implicit conversion to
>> Boolean (true/false) occurring there and if POSOType contains
>> anything other than 0 (or null), it evaluates to True and Iif
>> returns the value in POSOType; otherwise, Iif returns'Standard' .
>>
>> In order for your CASE statement to do the same thing, you would
>> have to say:
>>
>> CASE WHEN COALESCE(POSOType,0) <> 0 THEN POSOType ELSE 'Standard'
>> END AS POSOTypeFull
>>
>> The COALESCE function returns the first non-null value, so if
>> POSOType contains Null, it returns 0.
>>
>> John wrote:
>>> On Jun 22, 8:30 pm, John <jevans10...(a)yahoo.com> wrote:
>>>> Hello. I have been given a conversion assignment to convert Access
>>>> db (frontend) with sql server 2005 backend to be included in a new
>>>> webapp that is being constructed. Unfortunately, my stored proc
>>>> creating is a bit weak and I'm not afraid to admit it. With that
>>>> said, I came across an Access query that is puzzling me. Here it
>>>> is;
>>>>
>>>> SELECT Purchase Orders *,
>>>> POSO Relationships.SONumber AS RelatedSONumber,
>>>> IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull,
>>>> POSORelationships.MTXRMA,
>>>> POSORelationships.SupplierRMA,
>>>> POSORelationships.POSOType
>>>>
>>>> FROM PurchaseOrders
>>>> LEFT JOIN POSORelationships ON PurchaseOrders.PONumber =
>>>> POSORelationships.PONumber
>>>>
>>>> ORDER BY PurchaseOrders.PONumber DESC;
>>>>
>>>> Now, a good portion of the above I can understand and convert. It
>>>> is the IIF that is confusing me. I'm pretty certain sql does not
>>>> have IIF. As I mentioned, I'm a bit weak in the stored proc
>>>> department. So I seek help from the group. Can anyone translate
>>>> the IIF?
>>>>
>>>> Thanks...John
>>>
>>> Hey, I think I figured it out. Since sql does not have IIF then
>>> CASE has to be used. So the IIF line would be;
>>>
>>> CASE WHEN POSOType = POSOType THEN POSOType ELSE 'Standard' END AS
>>> POSOTypeFull,
>>>
>>> Would this be correct?
>>>
>>> Thanks.
>>> John
>>
>> --
>> HTH,
>> Bob Barrows

--
HTH,
Bob Barrows

First  |  Prev  | 
Pages: 1 2
Prev: Retrieve unique values based on multiple columns
Next: Tracking of changes