From: John on
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
From: John on
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
From: Bob McClellan on
Hey John....
Should the original sql syntax read as IIf([POSOType]=[POSOType],
[POSOType], 'Standard') AS POSOTypeFull?
if so.. the case statement you provided should work fine.

also... if you are simply checking for null in POSOType then...
IsNull(POSOType,'Standard') AS POSOTypeFull would also work.

hth,
...bob

"John" <jevans10253(a)yahoo.com> wrote in message
news:580b486c-8e8a-4b6b-ad0c-24a902b82748(a)d37g2000yqm.googlegroups.com...
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

From: John on
On Jun 22, 9:22 pm, "Bob McClellan" <bob...(a)tricolift.RemoveThis.com>
wrote:
> Hey John....
> Should the original sql syntax read as IIf([POSOType]=[POSOType],
> [POSOType], 'Standard') AS POSOTypeFull?
> if so.. the case statement you provided should work fine.
>
> also... if you are simply checking for null in POSOType  then...
> IsNull(POSOType,'Standard') AS POSOTypeFull would also work.
>
> hth,
> ..bob
>
> "John" <jevans10...(a)yahoo.com> wrote in message
>
> news:580b486c-8e8a-4b6b-ad0c-24a902b82748(a)d37g2000yqm.googlegroups.com...
> 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- Hide quoted text -
>
> - Show quoted text -

Bob, thanks for the reply. I rechecked and it is just like I typed it
in the initial post. Darn, and I thought I was on a roll. I looked
up the IIF for Access and found;

iif ( condition, value_if_true, value_if_false )

Also, after I used the case from above, and did an execute, it ran
without error and returned the same number of records the SELECT
returned without using the case. That's what got me excited about
figuring it out.

Let me know what you think.

Thanks again.... John
From: Bob McClellan on
it should be fine John.
My understanding of the IIF statement was that the first part of the
statement
is the condition...
IIf(expr, truepart, falsepart)
as an example... =IIf([CountryRegion]="Italy", "Italian", "Some other
language")
That's why I questioned the syntax you posted.
It looks like your Expression is not evaluating anything.
The case statement you provided IS evaluating...WHEN POSOType = POSOType
so.... the bottom line is... it looks like your case statement is doing what
we'd assume
your IIF statement is supposed to be doing.

"John" <jevans10253(a)yahoo.com> wrote in message
news:5ab2a84f-6c83-4005-8f9e-667f9b18ee40(a)x21g2000yqa.googlegroups.com...
On Jun 22, 9:22 pm, "Bob McClellan" <bob...(a)tricolift.RemoveThis.com>
wrote:
> Hey John....
> Should the original sql syntax read as IIf([POSOType]=[POSOType],
> [POSOType], 'Standard') AS POSOTypeFull?
> if so.. the case statement you provided should work fine.
>
> also... if you are simply checking for null in POSOType then...
> IsNull(POSOType,'Standard') AS POSOTypeFull would also work.
>
> hth,
> ..bob
>
> "John" <jevans10...(a)yahoo.com> wrote in message
>
> news:580b486c-8e8a-4b6b-ad0c-24a902b82748(a)d37g2000yqm.googlegroups.com...
> 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- Hide quoted text -
>
> - Show quoted text -

Bob, thanks for the reply. I rechecked and it is just like I typed it
in the initial post. Darn, and I thought I was on a roll. I looked
up the IIF for Access and found;

iif ( condition, value_if_true, value_if_false )

Also, after I used the case from above, and did an execute, it ran
without error and returned the same number of records the SELECT
returned without using the case. That's what got me excited about
figuring it out.

Let me know what you think.

Thanks again.... John