convert to stored proc
in [Programming SQL]
|
From: John on 22 Jun 2010 21:30 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 22 Jun 2010 21:55 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 22 Jun 2010 22:22 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 22 Jun 2010 22:47 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 22 Jun 2010 23:46 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
|
Next
|
Last
Pages: 1 2 Prev: Retrieve unique values based on multiple columns Next: Tracking of changes |