Selecting data with SQL code acting differently in CR

[Former Member]

Hi,

I have a SQL Query containing the following codes:

cast(AROBP_A.IDINVC as int) as AROBP_A_IDINVC,

...

where AROBP_N.FISCYR = '2014' and AROBP_N.FISCPER = '07' and AROBP_N.IDINVC not like '[A-Z]%' and AROBP_N.IDINVC not like '%[A-Z]'

IDIINV is an Invoice "Number"; the "not like" clause is used to filter out non numeric data in order to convert from a string to a usable numeric, so I can link to another database where Invoice Numbers are of a numeric type.

The full query above selects 2087 rows.

When I copy this query into a CR  command the cast statement fails, the error tells me it cannot cast alpha to type int.

When I remove the cast clause, it works fine, but picks the rows including alpha entries, a total of 2114 rows.

Is someone familiar with how I need to adjust the syntax for the "not like"s?  It doesn't error, it just seems to ignore it.

Thanks!

MAtt

[DellSC]

What type of database are you connecting to and what type of connection are you using (ODBC, OLEDb, native)?

-Dell

[Former Member]

Hi Dell,

Oops, Thank you.

CR 2011 using SQL Native Client 10.0 w Windows Authentication.

[DellSC]

CR 2011 doesn't connect natively to SQL Server - it uses either ODBC or OLEDb.  Which are you using?

-Dell

[Former Member]

It's ODBC

[DellSC]

That's what I suspected.  ODBC doesn't always work the same way as the native connecting in SQL Server Management Studio will.  Try changing the connection to OLEDb (which is closer to a native connection) and see if that solves the problem.

-Dell

[DellSC]

One other thing...

If you're linking multiple commands or a command and table(s), you're going to slow down your report because Crystal will pull the data into memory and do the join there.  Best practice is to use a single command that returns ALL of the data required by the report.

Also, if you're using parameters for your report, you'll need to create and use them in the command.  Commands can't "see" the parameters from the report but the report will see the params from the command after the command has been saved.  If you use the Select Expert instead of the Where clause in the command, again Crystal will bring the data into memory and filter it there instead of pushing the filter to the database.

-Dell

[Former Member]

Hi Dell,

It's not multiple queries, just a single command, the only difference is it seems to just ignore the "not like" clauses to filter out non numerics:

select distinct

    cast(AROBP_A.IDINVC as int) as AROBP_A_IDINVC,

    cast(AROBP_A.IDCUST as int) as AROBP_A_IDCUST,

    ARCUS.NAMECUST     as ARCUS_NAMECUST,

    ARIBH.DATEINVC     as ARIBH_DATEINVC,

    ARIBH.TEXTTRX      as ARIBH_TEXTTRX,

    ARIBH_TEXTTRXTXT = case ARIBH.TEXTTRX

        when 1 then 'Invoice' when 2 then 'Debit Note' when 3 then 'Credit Note' else '' end,

    ARIBH.AMTINVCTOT   as ARIBH_AMTINVCTOT_ABS,

    ARIBH_AMTINVCTOT = case ARIBH.TEXTTRX

        when 3 then - ARIBH.AMTINVCTOT else ARIBH.AMTINVCTOT end,

    AROBP_A.AMTPAYMTC  as AROBP_A_AMTPAYMTC,

    INV_DIFF = case ARIBH.TEXTTRX

        when 3 then AROBP_A.AMTPAYMTC - ARIBH.AMTINVCTOT else AROBP_A.AMTPAYMTC + ARIBH.AMTINVCTOT end,

    AROBP_A.DATEBTCH   as AROBP_A_DATEBTCH,

    AROBP_A.TRXTYPE    as AROBP_A_TRXTYPE,

    AROBP_A_TRXTYPETXT = case AROBP_A.TRXTYPE

        when 43 then 'Credit Note Applied To' when 44 then 'Applied Credit Note'

        when 51 then 'Receipt Posted' when 52 then 'Receipt Applied' when 53 then 'Receipt Reversed'

        when 61 then 'Discount Posted' when 63 then 'Discount Reversed'

        when 81 then 'Adjustment Posted' when 83 then 'Adjustment Reversed'

        else 'Other' end,

    AROBP_A.TRANSTYPE  as AROBP_A_TRANSTYPE,

    AROBP_A_TRANSTYPETXT = case AROBP_A.TRANSTYPE

        when 8 then 'Credit Note Applied To' when 9 then 'Applied Credit Note'

        when 11 then 'Receipt' when 12 then 'Discount' when 14 then 'Adjustment'

        else 'Other' end,

    AROBP_A.FISCPER    as AROBP_A_FISCPER,

    AROBP_A.FISCYR     as AROBP_A_FISCYR,

    ARSAP.NAMEEMPL     as ARSAP_NAMEEMPL,

    ARSAP2.NAMEEMPL    as ARSAP2_NAMEEMPL

from GGIAPP01.GGI.dbo.ARIBH ARIBH

right join GGIAPP01.GGI.dbo.AROBP AROBP_N on ARIBH.IDINVC = AROBP_N.IDINVC

left  join GGIAPP01.GGI.dbo.AROBP AROBP_A on ARIBH.IDINVC = AROBP_A.IDINVC

left  join GGIAPP01.GGI.dbo.ARCUS ARCUS   on ARIBH.IDCUST = ARCUS.IDCUST

left  join GGIAPP01.GGI.dbo.ARSAP ARSAP   on ARCUS.CODESLSP1 = ARSAP.CODESLSP

left  join GGIAPP01.GGI.dbo.ARSAP ARSAP2  on ARIBH.CODESLSP1 = ARSAP2.CODESLSP

where AROBP_N.FISCYR = '2014' and AROBP_N.FISCPER = '07'

  and AROBP_N.IDINVC not like '[A-Z]%' and AROBP_N.IDINVC not like '%[A-Z]'

order by ARSAP_NAMEEMPL, AROBP_A_IDCUST, AROBP_A_IDINVC;

[DellSC]

Ok, like I said above, try using an OLEDb connection instead of ODBC.

-Dell

[Former Member]

Hi,

Data was being accessed through a linked server.  Just fine in SMS, but CR didn't like it.

When I changed the ODBC connection to connect directly to the other SQL instance, it's giving me what I need.

Thank you for your help!

Matt