question

MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 asked ErlandSommarskog commented

Sql query issue while converting from oracle to SQL syntax

Hello Team,

Can someone please help me out converting below oracle query to SQL server. I am finding it difficult to have it converted.
Really Appreciate your help here.

1)
select substr(deliveredtime,0,10) from (select deliveredtime from messages where currentstatetype = 'Delivered' and deliveredtime <> '0' and documentclass is not null and documentclass <> 'Receipt' and $BOUND and $URL like '${PROTOCOL}:%' order by deliveredtime desc) where rownum = 1;

2)

select to_char(new_time(to_date('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+($EPOCH_TIME - 14400)/86400,'$EST','EST'),'YYYYMMDDHH24MI.SS') from dual;

Regards,
Jakati

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It could help if you explained what these queries are doing.

In the first query what are $BOUND and $URL? columns in the messages table? Or something special? ${PROTOCOL} also looks like some kind of variable?

Maybe there are people where who knows both Oracle and SQL Server, bu I don't.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MJJakati-2429,

You could have a try with format function as below:

 select FORMAT(GETDATE(),'yyyyMMddHHmm.ss')
    
 SELECT FORMAT(CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'),'yyyyMMddHHmm.ss')

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MJJakati-2429,

Welcome to Microsoft Q&A!

What do '$BOUND',{PROTOCOL} and $EPOCH_TIME refer? Are they column names or variables? If they are variables, you could declare them as @BOUND,@PROTOCOL and @EPOCH_TIME.

Beside, you could refer below and check whether it is helpful.

 select top 1 substring(deliveredtime,0,10) deliveredtime 
 from messages 
 where currentstatetype = 'Delivered' 
 and deliveredtime <> '0' and documentclass is not null 
 and documentclass <> 'Receipt' 
 and $BOUND and $URL like '${PROTOCOL}:%' 
 order by deliveredtime desc

 SELECT dateadd(second, $EPOCH_TIME, CAST( '1970-01-01' as datetime ) )
    
 SELECT CONVERT(DATETIME,dateadd(second, $EPOCH_TIME, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 answered

Hello Melissa,

Firstly I would say thanks for below query,

$BOUND and $URL? these are variables which are used in current linux script for oracle as we are moving from oracle to SQL server on linux we need to use replace SQL query on those linux scripts.

Below are some dummy variables which are stimualtes to actual one

BOUND="test = 'bound'"
URL="consumpurl"
PROTOCOL=$8

select top 1 substring(deliveredtime,0,10) deliveredtime
from messages
where currentstatetype = 'Delivered'
and deliveredtime <> '0' and documentclass is not null
and documentclass <> 'Receipt'
and $BOUND and $URL like '${PROTOCOL}:%'
order by deliveredtime desc

I did try to run you above query by replacing the variables to actual values,I am gettig this below error. Current deliveredtime data type is numeric

Msg 8116, Level 16, State 1, Line 15
Argument data type numeric is invalid for argument 1 of substring function.

Regards,
Jakati

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 answered

Below is the query where variable values are been replaced.

select to_char(new_time(to_date('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+(174532 - 14400)/86400,'EST','EST'),'YYYYMMDDHH24MI.SS') from dual;

select substr(deliveredtime,0,10) from (select deliveredtime from messages where currentstatetype = 'Delivered' and deliveredtime <> '0' and documentclass is not null and documentclass <> 'Receipt' and (direction = 'Otbd' or direction = 'nal') and url like 'http:%' order by deliveredtime desc) where rownum = 1;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Given the environment variables and all that, I think you need to rewrite not only the SQL part, but a little more than one.

Anyway, when it comes to the queries, the common recommendation for these type of questions is that you post the CREATE TABLE statement for your table(s) and INSERT statements with sample data, enough to illustrate all angles of the problem together with the expected result. To that also add a short description of the business rules explaining why you want that results. That description should be in plain English. Not PL/SQL.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MJJakati-2429,

Thanks for your update.

Please have another try with below:

 select top 1 substring(CONVERT(varchar(30), deliveredtime),0,10) deliveredtime
  from messages 
  where currentstatetype = 'Delivered' and deliveredtime <> '0'
  and documentclass is not null and documentclass <> 'Receipt' and 
  (direction = 'Otbd' or direction = 'nal') and url like 'http:%' 
  order by deliveredtime desc

  SELECT CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')

If above are still not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 answered MelissaMa-msft commented

Hello MelissaMa-msft ,

Thanks a lot the queries are working, Appreciate your help here.

Had one clarification can we save the query in variable in Linux script and run with sqlcmd? If yes what option we need to use?

SQL="select top 1 substring(CONVERT(varchar(30), deliveredtime),0,10) deliveredtime from messages where currentstatetype = 'Delivered' and deliveredtime <> '0' and documentclass is not null and documentclass <> 'Receipt' and (direction = 'Otbd' or direction = 'nal') and url like 'http:%' order by deliveredtime desc"

/opt/mssql-tools/bin/sqlcmd -S localhost -U XXXX -P XXXX -Q $SQL -o test.log

I am trying above command but its not working getting below error.

Sqlcmd: 'top': Unknown Option. Enter '-?' for help.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MJJakati-2429,

Thanks for your update.

Since you mentioned that the queries were working, please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Regarding the sqlcmd issue, you could have a try with Erland's suggestion or post a new question with all details and add tag sql-server-general , then you will get more professional help from many experts.

Thank you for understanding!

Best regards
Melissa



0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Don't you need quotes around $SQL?

/opt/mssql-tools/bin/sqlcmd -S localhost -U XXXX -P XXXX -Q "$SQL" -o test.log
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 answered ErlandSommarskog commented

Hi - ErlandSommarskog Thanks by using "$SQL" its working.

Hello MelissaMa-msft/Team,

This will be my last request sorry for bugging a lot on this.

The query which you have shared getting below output format.

SELECT CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')
2021-05-07 14:35:32.000

Can you convert into below format please.
202105071435.32

Regards,
Jakati

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That was an odd format, but try:

SELECT Format (
          CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'),
          'yyyyMMddhhmm.ss')
0 Votes 0 ·
MJJakati-2429 avatar image
0 Votes"
MJJakati-2429 answered

Thanks a lot all for your help here, Special thanks to @MelissaMa-msft for helping multiple times. Now its working fine.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.