topic Re: Read fields from SQL database with field names lower case only? in QlikView

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

Mon, 22 Oct 2012 12:32:40 GMT

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...

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

flipside — Mon, 22 Oct 2012 13:34:25 GMT

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.

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

Mon, 22 Oct 2012 14:15:39 GMT

SQL is not case sensitive.

When pulling data name the fields in lower case.

load *;

SQL Select sendalertcomplete from tblName;

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

flipside — Mon, 22 Oct 2012 14:19:32 GMT

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

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

Mon, 22 Oct 2012 14:24:27 GMT

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).

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

flipside — Mon, 22 Oct 2012 14:28:05 GMT

Very true !

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

Mon, 22 Oct 2012 14:37:11 GMT

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.

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

flipside — Mon, 22 Oct 2012 14:52:00 GMT

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

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

Mon, 22 Oct 2012 15:09:56 GMT

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;

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

rwunderlich — Mon, 22 Oct 2012 16:56:58 GMT

Add the script statement:

Force Case Lower;

prior to the Load statement.

-Rob

http://robwunderlich.com

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

Mon, 22 Oct 2012 17:08:19 GMT

That saved me probably several hours worth of work, Thank you very much sir.

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

flipside — Mon, 22 Oct 2012 17:12:18 GMT

D'Oh! I've even used that one myself previously!!

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

Mon, 22 Oct 2012 17:22:14 GMT

Do you need to turn this off after you completed this step?

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

Mon, 22 Oct 2012 17:28:42 GMT

Klangley had a good question, do I need to apply this once at the begining of the document, or before every Load statement that I want it to affect?

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

rwunderlich — Mon, 22 Oct 2012 17:42:06 GMT

It's a directive. It remains in effect until changed by a subsequent FORCE statement. So once is good enough.

See "Force" in the help or Ref Guide for options.

-Rob

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

Mon, 22 Oct 2012 20:11:08 GMT

Rob,

Does this Force Lower only the field names, or also force lowers the contents of the fields themselves?

I need JUST the field names to be lower case, and the contents to be mixed.

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

rwunderlich — Mon, 22 Oct 2012 21:49:54 GMT

Ouch. It does indeed change the values to lower case as well as the field name.

-Rob

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

jeffmartins — Mon, 22 Oct 2012 22:14:42 GMT

Hi Rob Wunderlich,

I tried to use Force Case Upper but it doesn't work for me.

Am I doing something wrong ?

Regards

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

flipside — Tue, 23 Oct 2012 09:56:33 GMT

Here's another solution I've discovered if the FORCE option doesn't suit your needs.

ODBC CONNECT TO {connection}; //or oledb

SQLTableColumns:

load *;

SQLCOLUMNS; //returns info on all poss tables and columns via the connection

//Build SQL Commands

//This effectively concatenates a list of all the fields in that table and builds a dynamic select string (limited only to the tables you need by the MATCH command)

SQLSelectCommands:

LOAD

TABLE_NAME,

'Select ' & concat(lower(COLUMN_NAME),',') & ' from ' & TABLE_NAME AS ColList

resident SQLTableColumns

where Match(TABLE_NAME,'{table1}','{table2}','{table3}' ...) > 0

Group By TABLE_NAME;

DROP TABLE SQLTableColumns; //don't need this anymore

//when you are ready to do the select * command, replace as follows for each table

LET cmdSQL = Lookup('ColList', 'TABLE_NAME', '{table}'); // where {table} is your source SQL table

Order1:

LOAD {QV LOGIC as previous}

SQL $(cmdSQL) where clause ...;

I have only tested this briefly, but seems to work. Hope it helps.

flipside

EDIT: This works with some drivers and not others. My Informix driver can return either all UPPERCASE or all LOWERCASE by using the FORCE CASE command, but default is lowercase - not mixed - and cannot FORCE mixed, even if ColList above uses column name aliasing. With Microsoft Excel driver though, mixed cases are possible and this works.

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

flipside — Tue, 23 Oct 2012 10:03:18 GMT

With reference to the RENAME fields option I gave earlier, it will generate an error if you attempt to rename a field to the same name, so test for this first to prevent the error ...

sub RenameFlds(SQLQuery, qvdName)

tmp:

SQL $(SQLQuery);

For f = 1 to NoOfFields('tmp')

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

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

If $(vFieldNew) <> $(vFieldOld) then

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

ENDIF;

Next f;

STORE tmp INTO '$(tblName)'.qvd (qvd);

DROP Table tmp;

end sub

In the code above I have placed the logic into a sub routine to create a temp qvd within the load script which can then be called multiple times as follows ...

LET SQLcmd = 'SELECT * FROM {tbl}';

CALL RenameFlds('$(SQLcmd)','Order1');