March 14, 2008 at 9:44 am
I have a customer I support who created the view below from Foxpro. I believe I am correct in the nowait needs to come out, and I question the validity of the top 100 not being an issue. Any arguments accepted
CREATE VIEW dbo.cms_HRxs
AS
SELECT TOP 100 PERCENT
Posted, FacID, RxNo, DispenseDt, RoNo, PatID, TransType, PatName, Reversed, PharmID, PhrNPI, PhrName, PhNPI, PhName, PhLicNo, PhDeaNo, NsID, NDC, CCID, DrugLabelName, Qty, MOP, MOP2, PriceCd, Cost, Fee, Copay, Discount, TtlPrice, UandC, ElecAdj, ElecAdj2, ElecRej, ElecRej2, ActCost, BilledPrimaryTPA, Payor1PaidAmt, Payor2PaidAmt, PatientPayAmt, DaysSupply, TimesPerDay, Refills, PriorAuthNo, PriorAuthExpireDt, TreatmentRx, Auto, CutoffDt, OrigDt, ExpireDt, Lables, LabQtys, NewRx, PriceOr, DAW, Notes, ClarificationCd, LastModifiedBy, LastModifiedOn, InvoiceGrp, RxLabelText, DispFor, BilledSecondaryTPA, BilledPrivate, UDRx, UDRxNo, DispMethod, Comments, LotNo, CCType, IVType, PrivMop, SheetID, SerialNo, SubmitQty1, SubmitQty2, InvCost, AuthNo1, AuthNo2, EnteredByNPI, Entered, PackSlipMsg, CCardType, CCardNo, CCardExpDt, MCID, CreditQty, CreditAmt, CCardholderName, CCORide1, CCORide2, LabelPrinted, DDIChecked, PARChecked, DUPChecked, DrugID, Sig, RxType, NonForm, PackBy, Packed, BillCd, DispFactor, UniqueRxID, OptfillID, FedexNumber, ErrorCode, SigExp, AutoMedDate, FromWOE, InsertedByCredit
FROM Rx.dbo.HRxs Hx WITH (NOWAIT)
UNION ALL
SELECT TOP 100 PERCENT
Posted, FacID, RxNo, DispenseDt, RoNo, PatID, TransType, PatName, Reversed, PharmID, PhrNPI, PhrName, PhNPI, PhName, PhLicNo, PhDeaNo, NsID, NDC, CCID, DrugLabelName, Qty, MOP, MOP2, PriceCd, Cost, Fee, Copay, Discount, TtlPrice, UandC, ElecAdj, ElecAdj2, ElecRej, ElecRej2, ActCost, BilledPrimaryTPA, Payor1PaidAmt, Payor2PaidAmt, PatientPayAmt, DaysSupply, TimesPerDay, Refills, PriorAuthNo, PriorAuthExpireDt, TreatmentRx, Auto, CutoffDt, OrigDt, ExpireDt, Lables, LabQtys, NewRx, PriceOr, DAW, Notes, ClarificationCd, LastModifiedBy, LastModifiedOn, InvoiceGrp, RxLabelText, DispFor, BilledSecondaryTPA, BilledPrivate, UDRx, UDRxNo, DispMethod, Comments, LotNo, CCType, IVType, PrivMop, SheetID, SerialNo, SubmitQty1, SubmitQty2, InvCost, AuthNo1, AuthNo2, EnteredByNPI, Entered, PackSlipMsg, CCardType, CCardNo, CCardExpDt, MCID, CreditQty, CreditAmt, CCardholderName, CCORide1, CCORide2, LabelPrinted, DDIChecked, PARChecked, DUPChecked, DrugID, Sig, RxType, NonForm, PackBy, Packed, BillCd, DispFactor, UniqueRxID, OptfillID, FedexNumber, ErrorCode, SigExp, AutoMedDate, FromWOE, InsertedByCredit
FROM FWArchive.dbo.HRxs2006 Hx WITH (NOWAIT)
UNION ALL
SELECT TOP 100 PERCENT
Posted, FacID, RxNo, DispenseDt, RoNo, PatID, TransType, PatName, Reversed, PharmID, PhrNPI, PhrName, PhNPI, PhName, PhLicNo, PhDeaNo, NsID, NDC, CCID, DrugLabelName, Qty, MOP, MOP2, PriceCd, Cost, Fee, Copay, Discount, TtlPrice, UandC, ElecAdj, ElecAdj2, ElecRej, ElecRej2, ActCost, BilledPrimaryTPA, Payor1PaidAmt, Payor2PaidAmt, PatientPayAmt, DaysSupply, TimesPerDay, Refills, PriorAuthNo, PriorAuthExpireDt, TreatmentRx, Auto, CutoffDt, OrigDt, ExpireDt, Lables, LabQtys, NewRx, PriceOr, DAW, Notes, ClarificationCd, LastModifiedBy, LastModifiedOn, InvoiceGrp, RxLabelText, DispFor, BilledSecondaryTPA, BilledPrivate, UDRx, UDRxNo, DispMethod, Comments, LotNo, CCType, IVType, PrivMop, SheetID, SerialNo, SubmitQty1, SubmitQty2, InvCost, AuthNo1, AuthNo2, EnteredByNPI, Entered, PackSlipMsg, CCardType, CCardNo, CCardExpDt, MCID, CreditQty, CreditAmt, CCardholderName, CCORide1, CCORide2, LabelPrinted, DDIChecked, PARChecked, DUPChecked, DrugID, Sig, RxType, NonForm, PackBy, Packed, BillCd, DispFactor, UniqueRxID, OptfillID, FedexNumber, ErrorCode, SigExp, AutoMedDate, FromWOE, InsertedByCredit
FROM FWArchive.dbo.HRxs2005 Hx WITH (NOWAIT)
UNION ALL
SELECT TOP 100 PERCENT
Posted, FacID, RxNo, DispenseDt, RoNo, PatID, TransType, PatName, Reversed, PharmID, PhrNPI, PhrName, PhNPI, PhName, PhLicNo, PhDeaNo, NsID, NDC, CCID, DrugLabelName, Qty, MOP, MOP2, PriceCd, Cost, Fee, Copay, Discount, TtlPrice, UandC, ElecAdj, ElecAdj2, ElecRej, ElecRej2, ActCost, BilledPrimaryTPA, Payor1PaidAmt, Payor2PaidAmt, PatientPayAmt, DaysSupply, TimesPerDay, Refills, PriorAuthNo, PriorAuthExpireDt, TreatmentRx, Auto, CutoffDt, OrigDt, ExpireDt, Lables, LabQtys, NewRx, PriceOr, DAW, Notes, ClarificationCd, LastModifiedBy, LastModifiedOn, InvoiceGrp, RxLabelText, DispFor, BilledSecondaryTPA, BilledPrivate, UDRx, UDRxNo, DispMethod, Comments, LotNo, CCType, IVType, PrivMop, SheetID, SerialNo, SubmitQty1, SubmitQty2, InvCost, AuthNo1, AuthNo2, EnteredByNPI, Entered, PackSlipMsg, CCardType, CCardNo, CCardExpDt, MCID, CreditQty, CreditAmt, CCardholderName, CCORide1, CCORide2, LabelPrinted, DDIChecked, PARChecked, DUPChecked, DrugID, Sig, RxType, NonForm, PackBy, Packed, BillCd, DispFactor, UniqueRxID, OptfillID, FedexNumber, ErrorCode, SigExp, AutoMedDate, FromWOE, InsertedByCredit
FROM FWArchive.dbo.HRxs2004 Hx WITH (NOWAIT)
UNION ALL
SELECT TOP 100 PERCENT
Posted, FacID, RxNo, DispenseDt, RoNo, PatID, TransType, PatName, Reversed, PharmID, PhrNPI, PhrName, PhNPI, PhName, PhLicNo, PhDeaNo, NsID, NDC, CCID, DrugLabelName, Qty, MOP, MOP2, PriceCd, Cost, Fee, Copay, Discount, TtlPrice, UandC, ElecAdj, ElecAdj2, ElecRej, ElecRej2, ActCost, BilledPrimaryTPA, Payor1PaidAmt, Payor2PaidAmt, PatientPayAmt, DaysSupply, TimesPerDay, Refills, PriorAuthNo, PriorAuthExpireDt, TreatmentRx, Auto, CutoffDt, OrigDt, ExpireDt, Lables, LabQtys, NewRx, PriceOr, DAW, Notes, ClarificationCd, LastModifiedBy, LastModifiedOn, InvoiceGrp, RxLabelText, DispFor, BilledSecondaryTPA, BilledPrivate, UDRx, UDRxNo, DispMethod, Comments, LotNo, CCType, IVType, PrivMop, SheetID, SerialNo, SubmitQty1, SubmitQty2, InvCost, AuthNo1, AuthNo2, EnteredByNPI, Entered, PackSlipMsg, CCardType, CCardNo, CCardExpDt, MCID, CreditQty, CreditAmt, CCardholderName, CCORide1, CCORide2, LabelPrinted, DDIChecked, PARChecked, DUPChecked, DrugID, Sig, RxType, NonForm, PackBy, Packed, BillCd, DispFactor, UniqueRxID, OptfillID, FedexNumber, ErrorCode, SigExp, AutoMedDate, FromWOE, InsertedByCredit
FROM FWArchive.dbo.HRxs2003 Hx WITH (NOWAIT)
March 14, 2008 at 9:53 am
Yes, NOWAIT isn't valid syntax for SQL Server in this context. And the TOP 100 PERCENT is only useful if you're ordering the result set - which you shouldn't in a view anyway - so you should get rid of that as well.
John
March 14, 2008 at 9:55 am
Thanks, I get to tell my cowboy user this
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply