Read fields from SQL database with field names lower case only?

[#1]

I am having an issue with how qlikview reads fields from the database. I know SQL usually is not case sensitive when it comes to field names and table names.

Unfortunately we just upgraded our software and server so we now have a new database (Same tables and field names). In this new database instead of the fields being all lower case they are now like this example:

Old Field: sendalertcomplete

New Field: SendAlertComplete

Now since Qlikview reads the database as case sensitive, Qlikview cannot find any of my fields. Remembering where the letters are capitalized at will be a huge pain, especially when I have 30+ tables in some of my documents.

Does anyone know if there is a way to turn off case sensitivity when reading from SQL or transforming the field names so that they are lower case? I know doing the following is an option, but this will be cumbersome to do for every field for every table. (SendAlertComplete as sendalertcomplete)

Edit: Ok I tested and I dont have to do Table as table in the SQL Select portion of the scripts. Simply stating table pulls the information still. This is still annoying though as I am not unable to simply say Select * from; I now have to list every field from the table to get the proper case...

[flipside]

It looks like you might need to load the SQL columns into a temp table in the new upper+lower case format, then run a routine to rename (lowercase) these columns THEN do a resident load for the QV side of things.

ODBC CONNECT TO {ODBC connection};

tmp:

SQL SELECT {columns} FROM {SQLTable};

For f = 1 to NoOfFields('tmp')

    let vFieldOld = FieldName($(f),'tmp');

    let vFieldNew = Lower('$(vFieldOld)');

    RENAME FIELD $(vFieldOld) to $(vFieldNew);

Next f;

FinalTable:

LOAD {QV logic} resident tmp; 

An easier alternative may be to create views SQL side of your tables with lowercase names and read from them instead.

[#3]

SQL is not case sensitive.

When pulling data name the fields in lower case.

load *;

SQL Select sendalertcomplete from tblName;

[flipside]

I'm guessing that marcsliving is referring to the preceding load part of his script and is just trying to avoid the effort required in amending his fieldnames in his load scripts.

flipside

[#5]

Still...  If you develop the practice of naming your fields and not using select * you will always avoid this problem unless they completely change the name (which will always break things).

[flipside]

Very true !

[#7]

flipside is right, I am refering only to the SQL part. SQL does not care about case sensitivity, so it pulls as it reads. The original data has the caps, so if I do Select * it pulls all the data with the caps in the names. If I just paste every field name lower case in the select area then it pulls it in lower case.

I am doing other manipulations to the data in the load section (rtrim(text())) to every string in the table so I wanted to avoid entering every field twice. I am storing the entire table inside of a QVD to use in other documents. Naming of the fields is happening later in a resident load, or in the later documents, when the data is pulled from the QVD. The bigger problem as I said before is not that the actual names changed, it is just that with the update the fields are no longer base stored all lower case. When you are selecting entire Tables to then manipulate and restore into a QVD it was just easier and more space effective to do Select * (cannot do Load * as the text function needs to be done in the Load section to make sure qliview sees string fields containing numbers as only string fields)

This all started from an earlier issue where I found out our data needs to keep leading spaces and drop only trailing spaces. Since Verbatim only works for all leading and trailing I need to apply rtrim to every string as well.

flipside: I will look into your method, however I am not very familiar with doing routines, and since from looking at your example I would need to paste every field into this any way SQL SELECT {columns} FROM {SQLTable};

It would probably just be easier to enter every field as lowercase in the select area anyway to avoid this.

[flipside]

I would have thought the easiest solution would be to create views in the database with the lowercased field names as it would be very little different than reading direct from a single table as the view is also based on a single table.  However the routine I suggested isn't too difficult to implement and you can still use a select * SQL command.

What i assume you have at the moment is ...

     FinalTable:

     LOAD

          {QV logic};

     SQL SELECT * from {sql table}; 

What I have suggested is to change this to ...

     tmp:

     SQL SELECT * from {sql table};

     For f = 1 to NoOfFields('tmp')

          let vFieldOld = FieldName($(f),'tmp');

          let vFieldNew = Lower('$(vFieldOld)');

          RENAME FIELD $(vFieldOld) to $(vFieldNew);

     Next f;

     FinalTable:

     NOCONCATENATE LOAD {QV logic} resident tmp; 

     Drop Table tmp;

Could be a performance downside, though.

flipside

[#9]

Here is an example of what I have for one of the larger tables. As you can see it is already quite long. As I also said I am doing this for around 30 tables in the first document, 20+ in the second etc.:

Order1:

LOAD

openorder,

voidorder,

rtrim(text(company)) as 'company',

ordernum,

custnum,

rtrim(text(ponum)) as 'ponum',

orderheld,

rtrim(text(entryperson)) as 'entryperson',

rtrim(text(shiptonum)) as 'shiptonum',

requestdate,

orderdate,

rtrim(text(fob)) as 'fob',

rtrim(text(shipviacode)) as 'shipviacode',

rtrim(text(termscode)) as 'termscode',

discountpercent,

prcconnum,

shpconnum,

rtrim(text(salesreplist)) as 'salesreplist',

rtrim(text(userchar1)) as 'userchar1',

rtrim(text(userchar2)) as 'userchar2',

rtrim(text(userchar3)) as 'userchar3',

rtrim(text(userchar4)) as 'userchar4',

userdate1,

userdate2,

userdate3,

userdate4,

userdecimal1,

userdecimal2,

userinteger1,

userinteger2,

rtrim(text(ordercomment)) as 'ordercomment',

rtrim(text(shipcomment)) as 'shipcomment',

rtrim(text(invoicecomment)) as 'invoicecomment',

rtrim(text(picklistcomment)) as 'picklistcomment',

depositbal,

docdepositbal,

needbydate,

creditoverride,

rtrim(text(creditoverrideuserid)) as 'creditoverrideuserid',

creditoverridedate,

rtrim(text(creditoverridetime)) as 'creditoverridetime',

creditoverridelimit,

rtrim(text(character01)) as 'character01',

rtrim(text(character02)) as 'character02',

rtrim(text(character03)) as 'character03',

rtrim(text(character04)) as 'character04',

rtrim(text(character05)) as 'character05',

rtrim(text(character06)) as 'character06',

rtrim(text(character07)) as 'character07',

rtrim(text(character08)) as 'character08',

rtrim(text(character09)) as 'character09',

rtrim(text(character10)) as 'character10',

number01,

number02,

number03,

number04,

number05,

number06,

number07,

number08,

number09,

number10,

date01,

date02,

date03,

date04,

date05,

checkbox01,

checkbox02,

checkbox03,

checkbox04,

checkbox05,

sndalrtshp,

exchangerate,

rtrim(text(currencycode)) as 'currencycode',

lockrate,

rtrim(text(cardmembername)) as 'cardmembername',

rtrim(text(cardnumber)) as 'cardnumber',

rtrim(text(cardtype)) as 'cardtype',

expirationmonth,

expirationyear,

rtrim(text(cardid)) as 'cardid',

rtrim(text(cardmemberreference)) as 'cardmemberreference',

rtrim(text(allocprioritycode)) as 'allocprioritycode',

rtrim(text(reserveprioritycode)) as 'reserveprioritycode',

shipordercomplete,

weborder,

rtrim(text(ccapprovalnum)) as 'ccapprovalnum',

ediorder,

ediack,

linked,

icponum,

rtrim(text(extcompany)) as 'extcompany',

rtrim(text(webentryperson)) as 'webentryperson',

ackemailsent,

applyorderbaseddisc,

autoorderbaseddisc,

rtrim(text(entrymethod)) as 'entrymethod',

hdcasenum,

countersale,

createinvoice,

createpackingslip,

number11,

number12,

number13,

number14,

number15,

number16,

number17,

number18,

number19,

number20,

date06,

date07,

date08,

date09,

date10,

date11,

date12,

date13,

date14,

date15,

date16,

date17,

date18,

date19,

date20,

checkbox06,

checkbox07,

checkbox08,

checkbox09,

checkbox10,

checkbox11,

checkbox12,

checkbox13,

checkbox14,

checkbox15,

checkbox16,

checkbox17,

checkbox18,

checkbox19,

checkbox20,

rtrim(text(shortchar01)) as 'shortchar01',

rtrim(text(shortchar02)) as 'shortchar02',

rtrim(text(shortchar03)) as 'shortchar03',

rtrim(text(shortchar04)) as 'shortchar04',

rtrim(text(shortchar05)) as 'shortchar05',

rtrim(text(shortchar06)) as 'shortchar06',

rtrim(text(shortchar07)) as 'shortchar07',

rtrim(text(shortchar08)) as 'shortchar08',

rtrim(text(shortchar09)) as 'shortchar09',

rtrim(text(shortchar10)) as 'shortchar10',

lockqty,

rtrim(text(processcard)) as 'processcard',

ccamount,

ccfreight,

cctax,

cctotal,

ccdocamount,

ccdocfreight,

ccdoctax,

ccdoctotal,

rtrim(text(ccstreetaddr)) as 'ccstreetaddr',

rtrim(text(cczip)) as 'cczip',

btcustnum,

btconnum,

reprate4,

reprate5,

repsplit1,

repsplit2,

repsplit3,

repsplit4,

repsplit5,

reprate1,

reprate2,

reprate3,

outboundsalesdocctr,

outboundshipdocsctr,

demandcontractnum,

donotshipbeforedate,

resdelivery,

donotshipafterdate,

satdelivery,

satpickup,

hazmat,

doconly,

rtrim(text(refnotes)) as 'refnotes',

applychrg,

chrgamount,

cod,

codfreight,

codcheck,

codamount,

rtrim(text(groundtype)) as 'groundtype',

notifyflag,

rtrim(text(notifyemail)) as 'notifyemail',

declaredins,

declaredamt,

cancelafterdate,

demandrejected,

overridecarrier,

overrideservice,

creditcardorder,

demandheadseq,

rtrim(text(payflag)) as 'payflag',

rtrim(text(payaccount)) as 'payaccount',

rtrim(text(paybtaddress1)) as 'paybtaddress1',

rtrim(text(paybtaddress2)) as 'paybtaddress2',

rtrim(text(paybtcity)) as 'paybtcity',

rtrim(text(paybtstate)) as 'paybtstate',

rtrim(text(paybtzip)) as 'paybtzip',

rtrim(text(paybtcountry)) as 'paybtcountry',

dropship,

commercialinvoice,

shipexprtdeclartn,

certoforigin,

letterofinstr,

rtrim(text(ffid)) as 'ffid',

rtrim(text(ffaddress1)) as 'ffaddress1',

rtrim(text(ffaddress2)) as 'ffaddress2',

rtrim(text(ffcity)) as 'ffcity',

rtrim(text(ffstate)) as 'ffstate',

rtrim(text(ffzip)) as 'ffzip',

rtrim(text(ffcountry)) as 'ffcountry',

rtrim(text(ffcontact)) as 'ffcontact',

rtrim(text(ffcompname)) as 'ffcompname',

rtrim(text(ffphonenum)) as 'ffphonenum',

intrntlship,

rtrim(text(changedby)) as 'changedby',

changedate,

changetime,

autoprintready,

ediready,

individualpackids,

rtrim(text(ffaddress3)) as 'ffaddress3',

deliveryconf,

addlhdlgflag,

nonstdpkg,

servsignature,

servalert,

servhomedel,

rtrim(text(deliverytype)) as 'deliverytype',

servdeliverydate,

rtrim(text(servinstruct)) as 'servinstruct',

rtrim(text(servref1)) as 'servref1',

rtrim(text(servref2)) as 'servref2',

rtrim(text(servref3)) as 'servref3',

rtrim(text(servref4)) as 'servref4',

rtrim(text(servref5)) as 'servref5',

ffcountrynum,

rtrim(text(servphone)) as 'servphone',

servrelease,

rtrim(text(servauthnum)) as 'servauthnum',

rtrim(text(paybtaddress3)) as 'paybtaddress3',

paybtcountrynum,

rtrim(text(paybtphone)) as 'paybtphone',

upsquantumview,

rtrim(text(upsqvshipfromname)) as 'upsqvshipfromname',

rtrim(text(upsqvmemo)) as 'upsqvmemo',

readytocalc,

totalcharges,

totalmisc,

totaldiscount,

totalcomm,

totaladvbill,

totallines,

totalreleases,

totalreldates,

doctotalcharges,

doctotalmisc,

doctotaldiscount,

doctotalcomm,

totaltax,

doctotaltax,

doctotaladvbill,

totalshipped,

totalinvoiced,

totalcommlines,

srcommamt1,

srcommamt2,

srcommamt3,

srcommamt4,

srcommamt5,

srcommableamt1,

srcommableamt2,

srcommableamt3,

srcommableamt4,

srcommableamt5,

rounding,

rpt1depositbal,

docrounding,

rpt2depositbal,

rpt3depositbal,

rpt1totalcharges,

rpt2totalcharges,

rpt3totalcharges,

rpt1totaladvbill,

rpt2totaladvbill,

rpt3totaladvbill,

rpt1totalmisc,

rpt2totalmisc,

rpt3totalmisc,

rpt1totaldiscount,

rpt2totaldiscount,

rpt3totaldiscount,

rpt1totalcomm,

rpt2totalcomm,

rpt3totalcomm,

rpt1totaltax,

rpt2totaltax,

rpt1rounding,

rpt2rounding,

rpt3rounding,

rtrim(text(rategrpcode)) as 'rategrpcode',

rpt3totaltax,

rpt1ccamount,

rpt2ccamount,

rpt3ccamount,

rpt1ccfreight,

rpt2ccfreight,

rpt3ccfreight,

rpt1cctax,

rpt2cctax,

rpt3cctax,

rpt1cctotal,

rpt2cctotal,

rpt3cctotal,

orderamt,

docorderamt,

rpt1orderamt,

rpt2orderamt,

rpt3orderamt,

rtrim(text(sysrowid)) as 'sysrowid',

sysrevid,

taxpoint,

taxratedate,

rtrim(text(taxregioncode)) as 'taxregioncode',

useots,

rtrim(text(otsname)) as 'otsname',

rtrim(text(otsaddress1)) as 'otsaddress1',

rtrim(text(otsaddress2)) as 'otsaddress2',

rtrim(text(otsaddress3)) as 'otsaddress3',

rtrim(text(otscity)) as 'otscity',

rtrim(text(otsstate)) as 'otsstate',

rtrim(text(otszip)) as 'otszip',

rtrim(text(otsresaleid)) as 'otsresaleid',

rtrim(text(otscontact)) as 'otscontact',

rtrim(text(otsfaxnum)) as 'otsfaxnum',

rtrim(text(otsphonenum)) as 'otsphonenum',

otscountrynum,

totalwhtax,

doctotalwhtax,

rpt1totalwhtax,

rpt2totalwhtax,

rpt3totalwhtax,

totalsatax,

doctotalsatax,

rpt1totalsatax,

rpt2totalsatax,

rpt3totalsatax,

bitflag,

rtrim(text(otssaveas)) as 'otssaveas',

rtrim(text(otssavecustid)) as 'otssavecustid',

otscustsaved,

shiptocustnum,

rtrim(text(orderstatus)) as 'orderstatus',

holdsetbydemand,

inprice,

intotalcharges,

intotalmisc,

intotaldiscount,

docintotalcharges,

docintotalmisc,

docintotaldiscount,

rpt1intotalcharges,

rpt2intotalcharges,

rpt3intotalcharges,

rpt1intotalmisc,

rpt2intotalmisc,

rpt3intotalmisc,

rpt1intotaldiscount,

rpt2intotaldiscount,

rpt3intotaldiscount,

rtrim(text(arlocid)) as 'arlocid',

rtrim(text(ourbank)) as 'ourbank',

ersorder,

lochold,

rtrim(text(pscurrcode)) as 'pscurrcode',

rtrim(text(invcurrcode)) as 'invcurrcode',

rtrim(text(legalnumber)) as 'legalnumber',

rtrim(text(trandoctypeid)) as 'trandoctypeid',

rtrim(text(xrefcontractnum)) as 'xrefcontractnum',

xrefcontractdate,

demandprocessdate,

demandprocesstime,

rtrim(text(lastschedulenumber)) as 'lastschedulenumber',

rtrim(text(lasttctrlnum)) as 'lasttctrlnum',

rtrim(text(lastbatchnum)) as 'lastbatchnum',

progress_recid,

progress_recid_ident_,

rtrim(text(company)) & '-' & ordernum as primarykeyorder

;

SQL SELECT

  *

    FROM epicor905.dbo.orderhed

where voidorder='0'

and ((CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) >=

CAST((  STR( ( $(LastExecTimeyear) ) ) + '/' +  STR( ($(LastExecTimemonth)) ) + '/' +  STR(($(LastExecTimeday)) )  ) AS DATETIME) 

AND

(CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) <=

  CAST((  STR( ( $(ThisExecTimeyear) ) ) + '/' +  STR( ($(ThisExecTimemonth)) ) + '/' +  STR(($(ThisExecTimeday)) )  ) AS DATETIME) )  

;

Concatenate LOAD

*

FROM [\\server\UpdatedQVD\Orderhead.qvd(qvd)] (qvd)

WHERE NOT EXISTS(primarykeyorder);

Inner Join LOAD rtrim(text(company)) as company, ordernum;SQL SELECT company,ordernum FROM epicor905.dbo.orderhed where voidorder='0';

If ScriptErrorCount = 0 then

STORE Order1 into [\\server\UpdatedQVD\Orderhead.qvd(qvd)];

End If

Order:

LOAD

company & '-' & custnum as companycust_X,

company & '-' & custnum as companycustord,

company & '-' & ordernum as companyordernum,

company & '-' & ordernum as companyord,

company as Company1,

ponum as 'Customer PO',

Monthname(orderdate) as 'Month and Year Ordered',

Month(orderdate) as 'Month Ordered',

Year(orderdate) as 'Year Ordered',

Date(orderdate,'MM/DD/YYYY') as 'Order Date',

if(YEAR(orderdate)=Year(today()),if(company='Other',orderamt)) as 'Current Year',

if(YEAR(orderdate)=(Year(today())-1),if(company='Other',orderamt/.8)) as Goal,

'Q' & Ceil(Month(orderdate)/3) as 'Quarter Ordered',

openorder as 'Open Order',

if(WildMatch(salesreplist,'*HOUSE*','*House*'),'House','Non-House') as 'House',

orderamt as 'Order Amount',

If(orderamt<25000,'1. Under 25K',If((orderamt>=25000 and orderamt<50000),'2. 25K–50K',If((orderamt>=50000 and orderamt<100000),'3. 50K to 100K',If((orderamt>=100000 and orderamt<200000),'4. 100K to 200K',If((orderamt>=200000 and orderamt<300000),'5. 200K to 300K',If((orderamt>=300000 and orderamt<400000),'6. 300K to 400K',If((orderamt>=400000 and orderamt<500000),'7. 400K to 500K',if(orderamt>=500000,'8. Over 500K'))))))))as 'Order Level',

ordernum as 'Order Number',

company & '-' & custnum  & '-' & shiptonum & '-' & shpconnum as contact1,

requestdate as 'Requested by Date',

Capitalize( SubField(salesreplist,'~',1)) as 'Salesperson SO'

Resident Order1;

DROP Table Order1;

[rwunderlich · Accepted solution]

Add the script statement:

Force Case Lower;

prior to the Load statement.

-Rob

http://robwunderlich.com