Results 1 to 10 of 10

Thread: Help please

  1. #1
    Join Date
    Nov 2002
    Posts
    9

    Question Unanswered: Help please

    Can anyone check my syntax please, i am trying to converts Oracle script to SQL server:-
    oracle
    select TO_DATE('12/15/1970','MM/DD/YYYY')+(DS_PENDING_JOB.END_DATETIME/86400)from DS_PENDING_JOB


    while converting to SQL Server:
    select convert(date(10),'12/15/1970','mm/dd/yy')+(DS_PENDING_JOB.END_DATETIME/86400)from DS_PENDING_JOB

    getting this error:

    Server: Msg 8116, Level 16, State 1, Line 1
    Argument data type varchar is invalid for argument 3 of convert function.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Were you trying to do the following:

    select convert(datetime,'12/15/1970',101)

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: Help please


    Q1 Can anyone check my syntax please, i am trying to converts Oracle select convert(date(10),'12/15/1970','mm/dd/yy')+(DS_PENDING_JOB.END_DATETIME/86400)from DS_PENDING_JOB
    A1 You may wish to consider first converting your data to datetimes, then performing datetime calculations. Some issues with convert (DataType, 'Argument', Style) are as follows:

    i There is no DataType designated: "date(10)" note: length may only be specified for nchar, nvarchar, char, varchar, binary, or varbinary convert statements.

    ii There is no datetime / smalldatetime Style designation: 'mm/dd/yy'
    They are designated as integers: 0,1,2,3,4, ...21 and 100, 101, ...131
    Select Convert(SmallDateTime,'12/15/1970', 101)
    Select Convert(DateTime,'12/15/1970',101)

    Examples:

    Select Convert(SmallDateTime,'12/15/1970', 101) As 'SmallDateTime style 101 convert example'
    Select Convert(DateTime,'12/15/1970',101)As 'DateTime style 101 convert example'

    Select Cast('12/15/1970' As SmallDateTime) As 'SmallDateTime cast example'
    Select Cast('12/15/1970' As DateTime)As 'DateTime cast example'


    Note, DataTypes are:
    bigint
    Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
    int
    Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
    smallint
    Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
    tinyint
    Integer data from 0 through 255.
    bit
    Integer data with either a 1 or 0 value.
    decimal
    Fixed precision and scale numeric data from -10^38 +1 through 10^38 1.
    numeric
    Functionally equivalent to decimal.
    money and smallmoney
    money
    Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
    smallmoney
    Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
    float
    Floating precision number data from -1.79E + 308 through 1.79E + 308.
    real
    Floating precision number data from -3.40E + 38 through 3.40E + 38.
    datetime
    Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
    smalldatetime
    Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
    char
    Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
    varchar
    Variable-length non-Unicode data with a maximum of 8,000 characters.
    text
    Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
    nchar
    Fixed-length Unicode data with a maximum length of 4,000 characters.
    nvarchar
    Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
    ntext
    Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
    binary
    Fixed-length binary data with a maximum length of 8,000 bytes.
    varbinary
    Variable-length binary data with a maximum length of 8,000 bytes.
    image
    Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
    sql_variant
    A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
    timestamp
    A database-wide unique number that gets updated every time a row gets updated.
    uniqueidentifier
    A globally unique identifier (GUID)
    Last edited by DBA; 12-02-02 at 23:43.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    For future reference, you can use the books online (BOL) for sql server.

    Check out the "cast and convert" / "Data Types". Cast and convert will detail the style parameter.

  5. #5
    Join Date
    Nov 2002
    Posts
    9
    Originally posted by rnealejr
    For future reference, you can use the books online (BOL) for sql server.

    Check out the "cast and convert" / "Data Types". Cast and convert will detail the style parameter.
    Hi
    Thanks for your response now I got it. As I never worked with SQL server before. Most of our databases are in Oracle, recently we moved some of them to sequel server. I am trying to convert some repositor scripts from Oracle to SQL server. Here I would like to request you to clarify some of my doubts.
    For ex : two table TableA and TableB with a columnA and ColumnB respectively. I am trying to join these two tables with a left out join like

    Select Table A.columnA, TableB.columnB
    From TableA left outer join TableB ON
    ColumnA=ColummB.

    But my problem is ColumnB = 1 (which is a constant)
    If I am giving ColumnB = 1 getting the following error.

    Error:Both terms of an outer join must contain columns

    How to over come this please explain me, keep in mind I am new to Sequel server.
    regards

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post the original oracle sql query and the query you tried to convert to sql server ?

  7. #7
    Join Date
    Nov 2002
    Posts
    9
    Originally posted by rnealejr
    Can you post the original oracle sql query and the query you tried to convert to sql server ?
    Oracle SQL:

    SELECT
    CASE WHEN GRP6.M_ACTOR_C_NAME IS NOT NULL THEN GRP6.M_ACTOR_C_NAME || '\' END ||
    CASE WHEN GRP5.M_ACTOR_C_NAME IS NOT NULL THEN GRP5.M_ACTOR_C_NAME || '\' END ||
    CASE WHEN GRP4.M_ACTOR_C_NAME IS NOT NULL THEN GRP4.M_ACTOR_C_NAME || '\' END ||
    CASE WHEN GRP3.M_ACTOR_C_NAME IS NOT NULL THEN GRP3.M_ACTOR_C_NAME || '\' END ||
    CASE WHEN GRP2.M_ACTOR_C_NAME IS NOT NULL THEN GRP2.M_ACTOR_C_NAME || '\' END ||
    CASE WHEN GRP1.M_ACTOR_C_NAME IS NOT NULL THEN GRP1.M_ACTOR_C_NAME || '\' END || ' ' Path,
    CASE WHEN GRP1.M_ACTOR_C_NAME IS NULL THEN ' ' ELSE GRP1.M_ACTOR_C_NAME END Group_Name,
    OBJ_M_ACTOR.M_ACTOR_C_NAME Actor_name,
    CASE WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 1 THEN 'Group'
    WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 16 THEN 'User'
    WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 128 THEN 'CMDSetting'
    ELSE 'Type ' || to_char(OBJ_M_ACTOR.M_ACTOR_N_TYPE)
    END Actor_Type,
    rtrim(
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 1)) = 1 THEN 'enabled, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 1)) = 0 THEN 'disabled, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 128)) = 128 THEN 'locked, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 2)) = 2 THEN 'locked-2, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 4)) = 4 THEN 'offline prevent, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 8)) = 8 THEN 'cannot change password, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 32)) = 32 THEN 'realtime update, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 64)) = 64 THEN 'cannot delete documents, ' END ||
    CASE WHEN to_number(bitand(OBJ_M_ACTOR.M_ACTOR_N_STATUS, 256)) = 256 THEN 'disable login of pre-4.1.5 release, ' END, ', ') Actor_Status
    FROM
    OBJ_M_ACTOR GRP1,
    OBJ_M_ACTOR GRP2,
    OBJ_M_ACTOR GRP3,
    OBJ_M_ACTOR GRP4,
    OBJ_M_ACTOR GRP5,
    OBJ_M_ACTOR GRP6,
    OBJ_M_ACTOR,
    OBJ_M_ACTORLINK ACTL_2,
    OBJ_M_ACTORLINK ACTL_1,
    OBJ_M_ACTORLINK ACTL_3,
    OBJ_M_ACTORLINK ACTL_4,
    OBJ_M_ACTORLINK ACTL_5,
    OBJ_M_ACTORLINK ACTL_6,
    OBJ_M_ACTORLINK
    WHERE
    OBJ_M_ACTOR.M_ACTOR_N_LAT <> 1
    AND ACTL_1.M_ACTL_N_ACTORID=GRP1.M_ACTOR_N_ID(+)
    AND ACTL_1.M_ACTL_N_ACTORTYPE(+)=1
    AND ACTL_1.M_ACTL_N_FATLINKID=ACTL_2.M_ACTL_N_ID(+)
    AND GRP2.M_ACTOR_N_ID(+)=ACTL_2.M_ACTL_N_ACTORID
    AND ACTL_2.M_ACTL_N_ACTORTYPE(+)=1
    AND ACTL_2.M_ACTL_N_FATLINKID=ACTL_3.M_ACTL_N_ID(+)
    AND GRP3.M_ACTOR_N_ID(+)=ACTL_3.M_ACTL_N_ACTORID
    AND ACTL_3.M_ACTL_N_ACTORTYPE(+)=1
    AND ACTL_3.M_ACTL_N_FATLINKID=ACTL_4.M_ACTL_N_ID(+)
    AND GRP4.M_ACTOR_N_ID(+)=ACTL_4.M_ACTL_N_ACTORID
    AND ACTL_4.M_ACTL_N_ACTORTYPE(+)=1
    AND ACTL_4.M_ACTL_N_FATLINKID=ACTL_5.M_ACTL_N_ID(+)
    AND GRP5.M_ACTOR_N_ID(+)=ACTL_5.M_ACTL_N_ACTORID
    AND ACTL_5.M_ACTL_N_ACTORTYPE(+)=1
    AND GRP6.M_ACTOR_N_ID(+)=ACTL_6.M_ACTL_N_ACTORID
    AND ACTL_6.M_ACTL_N_ACTORTYPE(+)=1
    AND ACTL_5.M_ACTL_N_FATLINKID=ACTL_6.M_ACTL_N_ID(+)
    AND GRP1.M_ACTOR_N_TYPE(+)=1
    AND GRP2.M_ACTOR_N_TYPE(+)=1
    AND GRP3.M_ACTOR_N_TYPE(+)=1
    AND GRP4.M_ACTOR_N_TYPE(+)=1
    AND GRP5.M_ACTOR_N_TYPE(+)=1
    AND GRP6.M_ACTOR_N_TYPE(+)=1
    AND ACTL_1.M_ACTL_N_LAT(+) <> 1
    AND ACTL_2.M_ACTL_N_LAT(+) <> 1
    AND ACTL_3.M_ACTL_N_LAT(+) <> 1
    AND ACTL_4.M_ACTL_N_LAT(+) <> 1
    AND ACTL_5.M_ACTL_N_LAT(+) <> 1
    AND ACTL_6.M_ACTL_N_LAT(+) <> 1
    AND GRP1.M_ACTOR_N_LAT(+) <> 1
    AND GRP2.M_ACTOR_N_LAT(+) <> 1
    AND GRP3.M_ACTOR_N_LAT(+) <> 1
    AND GRP4.M_ACTOR_N_LAT(+) <> 1
    AND GRP5.M_ACTOR_N_LAT(+) <> 1
    AND GRP6.M_ACTOR_N_LAT(+) <> 1
    AND ACTL_1.M_ACTL_N_ID(+)=OBJ_M_ACTORLINK.M_ACTL_N_FAT LINKID
    AND OBJ_M_ACTORLINK.M_ACTL_N_ACTORID(+)=OBJ_M_ACTOR.M_ ACTOR_N_ID
    AND OBJ_M_ACTORLINK.M_ACTL_N_LAT(+)<>1
    order by 1,2




    The sql server query i tryed, but it is not completed.


    SELECT
    CASE WHEN GRP6.M_ACTOR_C_NAME IS NOT NULL THEN GRP6.M_ACTOR_C_NAME + '\' END +
    CASE WHEN GRP5.M_ACTOR_C_NAME IS NOT NULL THEN GRP5.M_ACTOR_C_NAME + '\' END +
    CASE WHEN GRP4.M_ACTOR_C_NAME IS NOT NULL THEN GRP4.M_ACTOR_C_NAME + '\' END +
    CASE WHEN GRP3.M_ACTOR_C_NAME IS NOT NULL THEN GRP3.M_ACTOR_C_NAME + '\' END +
    CASE WHEN GRP2.M_ACTOR_C_NAME IS NOT NULL THEN GRP2.M_ACTOR_C_NAME + '\' END +
    CASE WHEN GRP1.M_ACTOR_C_NAME IS NOT NULL THEN GRP1.M_ACTOR_C_NAME + '\' END + ' ' Path,
    CASE WHEN GRP1.M_ACTOR_C_NAME IS NULL THEN ' ' ELSE GRP1.M_ACTOR_C_NAME END Group_Name,
    OBJ_M_ACTOR.M_ACTOR_C_NAME Actor_name,
    CASE WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 1 THEN 'Group'
    WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 16 THEN 'User'
    WHEN OBJ_M_ACTOR.M_ACTOR_N_TYPE = 128 THEN 'CMDSetting'
    ELSE 'Type' + cast(OBJ_M_ACTOR.M_ACTOR_N_TYPE as Decimal)
    END Actor_Type,
    rtrim(
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 1 THEN 'enabled, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 0 THEN 'disabled, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 128 THEN 'locked, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 2 THEN 'locked-2, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 4 THEN 'offline prevent, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 8 THEN 'cannot change password, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 32 THEN 'realtime update, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 64 THEN 'cannot delete documents, ' END +
    CASE WHEN cast(OBJ_M_ACTOR.M_ACTOR_N_STATUS as int) = 256 THEN 'disable login of pre-4.1.5 release,' END) Actor_Status
    FROM
    OBJ_M_ACTOR GRP1 LEFT outer join OBJ_M_ACTORLINK ACTL_1 ON GRP1.M_ACTOR_N_ID=ACTL_1.M_ACTL_N_ACTORID,
    OBJ_M_ACTORLINK ACTL_2 INNER JOIN OBJ_M_ACTORLINK ACTL_1a ON ACTL_2.M_ACTL_N_ID=ACTL_1a.M_ACTL_N_FATLINKID,
    OBJ_M_ACTOR GRP2 left outer join OBJ_M_ACTORLINK ACTL_2a on GRP2.M_ACTOR_N_ID=ACTL_2a.M_ACTL_N_ACTORID,
    OBJ_M_ACTORLINK ACTL_3a INNER JOIN OBJ_M_ACTORLINK ACTL_2a1 ON ACTL_3a.M_ACTL_N_ID=ACTL_2a1.M_ACTL_N_FATLINKID,
    OBJ_M_ACTOR GRP3 left outer join OBJ_M_ACTORLINK ACTL_3 on GRP3.M_ACTOR_N_ID=ACTL_3.M_ACTL_N_ACTORID,
    OBJ_M_ACTORLINK ACTL_3a1 INNER JOIN OBJ_M_ACTORLINK ACTL_4 ON ACTL_3a1.M_ACTL_N_ID=ACTL_4.M_ACTL_N_FATLINKID,
    OBJ_M_ACTOR GRP4 left outer join OBJ_M_ACTORLINK ACTL_4a on GRP4.M_ACTOR_N_ID=ACTL_4a.M_ACTL_N_ACTORID,
    OBJ_M_ACTORLINK ACTL_4a1 INNER JOIN OBJ_M_ACTORLINK ACTL_5 ON ACTL_4a1.M_ACTL_N_ID=ACTL_5.M_ACTL_N_FATLINKID,
    OBJ_M_ACTOR GRP5 left outer join OBJ_M_ACTORLINK ACTL_5a on GRP5.M_ACTOR_N_ID=ACTL_5a.M_ACTL_N_ACTORID,
    OBJ_M_ACTOR GRP6 left outer join OBJ_M_ACTORLINK ACTL_6a on GRP6.M_ACTOR_N_ID=ACTL_6a.M_ACTL_N_ACTORID,
    OBJ_M_ACTORLINK ACTL_5a1 INNER JOIN OBJ_M_ACTORLINK ACTL_6 ON ACTL_5a1.M_ACTL_N_ID=ACTL_6.M_ACTL_N_FATLINKID,
    OBJ_M_ACTORLINK ACTL_7 left outer join OBJ_M_ACTORLINK ON ACTL_7.M_ACTL_N_ID=OBJ_M_ACTORLINK.M_ACTL_N_FATLIN KID,
    OBJ_M_ACTORLINK OA LEFT OUTER JOIN OBJ_M_ACTOR ON OA.M_ACTL_N_ACTORID=OBJ_M_ACTOR.M_ACTOR_N_ID
    WHERE
    OBJ_M_ACTOR.M_ACTOR_N_LAT<>1
    AND OBJ_M_ACTORLINK.M_ACTL_N_LAT<>1
    ORDER BY 1,2


    thanks.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Sorry I asked I will try to get back to you in a few hours.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Which ones are constants ? When you say constants where are they being defined and what are their values ? Are they not part of a table ? Give me a simple example of the data in the table and the constants of what it would look like before and after the query. How does oracle treat a left outer join that has a constant - what does it return ?

  10. #10
    Join Date
    Nov 2002
    Posts
    9
    Originally posted by rnealejr
    Which ones are constants ? When you say constants where are they being defined and what are their values ? Are they not part of a table ? Give me a simple example of the data in the table and the constants of what it would look like before and after the query. How does oracle treat a left outer join that has a constant - what does it return ?

    Basically there are only two tables OBJ_M_ACTOR and OBJ_M_ACTORLINK having a PK link between M_ACTOR_N_ID column with 100 rows from OBJ_M_ACTOR table and M_ACTL_N_ACTORID column with 200 rows from OBJ_M_ACTORLINK. As we know in Oracle we can through an outer join on a column with less number of rows.
    My tables values are like this:

    OBJ_MACTOR table
    M_ACTOR_N_STATUS || M_ACTOR_N_ID ||M_ACTOR_N_LAT
    1 1 4
    1 2 4
    1 3 4
    1 4 4
    OBJ_M_ACTORLINK Table
    M_ACTL_N_ACTORID || M_ACTL_N_ID || M_ACTL_N_FATLINKID
    1 1 1
    2 2 2
    3 3 2
    4 3 3


    select a.M_ACTOR_N_STATUS,a.M_ACTOR_N_ID,b.M_ACTL_N_ACTOR ID,b.M_ACTL_N_ACTORTYPE
    from OBJ_M_ACTOR a,
    OBJ_M_ACTORLINK b
    where
    b.M_ACTL_N_ACTORID=a.M_ACTOR_N_ID(+)
    and b.M_ACTL_N_ACTORTYPE(+)=1



    Probably I may confused you by mentioning constant, in my talk constant means the value refer in where condition that is: b.M_ACTL_N_ACTORTYPE(+)=1 in my case. You can get better idea by look at the sample script above. But in the original script they used different alias for the same table you may notice that.
    OBJ_M_ACTOR GRP1,
    OBJ_M_ACTOR GRP2,
    OBJ_M_ACTOR GRP3,
    OBJ_M_ACTOR GRP4,
    OBJ_M_ACTOR GRP5,
    OBJ_M_ACTOR GRP6,
    OBJ_M_ACTOR,
    OBJ_M_ACTORLINK ACTL_2,
    OBJ_M_ACTORLINK ACTL_1,
    OBJ_M_ACTORLINK ACTL_3,
    OBJ_M_ACTORLINK ACTL_4,
    OBJ_M_ACTORLINK ACTL_5,
    OBJ_M_ACTORLINK ACTL_6,
    OBJ_M_ACTORLINK
    And also there is one join (like self join) on the same table of different aliases ex:
    ACTL_1.M_ACTL_N_FATLINKID=ACTL_2.M_ACTL_N_ID(+)
    Hope I explained clearly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •