Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How Do You Keep The "Pieces" Of A Complex Statement "Organized" Expand / Collapse
Author
Message
Posted Friday, September 21, 2012 2:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
I just finished a complex SQL statement; and, yes, I know there are even more complex ones.

When I am faced with a complex statement I try to create the pieces, then embed them in the main query. There are times when I have trouble differentiating where one ends and another begins. A few times I removed a part of the statement, or not enough, when I make changes. My only fall back is I always keep a backup of the last working statement.

The statement I just finished is below. I'm guessing that those of you who have developed the skills can probably see where pieces begin and end.

Is my approach the effective way?
Does anyone have suggestions on how to keep "the pieces straight"?

SELECT o.JOB_NUMBER                                         AS Job#, 
ORDER_NO AS Order#,
FORM_NO AS Form,
CUST_IDENT AS SpecID,
QTY_ORDERED AS QtyOrdered,
ORD_PRICE AS UnitPrice,
PRICING_METH AS UOM,
s.COLOR_DESC AS Colors,
opsused.PreStamp,
opsused.Decorative,
opsused.Emboss,
DESIGN_NO AS Drawing,
CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7))
+ ' x '
+ CAST(CAST(s.DIM_B AS DECIMAL(9, 3)) AS VARCHAR(7))
+ ' x '
+ CAST(CAST(s.DIM_C AS DECIMAL(9, 3)) AS VARCHAR(7)) AS Dimensions,
gf.DESCR AS Board,
DUE_DATE AS DueDate,
COMPLETION_FLG AS OrderStatus
FROM ORDERS o
LEFT JOIN SPECS s
ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE gf
ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN (SELECT JobOpsList.JobNum,
CASE jobopslist.prestamp
WHEN 0 THEN ''
ELSE 'Y'
END AS PreStamp,
CASE jobopslist.Decorative
WHEN 0 THEN ''
ELSE 'Y'
END AS Decorative,
CASE jobopslist.Emboss
WHEN 0 THEN ''
ELSE 'Y'
END AS Emboss
FROM (SELECT JobList.JobNum,
SUM(CASE ops.Mach_No
WHEN 288 THEN 1
ELSE 0
END) AS PreStamp,
SUM(CASE ops.Mach_No
WHEN 290 THEN 1
ELSE 0
END) AS Decorative,
SUM(CASE ops.Mach_No
WHEN 292 THEN 1
ELSE 0
END) AS Emboss
FROM (SELECT JOB_NUMBER AS JobNum,
CASE ISNULL(o.COMBO_NO, '')
WHEN '' THEN o.SPEC_NO
ELSE o.COMBO_NO
END AS MfrSpec
FROM ORDERS o
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
GROUP BY JOB_NUMBER,
CASE ISNULL(o.combo_no, '')
WHEN '' THEN o.SPEC_NO
ELSE o.COMBO_NO
END) JobList
LEFT JOIN OPERATIONS ops
ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN ( 288, 290, 292 )
GROUP BY JobNum) JobOpsList) OpsUsed
ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'



"The Road To Success Is Always Under Construction."
Post #1363003
Posted Friday, September 21, 2012 3:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
Really what you are talking about is formatting. This can be subject to personal preference. There is a free site http://poorsql.com/ that goes a pretty decent job.

Here are the results of your code from that site.

