July 16, 2022 at 12:53 pm
Frederico, I'm confused.
How does the following code, give me the results I need:
SELECT C1.Id
, C1.SinkCreatedOn
, C1.SinkModifiedOn
, C1.statecode
, C1.statuscode
, C1.prioritycode as C1_prioritycode
, C2.prioritycode as C2_prioritycode
, CASE
WHEN ISNULL(CAST(C1.prioritycode AS VARCHAR(50)),'')
<> ISNULL(CAST(C2.prioritycode AS VARCHAR(50)),'')
THEN CAST(C2.prioritycode AS VARCHAR(50))
ELSE CAST(C1.prioritycode AS VARCHAR(50))
END AS prioritycode
I didn't say it would give your desired results - only that it would help you understand why you are getting null values.
Seems you need to both understand your data in isolation as well as understand how the row_number over() works - see this last one here https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16 - search for "null values"
with regards to your own data - I would not normally do this as YOU have to understand and learn on your own and you were giving the pointer - but execute the 2 queries in isolation as follows.
select prioritycode, rownum, *
from (
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM appointment
) C1
order by c1.rownum
select prioritycode, rownum, *
from (
SELECT *,ROW_NUMBER()OVER(ORDER BY prioritycode)RowNum FROM optionsettransposedall_why
) C2
order by c2.rownum
note that I have placed the 2 important fields at the start.
run the above queries twice - the second time change the outside order by to be desc(ing)
It is this type of things that you need to practice in order to understand your data - and why the results are not what you expect.
July 16, 2022 at 1:39 pm
Hi Frederico,
I really appreciate what you're doing for me, and may be its because I'm under pressure that I'm not fully understanding what you're driving at. However, I am working with the samples you provided, but I'm still not getting it.
I understand you don't like to just provide answers - I understand the saying "Teach A Persson to Fish" but you could on just this single occasion provide the correct query code I would be most grateful.
July 16, 2022 at 1:49 pm
Also, part of the question, 'why I get output with table ending _copy and not the other table ending _why'?
July 16, 2022 at 1:55 pm
Hi Frederico,
I really appreciate what you're doing for me, and may be its because I'm under pressure that I'm not fully understanding what you're driving at. However, I am working with the samples you provided, but I'm still not getting it.
I understand you don't like to just provide answers - I understand the saying "Teach A Persson to Fish" but you could on just this single occasion provide the correct query code I would be most grateful.
that is where the issue is - you only asked for help to "remove CTE" and replace with something else - on this matter you refused to tell us what is the software and db version you are using that does not allow for CTE.
Other than that you then said on your "reduced" query you got some values on column PriorityCode but nothing else in terms of what are the requirements other than joining on rownum ... which seems to me to be incorrect, and by you complaining it does not give you your expected results it also seems to be wrong on your side as well. On this one you "likely" also need to join on the column ID - but as we have no idea of what your data is and your requirements are this may be way wrong.
so if you do really wish us to help us you need to supply a lot more info - including the full requirement (in english, not pseudo code), sample inputs (including table creation DDL, and insert statements) as well expected output and REASON why the output should be as you expected it to be.
July 16, 2022 at 3:12 pm
Hi Frederico, I think you're referring to one of my previous questions :-). I haven't asked for help in removing CTE's in this question 🙂
July 16, 2022 at 3:38 pm
Why are you joining on row number? How many rows will that actually match?
July 16, 2022 at 3:59 pm
It was code that was provided to me to fix an issue.
Its hard to determine the number of rows that will be matched.
The thing that is troubling me is that I can see the data when I run the code on table ending with _copy, but I can't see the data when I run the say code on table ending with _why. I simply don't get understand.
I believe its something to do with ORDER BY C1.Row Num, but I've tried that with no luck.
I'm simply asking for someone to help me out with the actual code
July 16, 2022 at 4:38 pm
It was code that was provided to me to fix an issue.
Its hard to determine the number of rows that will be matched.
The thing that is troubling me is that I can see the data when I run the code on table ending with _copy, but I can't see the data when I run the say code on table ending with _why. I simply don't get understand.
I believe its something to do with ORDER BY C1.Row Num, but I've tried that with no luck.
I'm simply asking for someone to help me out with the actual code
And that is the root of the problem - no one can help you out with the 'actual code' because no one here understands the *actual* issue you are having. Many questions have been asked for additional information, table definitions, sample data *and expected results*.
The fact that you don't understand why you get rows from one table - and don't get rows from another table - is almost certainly because the data in each table is different. Different data means different results...
You are creating a row number based on the order of the values in the column prioritycode. But that isn't a unique value for every row in the table - so what exactly are you expecting that row number value to be? If you have 10 rows with 'Normal', 8 rows with 'Low' and 16 rows with 'High' - what value should row_number() over(order by prioritycode) return?
Now - consider the row number values coming from the appointment table. Let's assume you have 200 rows with 'Normal' - and those rows have been assigned some row number value - how would that join to the other table(s) correctly?
Final note: instead of dumping a table with 50+ columns in a forum - remove all but the necessary columns to show the issue(s) you are having and the desired results. There are a lot of columns and data in these tables that is just clutter - it has nothing to do with the issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2022 at 4:50 pm
Hi Jeffrey,
Thanks for getting in touch.
"Many questions have been asked for additional information, table definitions, sample data *and expected results*."
I have provided all of the above - tbh, some might say I've provided too much.
The question is simple, 'why am I not able to see data from the 'proritiycode' field running the the code on table ending with _why?
I don't understand why I getting such vitriol from people in this forum?
July 16, 2022 at 5:26 pm
you are not seeing what you wish because of how the row_number works (did you go and read the manual I gave?) - and as you didn't look at the 2 individual queries I asked you to run you also don't know what your data looks like.
finally - selecting values from 2 tables with multiple rows each and doing a rownumber on a field that contains duplicate values (including nulls) and then joining on that rownumber is never going to give you anything good (it is what I would call "garbish" data).
so unless you give us the original query you are trying to sort (and I hope is not the one with CTE and join on rownum as you have on another thread (e.g. the one that was rewritten)) and we may be able to help you - on this case there is little point in trying to do anything - as even if you do a change on the order by of row_number , which will give you your "desired" values instead of nulls, that data is worthless and you better not present that to any business user.
July 17, 2022 at 3:02 pm
Hi Jeffrey,
Thanks for getting in touch.
"Many questions have been asked for additional information, table definitions, sample data *and expected results*."
I have provided all of the above - tbh, some might say I've provided too much.
The question is simple, 'why am I not able to see data from the 'proritiycode' field running the the code on table ending with _why?
I don't understand why I getting such vitriol from people in this forum?
It isn't vitriol - people are trying to help you but are not able to help because you keep coming back and stating you don't understand why you can't see the data. You also have posted a ton of data that is irrelevant to the issue with lots of extra columns and text that just isn't needed to see the issue.
Let's take a simpler case:
Appointment Id 6ec988ae-b089-e811-a95f-00224800c719 has prioritycode = 1. What is the expected result when joined to the table optionsettransposedall_copy? What is the expected result when joined to optionsettransposedall_why?
And why do you expect those results? What is the logic you are using to determine that the prioritycode should be equal to 'Normal' and not 'Low' or some other value?
Now - take a really good look at the differences in the data between those 2 tables. The _why table has multiple rows for each participationtypemask where the _copy table does not. The _copy table has NULL values for that column - the _why table does not.
Now - take a look at the logic being used to return that column. What the statement is saying is: If the prioritycode in C1 (Appointment) is not equal to the prioritycode in C2 then return the value from C2. The prioritycode in Appointment appears to be a numeric code and in your sample data all rows have a value of 1 for the prioritycode.
Given the value 1 is not equal to 'Normal', 'Low', 'High' - you will always be returning the value from the other table and because you have an arbitrary number being assigned there is no way to know what value will be returned.
Maybe this will make more sense:
_copy table has 14 rows in your sample - so you will have a row_number() value assigned from 1 to 14.
_why table has 16 rows in your sample - so you will have a row_number() value assigned from 1 to 16.
Let's assume your appointment table has 1000 rows with a row_number() value assigned from 1 to 1000. What happens when you look at row 20 in appointment using either option table? You are joining on the row number and there doesn't exist a row 20 in the option table - so what should be returned and why?
So this is really simple - figure out what you want the results to be and why you should get those results. Post that information as your *expected results*. Define that logic and tell us that logic so we can help you get those desired results.
I will not 'just write the code for you' - first off, I cannot do that because you have not provided any information on what the result should be and why it should be that value. Second, this isn't a code writing service - we do this on our own time because we like to help others learn and grow.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2022 at 9:47 pm
Jeffrey
I have just seen your response - its quite late here in London for me to reply in detail, but I just wanted to say I’ve glanced over response I thought I would say thank you.
I will reply in the morning
July 18, 2022 at 7:20 pm
Jeffrey,
I have fully read your comments - multiple times 🙂
I think I have been schooled - in a nice way. I have spent the last hour studying both tables, and I totatlly get what you're saying.
As you have been straight and honest with, I'll be straight with you - I was being lazy! I was under pressure to produce a result and I didn't think it through.
Haven't read your comments, I realise that if I want to enlist help from people taking their time to help me I should at least provide what results I expect, and provide better data to work with.
Cheers
July 18, 2022 at 8:24 pm
Jeffrey,
I have fully read your comments - multiple times 🙂
I think I have been schooled - in a nice way. I have spent the last hour studying both tables, and I totatlly get what you're saying.
As you have been straight and honest with, I'll be straight with you - I was being lazy! I was under pressure to produce a result and I didn't think it through.
Haven't read your comments, I realise that if I want to enlist help from people taking their time to help me I should at least provide what results I expect, and provide better data to work with.
Cheers
Thank you for that.
I think we would all like to help you get the results you need - have you been able to determine what is needed here?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2022 at 8:41 pm
have you been able to determine what is needed here?
TBH, my boss didn't like my approach to finding a solution to the issue - kinda echoed some of your sentiments to me today in the morning standup. Therefore, I'm going park this for now ....
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply