Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

Hide or show row in sql result depending on vaule

Hi,

I have a query looking like this:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, o.orderDate, ISNULL(paymentStatusID, 0) AS paymentStatusID, o.paymentTypeID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentType, shipmentType, IsNull(sentOrder, 0) AS sentOrder  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
INNER JOIN bklShipmentType AS bst ON bst.shipmentTypeID = o.shipmentTypeID 
INNER JOIN bklPaymentType AS bpt ON bpt.paymentTypeID = o.paymentTypeID 
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND orderTypeID = 1 AND orderStatusID NOT IN (5, 8) AND o.paymentTypeID <> 12 
ORDER BY orderDate DESC 

Open in new window


The result of the query looks like this:
sql.png
I want to change the query so that if paymentTypeID is 5 or 15 then paymentStatusID must be 2 or else the row shall not be part of the resultset.

How can I accomplish that?

Peter
0
peternordberg
Asked:
peternordberg
  • 5
  • 4
  • 3
1 Solution
 
SimonCommented:
Hi, please try with this where clause. I've broken it into separate lines for clarity, you don't have to.

WHERE (o.invoiceID > '0')
AND (o.orgID = 11)
AND orderSaved = 1
AND orderTypeID = 1
AND orderStatusID NOT IN (5, 8)
AND
(
   o.paymentTypeID  NOT IN(12,5,15)
   OR
   o.paymentTypeID  IN(5,15) AND paymentStatusID=2
)
0
 
GaryCommented:
Might want to bracket that OR condition
0
 
SimonCommented:
@Gary - 'might want to' for readability or because of unexpected results? I'm always willing to defer to greater expertise, but have tested this on SQL fiddle and appears fine as is. I'd agree it could be bracketed for clarity, but I normally rely on T-SQL operator precedence AND>OR and dislike extraneous parentheses.

Here's the whole select statement for testing, with extra parentheses for clarity...
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, o.orderDate, ISNULL(paymentStatusID, 0) AS paymentStatusID, o.paymentTypeID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentType, shipmentType, IsNull(sentOrder, 0) AS sentOrder  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
INNER JOIN bklShipmentType AS bst ON bst.shipmentTypeID = o.shipmentTypeID 
INNER JOIN bklPaymentType AS bpt ON bpt.paymentTypeID = o.paymentTypeID 
WHERE (o.invoiceID > '0')
AND (o.orgID = 11)
AND orderSaved = 1
AND orderTypeID = 1
AND orderStatusID NOT IN (5, 8)
AND
( o.paymentTypeID  NOT IN(12,5,15) OR (o.paymentTypeID  IN(5,15) AND paymentStatusID=2)) 
ORDER BY orderDate DESC 

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GaryCommented:
More for readability, not everyone knows the order and just makes more sense when you look back at it.
(...and I think it's better practise but thats a personal view)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not for readability. It really needs the brackets there or else will filter only for paymentStatusID=2 no matter the paymentTypeID.
0
 
SimonCommented:
I don't wish to seem disagreeable, but on my 2008R2 system, this works as advertised. It does not filter out PaymentStatusID values <>2, apart from records with PaymentTypeID in (5,15)

I've  simplified the table structure and query to omit the joins in the example below.
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] NULL,
	[InvoiceID] [int] NULL,
	[OrgID] [int] NULL,
	[OrderSaved] [int] NULL,
	[OrderTypeID] [int] NULL,
	[OrderStatusID] [int] NULL,
	[PaymentTypeID] [int] NULL,
	[PaymentStatusID] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Orders] 
VALUES (1, 1, 11, 1, 1, 1, 12, 2)
,(2, 2, 11, 1, 1, 1, 5, 2)
,(3, 3, 11, 1, 1, 1, 5, 3)
,(4, 4, 11, 1, 1, 1, 15, 2)
, (5, 5, 11, 1, 1, 1, 15, 3)
,(6, 6, 11, 1, 1, 1, 1, 14)
,(7, 7, 11, 1, 1, 1, 12, 3)
,(8, 8, 11, 1, 1, 1, 1, 7)
GO

SELECT o.orderID, o.invoiceID 
, ISNULL(paymentStatusID, 0) AS paymentStatusID, o.paymentTypeID, orderStatusID
FROM orders AS o  
 
WHERE (o.invoiceID > '0')
AND (o.orgID = 11)
AND orderSaved = 1
AND orderTypeID = 1
AND orderStatusID NOT IN (5, 8)
AND
o.paymentTypeID  NOT IN(12,5,15) OR o.paymentTypeID  IN(5,15) AND paymentStatusID=2

Open in new window


table content and query results
@Vitor, I'd really appreciate if you would paste my code into a query and run it to see if you get different results to me.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Simon, I did test it and worked as you said but then means that it's a SQL Server way of work. If you want to transfer the query to another RDBMS I don't think it will work as expected.
Then at least for readability you should use the brackets so the author knows that if for some reason he needs to move the paymentStatusID=2 he needs to move everything between the brackets.
0
 
SimonCommented:
Point taken about bracketing for readability and portability, Vitor. I appreciate you taking the time to test it. Thanks.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It was also good that you had the time to clarify it. I was so sure that the brackets would make all the difference in logic but you showed me that SQL Server uses the precedence of the operators. Thanks for that also. Living and learning :)
0
 
GaryCommented:
Should be the case in all DB's, certainly in MySQL it would work without the brackets.
0
 
SimonCommented:
Thanks both. In that case I stand by my earlier post as being the correct solution (with assistance from Vitor and Gary) :-)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No points for me please. My statement wasn't right.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now