SELECT o.JOB_NUMBER AS Job#
,ORDER_NO AS Order#
,FORM_NO AS Form
,CUST_IDENT AS SpecID
,QTY_ORDERED AS QtyOrdered
,ORD_PRICE AS UnitPrice
,PRICING_METH AS UOM
,s.COLOR_DESC AS Colors
,opsused.PreStamp
,opsused.Decorative
,opsused.Emboss
,DESIGN_NO AS Drawing
,CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_B AS DECIMAL(9, 3)) AS VARCHAR(7)) + ' x ' + CAST(CAST(s.DIM_C AS DECIMAL(9, 3)) AS VARCHAR(7)) AS Dimensions
,gf.DESCR AS Board
,DUE_DATE AS DueDate
,COMPLETION_FLG AS OrderStatus
FROM ORDERS o
LEFT JOIN SPECS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN (
SELECT JobOpsList.JobNum
,CASE jobopslist.prestamp
WHEN 0
THEN ''
ELSE 'Y'
END AS PreStamp
,CASE jobopslist.Decorative
WHEN 0
THEN ''
ELSE 'Y'
END AS Decorative
,CASE jobopslist.Emboss
WHEN 0
THEN ''
ELSE 'Y'
END AS Emboss
FROM (
SELECT JobList.JobNum
,SUM(CASE ops.Mach_No
WHEN 288
THEN 1
ELSE 0
END) AS PreStamp
,SUM(CASE ops.Mach_No
WHEN 290
THEN 1
ELSE 0
END) AS Decorative
,SUM(CASE ops.Mach_No
WHEN 292
THEN 1
ELSE 0
END) AS Emboss
FROM (
SELECT JOB_NUMBER AS JobNum
,CASE ISNULL(o.COMBO_NO, '')
WHEN ''
THEN o.SPEC_NO
ELSE o.COMBO_NO
END AS MfrSpec
FROM ORDERS o
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
GROUP BY JOB_NUMBER
,CASE ISNULL(o.combo_no, '')
WHEN ''
THEN o.SPEC_NO
ELSE o.COMBO_NO
END
) JobList
LEFT JOIN OPERATIONS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN (
288
,290
,292
)
GROUP BY JobNum
) JobOpsList
) OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'


One thing in your query jumps right off the page as a major flaw.

CAST(CAST(s.dim_A AS DECIMAL(9, 3)) AS VARCHAR(7)) 

You are asking for trouble with that. You explicitly cast you column to a decimal type that can be as long as 10 characters, but in the same breath you attempt to truncate it to 7 characters.

Consider this code.

declare @MyDec decimal(9,3) = 123456.789

--all is fine here. Your decimal has as many places as it can hold.
select @MyDec

--kapow!!! Arithmetic overflow because the value is too big.
select CAST(@MyDec as varchar(7))





_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1363013
Posted Friday, September 21, 2012 3:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 1,950, Visits: 20,197
If you are talking about formatting your code into a more readily readable layout then there are several "tools/addins" that will allow you to do this...along with allowing you to fine tune the output to your personal preference.

One I have used (no connection) is ApexSQL refactor.....last time I looked it was free
(http://www.apexsql.com/sql_tools_refactor.aspx)
Please note that there are several tools like this ...take a Google and choose what you feel happy with.

these can take adhoc layout (as written) and format code from something like this (deliberately brutalised )

SELECT o.JOB_NUMBER AS Job# , ORDER_NO AS Order# ,  FORM_NO AS Form , 
CUST_IDENT AS SpecID , QTY_ORDERED AS QtyOrdered ,
ORD_PRICE AS UnitPrice , PRICING_METH AS UOM ,
s.COLOR_DESC AS Colors , opsused.PreStamp , opsused.Decorative ,
opsused.Emboss , DESIGN_NO AS Drawing ,
CAST (
CAST( s.dim_A AS decimal( 9 , 3 ))
AS varchar( 7 )) + ' x ' + CAST( CAST( s.DIM_B AS decimal( 9 , 3 ))
AS varchar( 7 )) + ' x ' + CAST( CAST( s.DIM_C AS decimal( 9 , 3 ))
AS varchar( 7 ))AS Dimensions ,
gf.DESCR AS Board , DUE_DATE AS DueDate , COMPLETION_FLG AS OrderStatus
FROM ORDERS AS o LEFT JOIN SPECS AS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE AS gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN( SELECT JobOpsList.JobNum , CASE jobopslist.prestamp
WHEN 0 THEN '' ELSE 'Y' END AS PreStamp , CASE jobopslist.Decorative
WHEN 0 THEN '' ELSE 'Y' END AS Decorative , CASE jobopslist.Emboss
WHEN 0 THEN '' ELSE 'Y' END AS Emboss FROM( SELECT JobList.JobNum ,
SUM( CASE ops.Mach_No WHEN 288 THEN 1 ELSE 0
END )AS PreStamp , SUM( CASE ops.Mach_No
WHEN 290 THEN 1 ELSE 0 END )AS Decorative ,
SUM( CASE ops.Mach_No WHEN 292 THEN 1 ELSE 0
END )AS Emboss FROM
(
SELECT JOB_NUMBER AS JobNum ,
CASE ISNULL( o.COMBO_NO , '' ) WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END AS MfrSpec FROM ORDERS AS o
WHERE o.CSCODE = '2507' AND ORDER_DATE >= '1/1/2012' AND COMPLETION_FLG <> 'X'
GROUP BY JOB_NUMBER , CASE ISNULL( o.combo_no , '' )
WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END )AS JobList LEFT JOIN OPERATIONS AS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN( 288 , 290 , 292 )
GROUP BY JobNum )AS JobOpsList )AS OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507' AND ORDER_DATE >= '1/1/2012' AND COMPLETION_FLG <> 'X';

to something like this...in a click

SELECT o.JOB_NUMBER AS Job# , 
ORDER_NO AS Order# ,
FORM_NO AS Form ,
CUST_IDENT AS SpecID ,
QTY_ORDERED AS QtyOrdered ,
ORD_PRICE AS UnitPrice ,
PRICING_METH AS UOM ,
s.COLOR_DESC AS Colors ,
opsused.PreStamp ,
opsused.Decorative ,
opsused.Emboss ,
DESIGN_NO AS Drawing ,
CAST( CAST( s.dim_A AS decimal( 9 , 3 ))AS varchar( 7 )) + ' x '
+ CAST( CAST( s.DIM_B AS decimal( 9 , 3 ))AS varchar( 7 )) + ' x '
+ CAST( CAST( s.DIM_C AS decimal( 9 , 3 ))AS varchar( 7 ))AS Dimensions ,
gf.DESCR AS Board ,
DUE_DATE AS DueDate ,
COMPLETION_FLG AS OrderStatus
FROM
ORDERS AS o LEFT JOIN SPECS AS s ON s.SPEC_NO = o.SPEC_NO
LEFT JOIN GRADE_FILE AS gf ON gf.GRADE_CD = s.GRADE_CD
LEFT JOIN(
SELECT JobOpsList.JobNum ,
CASE jobopslist.prestamp
WHEN 0 THEN '' ELSE 'Y'
END AS PreStamp ,
CASE jobopslist.Decorative
WHEN 0 THEN '' ELSE 'Y'
END AS Decorative ,
CASE jobopslist.Emboss
WHEN 0 THEN '' ELSE 'Y'
END AS Emboss
FROM(
SELECT JobList.JobNum ,
SUM( CASE ops.Mach_No
WHEN 288 THEN 1 ELSE 0
END )AS PreStamp ,
SUM( CASE ops.Mach_No
WHEN 290 THEN 1 ELSE 0
END )AS Decorative ,
SUM( CASE ops.Mach_No
WHEN 292 THEN 1 ELSE 0
END )AS Emboss
FROM
(
SELECT JOB_NUMBER AS JobNum ,
CASE ISNULL( o.COMBO_NO , '' )
WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END AS MfrSpec
FROM ORDERS AS o
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X'
GROUP BY JOB_NUMBER ,
CASE ISNULL( o.combo_no , '' )
WHEN '' THEN o.SPEC_NO ELSE o.COMBO_NO
END )AS JobList LEFT JOIN OPERATIONS AS ops ON ops.SPEC_NO = JobList.MfrSpec
WHERE ops.MACH_NO IN( 288 , 290 , 292 )
GROUP BY JobNum )AS JobOpsList )AS OpsUsed ON OpsUsed.JobNum = o.JOB_NUMBER
WHERE o.CSCODE = '2507'
AND ORDER_DATE >= '1/1/2012'
AND COMPLETION_FLG <> 'X';



______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1363021
Posted Friday, September 21, 2012 9:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 35,827, Visits: 32,498
I not only use formatting to help organize things, I also include comments for every subquery. I also make sure that every column returned by every query and subquery have proper table aliases and that the aliases actually mean something especially when self-joins are present.

Depending on the performance of the code, I may also break the code up into smaller and tighter pieces and store the interim results in a TempTable or two so I don't have to work with such large data sets. Sometimes, the performmance improvement is quite dramatic. I recently took such an "all-in-one" query (35 tables joined including some self joins) that virtually crippled the server it was running on for 45 minutes and divided it up in such a manner. The code now runs so fast that it usually doesn't even show up on the Task Manager performance chart.

Getting back on topic, the splitting up of the code also allows for more pertinent comments for each section of the code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363064
Posted Saturday, September 22, 2012 2:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
Jeff Moden (9/21/2012)
Depending on the performance of the code, I may also break the code up into smaller and tighter pieces and store the interim results in a TempTable or two so I don't have to work with such large data sets. Sometimes, the performmance improvement is quite dramatic. I recently took such an "all-in-one" query (35 tables joined including some self joins) that virtually crippled the server it was running on for 45 minutes and divided it up in such a manner. The code now runs so fast that it usually doesn't even show up on the Task Manager performance chart.

I agree wholeheartedly with this. It is not a magic bullet by any means, but smaller (and more relational) queries are very often easier for the optimizer to reason about. The availability of statistics (on the temporary tables used to hold intermediate results) can also be a big factor. Large all-in-one queries are also generally harder for humans to understand and maintain, regardless of how well you lay them out on screen.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363081
Posted Saturday, September 22, 2012 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
First! Thank you all for taking the time to reply. I appreciate all the feedback.

A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports. Since starting I have added dumping data/information into Excel, which is becoming more and more frequent. No formal SQL Server or SQL education, all via books and the internet - like SQL Server Central. I'm at a stage where I am using more and more Pass Through Queries in Access. Along with that I find I start solving most query problems by using SSMS instead of Access Query Design. Especially when the request is one of those ad hoc ones for the results in Excel.

This "little project" was a customer request that got modified after I gave them a 1st pass. The problem I encountered was the statement was on so many lines I had to scroll up and down to select parts and selected at wrong points. Then I would have to start over - good thing I keep a copy of the last good statement.

Sean - Regarding the CAST and VARCHAR(7). I know the values will not exceed 48.999 and the decimal will be in 1/8's of an inch increment. So, I guess 5,3 would have been "better". I perked up reading your comment - I visualized that it 'jumped out at you'. That's Cool!

J - I wasn't asking about the formatting, per se, except that I do realize it does make a big difference in readability, which is a first step of the answer to my question. I've been using Instant SQL Formatter, considering purchasing Pretty Printer. Thanks for the tip about ApexSQL. I'll add that to my list of candidates.

Jeff - Comments embedded in the statement. Sheesh! (To myself) It never occurred to me.

Breaking up code - this will be new territory for me. Do you (anyone reading this) have any suggested reading materials? Either internet or books. There's a statement on my resume that's so true, "The road to success is always under construction."

Paul - Thanks for echoing support to Jeff's comments. It's good to hear when someone does that - seems to drive the point home.

Again, thanks everyone for your help!



"The Road To Success Is Always Under Construction."
Post #1363107
Posted Saturday, September 22, 2012 11:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 1,945, Visits: 3,203
SQL is declarative programming, so the mindset is more top-down than your bottom-up approach. Start with a single simple sentence that says what you want to do, no clauses or qualifiers; think verb-object. Gather all the tables involved and put them in a FROM clause. Write a list of all the columns you want in the final query result.

Do not do computations and display in that list, like you are doing! Do not change names of the data elements like you are doing! That stuff is for the presentation layers. This is how we did it in COBOL, when the data and process were melded in one monolithic block of code.

The hard part is the WHERE clause since it does the joins and filterS. There should be few (if any) OUTER JOINs in a correct schema. It tells us that the schema is bad and you have to kludge it together as you query, over and over and over. You have four in one query; I might have that many in an entire system.

For example, when you say “LEFT OUTER JOIN Grade_File AS GF ON GF.grade_cd = S.grade_cd” probably should have been done with a REFERENCES in the DDL and not with a join at run time that will be repeated hundreds of thousands of times.

We do not use flags in SQL; that was assembly language and file systemS. Tables are parts of a schema, so we can use predicates to find the current state of the data. Files could not talk to each other, so we set flags for the next sequential (non set-oriented) process step to find. Your “completion_flg AS order_status” is a mess; these are totally different kinds of attributeS. Is it a flag or it a status? I will guess status since you also violated Netiquette by not posting DDL.

Never use # or other special characters in a data element name; they have meaning in other languages and violate ISO standards. We use the ISO-11179 rules; [role_]<attribute>_<attribute property>, so it is “order_qty” and not the adverbial “qty_ordered” you had. Why do you use “_num”, “#”, “_number” and “_no” for the same attribute property (I like “_nbr” for tag numbers)? This tells me you have no master data management in your company. You also failed to qualify all the columns in the SELECT list so we have no idea which table has them.

You have table names like “Grade_File” and “Job_Ops_List”; those are data structures we do not use RDBMS. The only date format in Standard SQL and the other ANSI/ISO standards is ISO-8601 (yyyy-mm-dd).

Think about how deeply nested this thing is. At one level you write:

SUM(CASE OPS.mach_nbr WHEN 288 THEN 1 ELSE 0 END) AS prestamp_cnt

I added the “_cnt” attribute property for you. Now you pass this up a level of nesting to:

CASE Job_Ops_List.prestamp WHEN 0 THEN '' ELSE 'y' END AS prestamp_flg

I added the “_flg” attribute property for you. You destroyed information about the count! You put display formatting in a query! Let the presentation layer decide if they wan to dumb it down and save yourself a huge, expensive subquery.

My guess is that you need a lot of VIEWs that will be re-used, but without DDL or specs it is only a guess based on 25 years of fixing SQL. For example:

CREATE VIEW Job_Option_Counts
(job_nbr, prestamp_cnt, decorative_cnt, emboss_cnt)
AS
SELECT job_nbr,
SUM(CASE OPS.mach_nbr WHEN 288 THEN 1 ELSE 0 END),
SUM(CASE OPS.mach_nbr WHEN 290 THEN 1 ELSE 0 END),
SUM(CASE OPS.mach_nbr WHEN 292 THEN 1 ELSE 0 END),
FROM Orders AS O
WHERE O.cs_code = '2507'
AND O.order_status <> 'x'
GROUP BY O.job_nbr;

Does that help?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1363145
Posted Sunday, September 23, 2012 5:43 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
CELKO (9/22/2012)
Does that help?

It might; but it would have been more likely to if you had read this before replying:

EdA ROC (9/22/2012)
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363215
Posted Sunday, September 23, 2012 5:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:45 AM
Points: 9,928, Visits: 11,208
EdA ROC (9/22/2012)
A little background about me and my situation. We have a 3rd party ERP system, SQL Server based. My primary job is to use Access to develop reports.

I'm no Access expert, but I think it is true to say that pass-through queries have to be a single SELECT statement. If you are able to create objects in the SQL Server, you could consider creating a stored procedure there. Access can call a stored procedure, and SQL Server stored procedures can contain multiple statements and use temporary objects like table variables and temporary tables.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363216
Posted Sunday, September 23, 2012 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394

So much to learn! And so little time. (ha, ha)

This job has been quite the learning experience for me. Before 2004, when I started, I had developed two custom ERP packages over the span of 24 years. Both with what I call linear programming languages - an obscure INFO by HENCO, and then a Pick like language. When I started here I had little knowledge of Access and none of OOP and SQL. I am getting better all the time, but I still haven't mastered thinking OOP (I catch myself still translating from linear to OOP). Pretty pleased with myself that I'm creating more and more SQL statements without Access Design, it's challenging, but fun!

Celko - Thanks for your comments.
I understand "the rule" about leaving formatting to the presentation layer. But, like driving a car, we break rules for convenience ... a little over the speed limit, not leaving the recommended distance between cars, etc. The "losing the count" was no problem, the request was for whether or not the operations existed. To me, what I did was quicker than waiting until the data was copied into Excel then ... create a column, add =IF(F1>0,"Y",""), fill down, insert another column, copy & paste special:values, and finally deleting the initial column.
I like your advise about the steps for constructing a statement. I copied them down on an index card, will tape it to my monitor at work and follow the steps. One part I already do - identifying the data sources before beginning the assembly process.

As for names? As I mentioned, the database isn't my design. You should see some of the things they do. For example, A Job (JOB_NUMBER) consists of one or more Orders (ORDER_NO). Yet, in some tables they use ORDER_NO for a Job#. You should see all the notes I have in my Data Dictionary.

Hey Everyone! Thanks again and have a fun week.


"The Road To Success Is Always Under Construction."
Post #1363225
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse