|
I think you mean SQL != MySQL. Different SQL implementations have different features, that is well known.
|
|
|
|
|
NULLs cannot be compared in SQL
|
|
|
|
|
Try using a case statement in the where clause to turn the bool in 1 or 0
where (case when intfield is null then 0 else 1 end) != (case when intfield is null then 0 else 1 end)
(why do you have int field that are null?)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why int field available NULL: To know wheter there have assigned a value. BTW the same "Problem" will be for any other datatype I assume.
case: Would be nice to have it. At the Moment I have to fight with Interbase SQL 
modified 19-Jan-21 21:04pm.
|
|
|
|
|
You need a logical operator to combine two boolean conditions.
In this example, you'd want the Xor operator. But unfortunately, MS SQL Server doesn't support it; it only provides a bitwise Xor operator, which won't work here.
You can either use CASE, as Mycroft suggested, or the longhand version of Xor:
a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)
... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you for your answer.
"The longhead version of Xor": That is the Thing a tried to write shorter when I one time again was struggling with the same described here:
The Weird and The Wonderful[^]
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Only for Information: MySQL 5.6 does evaluate it, like I expect it has to be evaluated. Tested on SQL Fidle.
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Strange, Could of swore when I first wrote this, I was just getting 1 record.
Then I changed the return value to List from IEnumerable and starting getting 3 records back for each record.
But I doubt that had anything to do with it. I did raise the .Net level to 4.5.2.
Must be my And Statement.
I tried Distinct but It doesn't seen to be working, most likely because I have no clue how use it here.
I started writing in c# and MVC and don't write like this anymore.
Public Shared Function load_COH_ListBox_Info(
ByRef pResults As List(Of cohIndex)) As Integer
Dim pValue As Integer = 0
Dim context As New hx5Context()
pResults =
(
From oh In context.Order_History
Where oh.OrderDate >= DbFunctions.AddDays(Today, -40) _
And
(
oh.OrderStatus = "COMPLETED" _
Or oh.OrderStatus = "ORDER_CANCELED" _
Or oh.OrderStatus = "ORDER_CANCELED_REFUND_COMPLETE"
)
Order By oh.OrderNumber Descending
Select New cohIndex With
{
.orderID = oh.CompletedOrderID,
.orderNumber = oh.OrderNumber,
.orderDate = oh.OrderDate,
.orderStatus = oh.OrderStatus,
.accountName = oh.LoginID,
.total_NetCharge = oh.GrandTotal
}
).Distinct().OrderByDescending(Function(ob) ob.orderNumber).ToList()
pValue = pResults.Count()
Return pValue
End Function
If it ain't broke don't fix it
|
|
|
|
|
The 3 records each are in my database, let me fix that first
If it ain't broke don't fix it
|
|
|
|
|
Instead of removing them, you can just group them by their ID. 
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
I am surprised as to why you didn't consider using GroupBy function? That would be useful in grouping the objects by a property, so that only distinct ones are returned, since they will be grouped by their property in a similar manner, to what you want to expect from Distinct function call here. Something like this,
from oh in context.Order_History
where oh.OrderDate ...
orderby oh.OrderNumber desc
group oh by oh.RequiredProperty in ohGrouped
...
select ...
Something like this will be useful, as it will group the records by the properties which you specify. Please have a look here, [c# - Group by in LINQ - Stack Overflow](https://stackoverflow.com/questions/7325278/group-by-in-linq)
There is a library, which contains a lot of added features to LINQ, you should also look into it as it might also help you out with a lot of good function calls, such as this DistinctBy (DistinctBy takes the properties by which you want to select the records), get it here, GitHub - morelinq/MoreLINQ: Extensions to LINQ to Objects
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
How do I set this in a Sql query?
If code >= 1 or <= 9 then place two zeros if front...001, 002....009
if code >= 10 or <= 99 then place one zero if front....010, 050...,099
Thanks
**** NEVER MIND, I GOT IT:*********
when [EMPLOYEE].JOB_CODE >=1 AND [EMPLOYEE].JOB_CODE <= 9 then '00' + [EMPLOYEE].JOB_CODE
when [EMPLOYEE].JOB_CODE >=10 AND [EMPLOYEE].JOB_CODE <= 99 then '0' + [EMPLOYEE].JOB_CODE
modified 20-Jul-17 8:43am.
|
|
|
|
|
CASE
If current SQL Server, look at FORMAT.
modified 19-Jul-17 20:53pm.
|
|
|
|
|
Don't you mean ...
If code >= 1 AND <= 9 then place two zeros if front...001, 002....009
if code >= 10 AND <= 99 then place one zero if front....010, 050...,099
... ?
|
|
|
|
|
Correct - was in a rush to leave the office when I typed it.
I want to concatenate 00 in front of the value.
modified 20-Jul-17 8:31am.
|
|
|
|
|
For Microsoft SQL 2012 or higher, use FORMAT[^]:
FORMAT(code, 'D3')
For older versions, use CASE[^]:
CASE
WHEN code Between 1 And 9 THEN '00'
WHEN code Between 10 And 99 THEN '0'
ELSE ''
END + CAST(code As varchar(10))
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
you can do it without any case statement like this:
SELECT Right('00' + Convert(Varchar(10),[code]),2) as [Code]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Firstly, the second argument to Right should be 3, not 2.
And secondly, if the input number is 1000 or higher, that will strip the leading digits:
WITH cte (code) As
(
SELECT 1
UNION ALL SELECT 10
UNION ALL SELECT 100
UNION ALL SELECT 1000
UNION ALL SELECT 10000
)
SELECT
code,
RIGHT('00' + CONVERT(varchar(10), code), 3)
FROM
cte
; Output:
1 001
10 010
100 100
1000 000
10000 000
It's not clear whether that's what the OP wants, but I doubt it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You are correct to point out my mistake, but it's easily fixed.
There is, of course another way:
SELECT Format([Code],'000') as [Code]
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: There is, of course another way:
Which was the first thing I suggested above[^]!
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello,
Use REPLICATE() and RIGHT Functions,
SELECT RIGHT(REPLICATE(0,2)+CAST([EMPLOYEE].JOB_CODE AS NVARCHAR(200)),3) FROM YourTable
Regards,
Pradeep M
|
|
|
|
|
How do I set value to zero in a Pivot? I would like to set the NULL values to zero from attached code
Select * From
(select Partner, Facility, PAY_PERIOD_BEGIN_DATE, PAY_PERIOD_END_DATE, Job_Code, Pay_ClassHrs, Pay_ClassAmount, Pay_Hours, Pay_Amount from #PremierFinal) as sourcetable
PIVOT
(
sum(Pay_Hours)
FOR Pay_ClassHrs in (PBS_Hrs, POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
)as pvt
Quote: PBS_Hrs
NULL
NULL
NULL
NULL
792
278
NULL
|
|
|
|
|
Try using an ISNULL
e.g. ISNULL(sum(Pay_hours),0)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
It is not the SUM that I want to zero but the column values in
FOR Pay_ClassHrs in (PBS_Hrs, POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
example:
case when PBS_Hrs IS NULL then 0 end and so on...
Thanks
|
|
|
|
|
I tried it but it comes back with Syntax error, my environment is MS SQL SERVER Management Studio 2016:
FOR Pay_ClassHrs in (ISNULL(PBS_Hrs, 0), POT_Hrs, PED_Hrs, POR_Hrs, PJB_Hrs, PTO_Hrs, PWH_Hrs, PHR_Hrs, PNH_Hrs)
Any ideas...!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
