December 5, 2016 at 12:37 pm
drew.allen (12/2/2016)
JALLY (12/2/2016)
Your solution above will create a conflict between columns that rely on the the sub queries for their source of data and the multi part identifier 'inv.active' will not be bound to the inventory table because it is outside of a sub query: for example :osu.SalesPerson is from the following subquery part identifier. if i add the where clause at the end of the script. the where clause will not be able to bind on to the inventory table due to the 2nd sub query bound by osu following it.
No, it won't. You have nested subqueries and inv is defined in the outer subquery, so it can be used ANYWHERE in the main subquery. The second subquery creates a new scope WITHIN the scope of the main subquery. It does not close the scope of the main subquery.
Drew
Thanks a lot drew, i did as you advised above and the script worked perfectly.
I learn from the footprints of giants......
December 6, 2016 at 1:51 pm
>> see script below: <<
We need to see the DDL. Did you read the forum rules before you posted? What you have posted is really awful. You do not know the ISO 11179 naming rules, you are doing display formatting with COBOL style strings in the database and have no idea what a tiered architecture should be.
But the truly awful, awful thing is your belief in a magic universal generic “id”; that is Kabbalah magic and not RDBMS. Sometimes the id is an order number, sometimes a “line_<something>”, and probably an automobile squid or Lady Gaga.
My guess is that your generic “id” are a way of mimicking pointer chains in a nineteen seventies network database. Instead of doing proper RDBMS joins, you are building pointer chains!
Then you give data elements their own names as aliases! This is the RDBMS equivalence of the Monty Python “Bruce” routine. But it seems are doing it to change the capitalization in the display name. Remember the first week of your SQL class? The concept of a tiered architecture in which we have a database layer that passes a result set to a presentation layer that handles that kind of stuff. For example, in SQL. We would pass the billing cycle code to the presentation layer, instead of putting in the case expression and doing the display work here in the database layer.
We also would probably not put in a constant address in the database layer. That is how you had to do it in COBOL with its monolithic data model in which the data is retrieved and the physical labels are printed in one layer.
Many years ago, the old Sybase ISNULL() was replaced with the ANSI/ISO standard COALESCE(). You talk about a “week_id”, But not the ISO week number. I have been writing SQL for over 30 years, I have never written a query with this many tables in it. In fact, the rule of thumb is that the query should have less than five table references in the from clause. This has to do with how fast the number of possible joins increases and the ability of a human being to handle five or more things. I hope that “is_active” is not a bit flag; that would mean you are using assembly language programming in SQL and we do not do that.
In short, you suffer from an incompetent design, with badly written code on top of that. Can you start over? We can probably get make guesses and come up with kludges, but would you rather do it right instead of having it collapsed when the database has to be maintained or the size of increases in a few years? This is the difference between being a competent professional and a cowboy coder.
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
December 6, 2016 at 2:26 pm
CELKO (12/6/2016)
I hope that “is_active” is not a bit flag; that would mean you are using assembly language programming in SQL and we do not do that.
Not that means that he is using the tools at hand. How many times do you have to be reminded that T-SQL does not support the Boolean data type that ANSI SQL recommends in place of the bit flag?
If you don't like people using a bit flag in T-SQL complain to MS about the lack of a Boolean data type. Until they implement it you have no basis to complain about people using a bit flag.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2016 at 6:48 pm
How many times do you have to be reminded that T-SQL does not support the Boolean data type that ANSI SQL recommends in place of the bit flag?
[/quote]
Actually, I believe that the current version standards deprecated Boolean flags, but I would have to look. It has really horrible structural and logical flaws because of trying to wedge 2-VL into 3-VL logic models. Dave McGovern is much better at all of these problems that I am, so if you can find posting on the Internet that he did read them and I hope you have at least a Masters degree in logic and mathematics.
is a If you don't like people using a bit flag in T-SQL complain to MS about the lack of a Boolean data type. Until they implement it you have no basis to complain about people using a bit flag.
I am not complaining about the existence of Boolean datatypes, because SQL is based on a three value logic. I am complaining about the use of flags. Back in the dinosaur days (e.g. when I started in in the 1960's). There was no concept of any job being interconnected that what we would now call a schema; a global, unified model of the universe of discourse (a term from formal logic).
The original the BIT data type in SQL Server was what computer science majors think it is {0, 1}, that he became a numeric type in all numeric types are NULL-able so it was {null, 0, 1}. I made a couple of bucks of telling people they should of had an is null constraint in their schema.
But beyond implementation issues, there are logical issues. SQL is a predicate language; we discovered the state of the schema (as a whole) with predicates. We do not to record by record testing a predicates that will never be read again like we did in operating systems, low-level assembly language stuff etc. etc.
We use flags 50 years ago because we could not get enough computing power with a little narrow window into the data, into the model of our world; so we had to leave "breadcrumbs" for those that were coming after us; the programming style was dictated by physical limitations. In those days. Operating systems and other low-level assembly language units still have some of this problem; they cannot see the universe (and when the computer can see the entire universe understand it were probably all gone):w00t:
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
December 6, 2016 at 7:05 pm
Hello Guys,
Following the code that drew modified, I found another way around it:
SELECT
StationName
, AiredDateTime
, AiredISCI
, OrderNumber
, Line
, Length
, FlexOrderRate = FlexOrderRate
, InvoiceNumber
, InvoiceDate = CONVERT(VARCHAR(20), InvoiceDate, 1)
, P.AgencyRate
, P.StationAddress
, P.StationCityZip
, Product
, P.BillingCycle
, AdvertiserName
, AgencyName
, P.SalesPerson
, P.Buyer
, P.AgencyAddress
, P.AgencyAddress2
, P.AgencyStateCity
, StationID
FROM
(
SELECT
StationName = s.Name
, ios.AiredDateTime
, ios.AiredISCI
, OrderNumber = oh.id
, Line = od.id
, cd.Length
, cd.FlexOrderRate
, InvoiceNumber = ih.id
, InvoiceDate = ih.invoicedate
, AgencyRate = CONVERT(INT, od.AgencyRate)
, StationAddress = 'Westwood One, Inc'
, StationCityZip = '3544 Momentum Place
Chicago, IL 60689'
/*sa.Address AS StationAddress,
ISNULL(sa.City,'')+', '+ISNULL(sa.state,'')+' '+ISNULL(sa.Zipcode,'') AS StationCityZip,*/
, Product = oh.ProductDescription
, BillingCycle = CASE
WHEN oh.BillingCycle = 'F' THEN
'End OF Flight'
WHEN oh.BillingCycle = 'M' THEN
'Monthly'
WHEN oh.BillingCycle = 'W' THEN
'Weekly'
ELSE
NULL
END
, AdvertiserName = c.name
, AgencyName = cag.Name
, SalesPerson = osu.SalesPerson ---(column referencing the second sub query below )
, Buyer = cc.FirstName + ' ' + cc.LastName
/*ca.Address1 AS AgencyAddress,
ISNULL(ca.Address2,'') AS AgencyAddress2,
ISNULL(ca.City,'')+', '+ISNULL(ast.Abrv,'')+' '+ISNULL(ca.Zip,'') AS AgencyStateCity,*/
, AgencyAddress = 'DIRECT RESULTS '
, AgencyAddress2 = '931 VILLAGE BLVD #905-507'
, AgencyStateCity = 'WEST PALM BEACH, FL 33409'
, StationID = s.ID
FROM
dbo.InvoiceHeader ih
INNER JOIN dbo.InvoiceDetail ihd ON ih.ID = ihd.InvoiceHeaderID
INNER JOIN dbo.OrderSpot os ON os.InvoiceDetailID = ihd.ID
INNER JOIN dbo.InventoryOrderSpot ios ON ios.OrderSpotID = os.ID
INNER JOIN dbo.Inventory inv ON inv.id = ios.InventoryID
INNER JOIN dbo.ContractDetail cd ON cd.id = inv.ContractDetailID
INNER JOIN dbo.Station s ON s.id = cd.StationID
INNER JOIN dbo.OrderDetailWeek odw ON odw.id = os.OrderDetailWeekID
INNER JOIN dbo.OrderDetail od ON od.id = odw.OrderDetailID
INNER JOIN dbo.OrderHeader oh ON oh.id = od.OrderHeaderID
INNER JOIN dbo.StationAddress sa ON sa.stationID = s.ID
INNER JOIN dbo.Company c ON c.id = oh.AdvertiserID
INNER JOIN dbo.Company cag ON cag.id = oh.AgencyID
LEFT JOIN dbo.CompanyAddress cc ON cc.id = oh.BuyerCompanyContactID --(the where clause should be here i.e(where active =1))
LEFT JOIN
(
SELECT
SalesPerson = usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
, orderSalesID = MAX(os .id)
FROM
dbo.OrderSales os
INNER JOIN dbo.[User] usr ON os.UserID = usr.ID
WHERE IsPrimary = 1
GROUP BY
usr.FirstName + ' ' + usr.LastName
, os.orderHeaderID
) osu ON osu.orderHeaderID = oh.ID
LEFT JOIN dbo.CompanyAddress ca ON ca.CompanyID = oh.AgencyID
LEFT JOIN dbo.AddressState ast ON ast.id = ca.AddressStateID
Where Inv.Active =1 ) P----the where clause resides here for performance issues and allow filtering within the inner query.
WHERE
InvoiceNumber = @InvoiceNumber AND
StationName = @StationName
--AND (@StationName = 'ALL' OR StationName = @StationName)
AND
AiredDateTime IS NOT NULL AND
ORDER BY
StationName
, AiredDateTime DESC;
I learn from the footprints of giants......
December 7, 2016 at 6:48 am
CELKO (12/6/2016)
...The original the BIT data type in SQL Server was what computer science majors think it is {0, 1}, that he became a numeric type in all numeric types are NULL-able so it was {null, 0, 1}. I made a couple of bucks of telling people they should of had an is null constraint in their schema....
constraining this type of column to be NOT NULL would seem to eliminate the possibility of representing an unknown value, or are you suggesting that everything should be in a code/lookup type of table, even if the only applicable values are {true, false, unknown}?
December 8, 2016 at 3:55 pm
constraining this type of column to be NOT NULL would seem to eliminate the possibility of representing an unknown value, or are you suggesting that everything should be in a code/lookup type of table, even if the only applicable values are {true, false, unknown}?
I agree with using the not null constraint whenever possible. My point was that originally the Sybase SQL Server BIT was not defined that way. The reason was simple; programmers were used to having a Boolean/hardware level mindset and found the three value logic of SQL really alien. So when they move their code over after Microsoft made BIT a numeric data type, lots of people did not bother with that not null constraint. And frankly, it was really hard to see when you are not used to three value logic.
I have a rule of thumb that when the set of values in a domain is "small and static" then use CHECK (x IN (..)) In the DDL. This will send information to the optimizer and save you the trouble of doing references to look up table. However, the set of values is "large or dynamic", then use a REFERENCES. Notice the quotes around the heuristics; today's "small" might have been "large" years ago.
https://www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql/
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply