# SQL query please

**URL:** <https://forum.sambapos.com/t/sql-query-please/17908>\
**Category:** V5 Question\
**Created:** [August 1, 2018, 9:53am UTC](https://forum.sambapos.com/t/sql-query-please/17908 "2018-08-01T09:53:43Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 9:53am UTC](https://forum.sambapos.com/t/sql-query-please/17908/1 "2018-08-01T09:53:43Z")

</div>

Hi  
(written with cell phone)  
Slept 2 hours, tired, could you please tell me the SQL query (for script) to get the value of:

- CPT (Ticket Tag)
- CodeId (Custom field of ENTITY TYPE “SUNAT” ; NAME = Sunat)  
Where TICKET ID = ‘+settingname+’

It would help me 😊 @QMcKay ?

Marc

---

<div class="post-metadata">

**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\
**Post date:** [August 1, 2018, 10:26am UTC](https://forum.sambapos.com/t/sql-query-please/17908/2 "2018-08-01T10:26:01Z")

</div>

Pretty sure ticket tags are stored in JSON within the SQL which makes extracting them harder with direct SQL. Might be easier with GraphQL if your up to date - unfortunatly I am yet to delve too deap into that to be able to speil off any sugestions on that front.  
How are you initiating the script? From what rule event that you need to store the ticket id in a setting rather than feed into the script as a variable?

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 10:39am UTC](https://forum.sambapos.com/t/sql-query-please/17908/3 "2018-08-01T10:39:18Z")

</div>

pleaaase the sql 😥  
going live in 2 hours

---

<div class="post-metadata">

**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\
**Post date:** [August 1, 2018, 10:48am UTC](https://forum.sambapos.com/t/sql-query-please/17908/4 "2018-08-01T10:48:31Z")

</div>

That’s no one’s fault but yours.  
I don’t have the answer you need so was trying to help with possible alternative, don’t want it then your choice, will leave you to it.  
Q is states based I beleive so it’s between 2-6am over there so expect he is sleeping.

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 10:49am UTC](https://forum.sambapos.com/t/sql-query-please/17908/5 "2018-08-01T10:49:52Z")

</div>

Thanks …  
I’ll wait that states wakes up.

---

<div class="post-metadata">

**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\
**Post date:** [August 1, 2018, 12:16pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/6 "2018-08-01T12:16:50Z")

</div>

To use SQL to get that you need JSON support turned on in SQL express 2016 or 2017. Do you have that part done?

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 12:19pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/7 "2018-08-01T12:19:23Z")

</div>

You mean that I cannot get that information with some standard SQL (without jason)?

(yes i have 2016, no JSON support is not turn on yet)

---

<div class="post-metadata">

**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\
**Post date:** [August 1, 2018, 12:20pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/8 "2018-08-01T12:20:42Z")

</div>

> [@JTRTech](#):
>
> Pretty sure ticket tags are stored in JSON within the SQL which makes extracting them harder

And

> [@Jesse](#):
>
> To use SQL to get that you need JSON support turned on in SQL express 2016 or 2017

Pretty sure we answered that.

---

<div class="post-metadata">

**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\
**Post date:** [August 1, 2018, 12:25pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/9 "2018-08-01T12:25:20Z")

</div>

There are other ways to get ticket tags using custom report tags. Maybe explain with screenshots what your doing and we can provide a few solutions.

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 12:25pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/10 "2018-08-01T12:25:28Z")

</div>

> [@Jesse](#):
>
> Pretty sure we answered that.

Yes. To take advantage of full power of JSON install and setup 2016 .  
Don’t know JSON, no time to learn how to take full advantage of JSON inside SQL query, just know basic SQL.  
As SambaPOS V5 works with any version of SQL, it must be possible to extract that info using “normal” SQL, am I wrong?

Thx

---

<div class="post-metadata">

**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\
**Post date:** [August 1, 2018, 12:27pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/11 "2018-08-01T12:27:04Z")

</div>

Your wrong I’m sorry you can wish it all you want but won’t change anything.

BTW that is normal SQL. The ability to do that came out with SQL Express 2016.

---

<div class="post-metadata">

**Author:** ![Jesse](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jesse/32/61493_2.png) [@Jesse](https://forum.sambapos.com/u/Jesse)\
**Post date:** [August 1, 2018, 12:31pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/12 "2018-08-01T12:31:46Z")

</div>

Please explain what your doing we likely can help you accomplish it without SQL

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 12:46pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/13 "2018-08-01T12:46:30Z")

</div>

I am trying without SQL.

B=Boleta : a type of bill; it has its own serial number  
F=Factura : another type of bill; it has its own serial number

I am using a GEN printer that prints ToFile.  
That file has a dynamic filename, like

```
20603258170-03-B001-56.xxx
or
20603258170-01-F001-24.xxx
^^^ ^^ ^^^^^^^
Company # Serial number of bill
            Type of bill

```

Company # is global, no problem

type of bill is ticket related (ticket tag)  
Serial number is global **but** it depends on the type of bill  
And these 2 ticket related variables are accessible while in ticket screen, reports, printer template, but are not accessible in the GEN printer configuration setting (here after)

 ![imagen](https://forum.sambapos.com/uploads/default/original/3X/4/3/4308551111946b3c81f9040e8d1ec8c06d62fdcb.png)

Note that I realize that the script/query based on “TICKET ID” will not work because this variable “TICKET ID” is also not accessible in the “memory space” (don’t know how to name it) of that printer configuration setting. Only global variables are available.

I created a “tempory” global variable `tmpCPT` as a work around, but I don’t like it because I had lots of troubles with the updates/reset of these global variables, not so global.

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 1:10pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/14 "2018-08-01T13:10:37Z")

</div>

> [@plume](#):
>
> I created a “tempory” global variable `tmpCPT` as a work around, but I don’t like it because I had lots of troubles with the updates/reset of these global variables, not so global.

And here it comes again …

 ![imagen](https://forum.sambapos.com/uploads/default/original/3X/9/c/9c612dff63982fd2b9f1d27cfd7228efbc93d98f.png)

 ![imagen](https://forum.sambapos.com/uploads/default/original/3X/0/f/0fc8b42a4b2e7868dfdcd3d40ed0521607fc3673.png)

That SFS button runs the rule that contain these actions:

- Set tmpCPT to the value of Ticket Tag CPT with sql UPDATE [ProgramSettingValues] …
- Buid the filename with the makeSFSFilename function that takes as parameter {GLOBAL SETTING…}  
which does not work, I’ll try with SQL now to see if it helps.

 ![imagen](https://forum.sambapos.com/uploads/default/original/3X/9/5/95d69aeefd2c2ca88c88eba43a16baad69e2ddc7.png)

---

<div class="post-metadata">

**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\
**Post date:** [August 1, 2018, 1:35pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/15 "2018-08-01T13:35:40Z")

</div>

Not sure if I’m misunderstanding but why have a script to set the program setting then using a nother script with the setting as a variable? Why not just feed to ket tag into the script?

---

<div class="post-metadata">

**Author:** ![plume](https://forum.sambapos.com/user_avatar/forum.sambapos.com/plume/32/1190_2.png) [@plume](https://forum.sambapos.com/u/plume)\
**Post date:** [August 1, 2018, 1:55pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/16 "2018-08-01T13:55:50Z")

</div>

See, before the tmpCPT global setting, I was using directly the TICKET TAG CPT.

the rule is executed in the ticket screen and itcalls multiple times the same function db.xxx:

- first action JUST to display a message (TICKET TAG:CPT) =\> result OK (0 or 1 is display)
- second action is the exec print job whose “filename configaration setting” contains also the same function, showing the same message, exept that it showed =\> it display {‘TICKET TAG:CPT’} instead of 0 or 1

So I deduce that “filename configaration setting” only recognises the global setting (and not the TICKET TAG), that’s why I am using (with trouble) tmpCPT in programsetting to store temporallily the ticket tag CPT.

dono if I am clear

---

<div class="post-metadata">

**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\
**Post date:** [August 1, 2018, 3:04pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/17 "2018-08-01T15:04:31Z")

</div>

Ok, but pretty sure based on that you don’t want to use global setting but local else you could get cross over between multiple tills.  
Not that it should make a difference but not sure why you would use a script to update program settings when there is action for this but either way.  
To your previous question you can in theory strip tag values out of the JSON without the JSON functionality but it is very hacky. My PMS intergration I did this for ticket states using splits to slice the data out of the JSON in it’s string/unparsed format. But it’s a dirty way to do it and only did it that way as was early days in my scripting knolage and SQL hadn’t added the JSON functions it has now.

---

<div class="post-metadata">

**Author:** ![markjw](https://forum.sambapos.com/user_avatar/forum.sambapos.com/markjw/32/9942_2.png) [@markjw](https://forum.sambapos.com/u/markjw)\
**Post date:** [August 1, 2018, 3:10pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/18 "2018-08-01T15:10:19Z")

</div>

Sorry just jumping in here without fully reading the topic, but even if you don’t use SQL 2016 or above with JSON support, another way you could access JSON data is in a script, you have access to `JSON.parse()` and you could load the relevant field in using `sql.Query()` or another SQL function. Read more here…

> [@JScript Helpers and Functions](https://forum.sambapos.com/t/jscript-helpers-and-functions/11966):
>
> Compilation of the helpers that are available in JScript and [=expressions]. Background The script engine currently implemented in SambaPOS 5 is actually Microsoft JScript (similar to JavaScript) which is ECMAScript 3 (ES3 (Dec 1999)) compatible. As such, some functionality that you might find on the internet does not apply, such as new functionality added in ES5 (Dec 2009), ES6 (2015), or ES7 (2016). More information: [https://blogs.msdn.microsoft.com/jscript/2007/10/29/ecmascript-3-and-…](https://blogs.msdn.microsoft.com/jscript/2007/10/29/ecmascript-3-and-beyond/)

---

<div class="post-metadata">

**Author:** ![JTRTech](https://forum.sambapos.com/user_avatar/forum.sambapos.com/jtrtech/32/21270_2.png) [@JTRTech](https://forum.sambapos.com/u/JTRTech)\
**Post date:** [August 1, 2018, 5:18pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/19 "2018-08-01T17:18:57Z")

</div>

Very true, good point mark.  
I remember struggling at that time and opting for the dirty hack as a quick fix. Need to clean up in v2 but it’s working fine for now.

---

<div class="post-metadata">

**Author:** ![QMcKay](https://forum.sambapos.com/user_avatar/forum.sambapos.com/qmckay/32/27925_2.png) [@QMcKay](https://forum.sambapos.com/u/QMcKay)\
**Post date:** [August 1, 2018, 6:21pm UTC](https://forum.sambapos.com/t/sql-query-please/17908/20 "2018-08-01T18:21:56Z")

</div>

Closed on August 1, 2018, 6:21pm UTC


