Reasonable SQL query

345 pts.
Tags:
SQL
SQL Query
Do you this this is a reasonable SQL query to have in an application?

sSQL = "select distinct "; sSQL += " S.ChainID,S.State,S.City,S.ChainCode,S.StoreNumber,"; sSQL += " DD.DeptID,D.DeptNum,DD.DeptDesc,DD.ReportGrpid ,' ' as tax1,' ' as tax2,' ' as tax3,DiscID "; sSQL += " ,convert(varchar(10),DiscStartDate) as DiscStartDate, convert(varchar(5),DiscStartTime) as DiscStartTime ,"; sSQL += " convert(varchar(10),DiscEndDate) as DiscEndDate,convert(varchar(5),DiscEndTime) as DiscEndTime , PricePoint,"; sSQL += " convert(varchar(3),TrivDiscID) as TrivDiscID into tmpdeptfile "; sSQL += " from Store_Info_Data S "; sSQL += " left outer join tblBrandDept BD "; sSQL += " on S.BrandID = BD.BrandID "; sSQL += " left outer join tblDepartments D "; sSQL += " on BD.DeptID = D.DeptID "; sSQL += " left outer join tblDeptDesc DD "; sSQL += " on (S.LanguageCode = DD.LanguageCode and D.DeptID = DD.DeptID )"; sSQL += " left outer join "; sSQL += " ("; sSQL += " select distinct Deptid, SDID.DiscID, SDID.ChainID, StoreNum, AutoDiscName, TrivDiscID, DiscStartDate,"; sSQL += " DiscStartTime, DiscEndDate, DiscEndTime, PricePoint "; sSQL += " from tblDeptDiscID DDID "; sSQL += " inner join tblStoreDiscID SDID "; sSQL += " on (DDID.Discid = SDID.discid) "; sSQL += " left outer join tblDiscountRules DR "; sSQL += " on (dr.Discid = sdid.discid) and DR.ChainID = SDID.ChainID "; sSQL += " ) x "; sSQL += " on D.DeptID = X.DeptID and x.ChainID = S.ChainID and x.StoreNum = S.StoreNumber "; sSQL += " WHERE S.State is not null and s.state not in ('TK Maxx') and s.ChainID is not null"; sSQL += " and D.DeptID is not null"; sSQL += " order by s.chaincode, s.storenumber, dd.DeptDesc";



Software/Hardware used:
SQL server 2008, C#
ASKED: June 24, 2013  2:39 PM
UPDATED: June 24, 2013  2:43 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CarlCioffi
    That's kind of ugly. sSQL = "select distinct "; sSQL += " S.ChainID,S.State,S.City,S.ChainCode,S.StoreNumber,"; sSQL += " DD.DeptID,D.DeptNum,DD.DeptDesc,DD.ReportGrpid ,' ' as tax1,' ' as tax2,' ' as tax3,DiscID "; sSQL += " ,convert(varchar(10),DiscStartDate) as DiscStartDate, convert(varchar(5),DiscStartTime) as DiscStartTime ,"; sSQL += " convert(varchar(10),DiscEndDate) as DiscEndDate,convert(varchar(5),DiscEndTime) as DiscEndTime , PricePoint,"; sSQL += " convert(varchar(3),TrivDiscID) as TrivDiscID into tmpdeptfile "; sSQL += " from Store_Info_Data S "; sSQL += " left outer join tblBrandDept BD "; sSQL += " on S.BrandID = BD.BrandID "; sSQL += " left outer join tblDepartments D "; sSQL += " on BD.DeptID = D.DeptID "; sSQL += " left outer join tblDeptDesc DD "; sSQL += " on (S.LanguageCode = DD.LanguageCode and D.DeptID = DD.DeptID )"; sSQL += " left outer join "; sSQL += " ("; sSQL += " select distinct Deptid, SDID.DiscID, SDID.ChainID, StoreNum, AutoDiscName, TrivDiscID, DiscStartDate,"; sSQL += " DiscStartTime, DiscEndDate, DiscEndTime, PricePoint "; sSQL += " from tblDeptDiscID DDID "; sSQL += " inner join tblStoreDiscID SDID "; sSQL += " on (DDID.Discid = SDID.discid) "; sSQL += " left outer join tblDiscountRules DR "; sSQL += " on (dr.Discid = sdid.discid) and DR.ChainID = SDID.ChainID "; sSQL += " ) x "; sSQL += " on D.DeptID = X.DeptID and x.ChainID = S.ChainID and x.StoreNum = S.StoreNumber "; sSQL += " WHERE S.State is not null and s.state not in ('TK Maxx') and s.ChainID is not null"; sSQL += " and D.DeptID is not null"; sSQL += " order by s.chaincode, s.storenumber, dd.DeptDesc";
    345 pointsBadges:
    report
  • CharlieBrowne
    Depends on your definition of reasonable. ;-) I would define that by having someone else in my shop look at it and determine if they were able to understand it and make a modification if necessary. The highest expense is programmer time. If it is going to take hours for a simple change because a different developer cannot understand it, then I think it is not reasonable.
    39,815 pointsBadges:
    report
  • CarlCioffi
    Good answer. I just thought it was more suited to a view that can be done with a query designer and stored in the database because it's only used to populate a datagrid that's not editable. Seems awfully complex for an inline query.
    345 pointsBadges:
    report
  • TomLiotta
    I've seen many much more complex queries. What seems odd to me is the number of assignment statements. I can't see any reason at all for a couple dozen assignment statements where only one is needed. In fact, since it's apparently a static SQL statement, I can't see why any assignment statement at all is used. . In any case, the query itself seems fine (assuming it does what's expected). . I suppose a VIEW would be reasonable if it provided any value outside of the particular program. If it's not valuable anywhere else, it's hard to justify creating a secondary object. I possibly would do it with a VIEW, but I don't know the program nor the environment. I tend to prefer external objects.. But without more info, it's hard to be sure. . Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following