February 23, 2018 at 9:32 am
Hi everyone,
I am using the Query shown below to retrieve records. The value obtained in the column Calcluated_value should be the same as Required_value which is a column I have added here to the putput for comparing this with the front end screen of the application, how can I modify my query so that it shows the calculated_value to be the same as the front end values which I have shown here in Required_value column.
Thanks
IQ
Query:-
select distinct top 10000
AccountSerial,a."Rec ID",
a."Enc Reading", ,
STR(CAST(a."Enc Reading" AS DECIMAL)/1000,LEN(a."Enc Reading")+2,3 ) "Calculated Value",
-- Required_value are the values obtained from Front-end GUI application
from "Enc Reading Trans" a,
"Account Enc Xref" b
where a."Rec ID" = b."Rec ID"
and CONVERT(date, a."Reading Time") between '2017-01-06' and '2017-01-06'
and ISNUMERIC("Enc Reading") = 1 and AccountSerial in(101761,125293,505832,662847,183716,143976)
order by AccountSerial desc
Output:-
| AccountSerial | Rec ID | Enc Reading | Calculated_value | Required_Value |
| 662847 | 50297353 | 000478500 | 478.500 | 478.500 |
| 662847 | 50297353 | 000479000 | 479.000 | 479.000 |
| 505832 | 41201519 | 0012039 | 12.039 | 1203.900 |
| 505832 | 41201519 | 0012041 | 12.041 | 1204.100 |
| 183716 | 2698541 | 0017771 | 17.771 | 1777.100 |
| 183716 | 2698541 | 0017773 | 17.773 | 1777.300 |
| 143976 | 50108757 | 000812000 | 812.000 | 812.000 |
| 125293 | 2775591 | 0010455 | 10.455 | 1045.500 |
| 125293 | 2775591 | 0010456 | 10.456 | 1045.600 |
| 125293 | 2775591 | 0010457 | 10.457 | 1045.700 |
| 101761 | 46012142 | 0016030 | 16.030 | 1603.000 |
| 101761 | 46012142 | 0016031 | 16.031 | 1603.100 |
| 101761 | 46012142 | 0016032 | 16.032 | 1603.200 |
c2
February 23, 2018 at 11:25 am
Just to add some more clarification to my question so people can understand reply me.
It appears that "Enc Reading" is supposed to represent a number (after all the processing is done), but in the data it is a string. It also seems that the string is always either seven or nine characters; and when it is nine characters, the last two are always 00.
If so, calculating the value as TO_NUMBER( substr("Enc Reading", 1, 7) ) / 10
If the nine-character strings don't necessarily end with 00, and instead you want to add two zeros at the end of the seven-digit strings:
TO_NUMBER( rpad("Enc Reading", 9, '0') ) / 1000
I want the equivalent functions in SQL server. Obviously the functions shown above are in Oracle. Can someone help me converting TO_NUMBER( rpad("Enc Reading", 9, '0') ) / 1000 into SQL server code ?
February 23, 2018 at 11:39 am
Well the equivalent of RPAD in SQL Server would be LEFT(<string> + '000000000', 9)
February 23, 2018 at 11:58 am
CAST(STUFF([Enc Reading], 7, 0, '.') AS DECIMAL(18,3))
Drew
Updated the query, because you want to insert instead of replacing.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply